Redefine field data type

Hi,

I am trying to write a function in a module in MS Access 2000 that
will change the data type of a field called 'Start' in table
'bo_cpm_CS01ALL'.  Here is the code that I have done so far but when I
run it nothing happens...no errors or changes to the table.  The code
finds the table and field, creates a new field called 'temp' then
copys 'Start' to 'temp' then deletes 'Start' and renames 'temp'.  The
field 'Start' has data type dbDouble.

Any help would be great!!

Public Function ChangeFieldType()

'Purpose: Changes a field's datatype

    Dim db As DAO.Database
    Dim tdef As DAO.TableDef        'Table to modify
    Dim fldOld As DAO.Field         'Field to modify
    Dim fldNew As DAO.Field         'Destination field
    Dim Property As DAO.Property    'Field property
    Dim strSQL As String            'SQL string to move the data

    Set db = CurrentDb

    'Get the table definition
    Set tdef = db.TableDefs("bo_cpm_CS01ALL")

    'Get the original field
    Set fldOld = tdef.Fields("Start")

    'Create the new field
    Set fldNew = tdef.CreateField("temp", dbDate)

    'Append the field
    tdef.Fields.Append fldNew

    'Copy the data
    strSQL = "UPDATE bo_cpm_CS01ALL Set bo_cpm_CS01ALL.Start = temp"
    db.Execute strSQL, dbFailOnError

    'Delete the original field
    tdef.Fields.Delete "Start"
    
    'Rename the new field
    fldNew.Name = "Start"

End Function
0
joshua
11/30/2004 3:59:51 PM
access.conversion 3037 articles. 0 followers. Follow

6 Replies
1563 Views

Similar Articles

[PageSpeed] 1

Is your update query backwards:
    strSQL = "UPDATE bo_cpm_CS01ALL Set temp = Start;"

In Access 2000 and later, you can change the field type on the fly:
    strSql = "ALTER TABLE bo_cpm_CS01ALL ALTER COLUMN Start DATE;"
    dbEngine(0)(0).Execute strSql, dbFailOnError
-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Josh" <joshua.paquin@tdsecurities.com> wrote in message
news:ac1dc21f.0411300759.3e7126b4@posting.google.com...
>
> I am trying to write a function in a module in MS Access 2000 that
> will change the data type of a field called 'Start' in table
> 'bo_cpm_CS01ALL'.  Here is the code that I have done so far but when I
> run it nothing happens...no errors or changes to the table.  The code
> finds the table and field, creates a new field called 'temp' then
> copys 'Start' to 'temp' then deletes 'Start' and renames 'temp'.  The
> field 'Start' has data type dbDouble.
>
> Any help would be great!!
>
> Public Function ChangeFieldType()
>
> 'Purpose: Changes a field's datatype
>
>    Dim db As DAO.Database
>    Dim tdef As DAO.TableDef        'Table to modify
>    Dim fldOld As DAO.Field         'Field to modify
>    Dim fldNew As DAO.Field         'Destination field
>    Dim Property As DAO.Property    'Field property
>    Dim strSQL As String            'SQL string to move the data
>
>    Set db = CurrentDb
>
>    'Get the table definition
>    Set tdef = db.TableDefs("bo_cpm_CS01ALL")
>
>    'Get the original field
>    Set fldOld = tdef.Fields("Start")
>
>    'Create the new field
>    Set fldNew = tdef.CreateField("temp", dbDate)
>
>    'Append the field
>    tdef.Fields.Append fldNew
>
>    'Copy the data
>    strSQL = "UPDATE bo_cpm_CS01ALL Set bo_cpm_CS01ALL.Start = temp"
>    db.Execute strSQL, dbFailOnError
>
>    'Delete the original field
>    tdef.Fields.Delete "Start"
>
>    'Rename the new field
>    fldNew.Name = "Start"
>
> End Function 


0
Allen
11/30/2004 4:28:39 PM
On 30 Nov 2004 07:59:51 -0800, joshua.paquin@tdsecurities.com (Josh)
wrote:

>Hi,
>
>I am trying to write a function in a module in MS Access 2000 that
>will change the data type of a field called 'Start' in table
>'bo_cpm_CS01ALL'.  Here is the code that I have done so far but when I
>run it nothing happens...no errors or changes to the table.  The code
>finds the table and field, creates a new field called 'temp' then
>copys 'Start' to 'temp' then deletes 'Start' and renames 'temp'.  The
>field 'Start' has data type dbDouble.
>
>Any help would be great!!
>
add 
db.tabledefs.requery

If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW
0
Andi
11/30/2004 10:14:15 PM
Excellent, Allen!

Would that apply to upgrading a BE via an FE, assuming one had design perms to
the table?

However, the originally posted method also works, if done properly, and is
compatible with all versions.

My code, similar to that posted, appends a new field and everything is fine
(for me). Maybe they are trying to write to it before it is "updated/stored"?
I can't see any other reason.

Chris

"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
> In Access 2000 and later, you can change the field type on the fly:


0
Chris
12/1/2004 8:42:34 AM
A "Doevents", or some such delay/cleanup, might assist between running code
and trying to "execute" something. Who can know what order things are executed
in, if you don't place some "Doevents" amongst it.

Merely a guess!
Chris

"Josh" <joshua.paquin@tdsecurities.com> wrote in message
news:ac1dc21f.0411300759.3e7126b4@posting.google.com...
> Hi,
>
> I am trying to write a function in a module in MS Access 2000 that
> will change the data type of a field called 'Start' in table
> 'bo_cpm_CS01ALL'.  Here is the code that I have done so far but when I
> run it nothing happens...no errors or changes to the table.  The code
> finds the table and field, creates a new field called 'temp' then
> copys 'Start' to 'temp' then deletes 'Start' and renames 'temp'.  The
> field 'Start' has data type dbDouble.
>
> Any help would be great!!
>
> Public Function ChangeFieldType()
>
> 'Purpose: Changes a field's datatype
>
>     Dim db As DAO.Database
>     Dim tdef As DAO.TableDef        'Table to modify
>     Dim fldOld As DAO.Field         'Field to modify
>     Dim fldNew As DAO.Field         'Destination field
>     Dim Property As DAO.Property    'Field property
>     Dim strSQL As String            'SQL string to move the data
>
>     Set db = CurrentDb
>
>     'Get the table definition
>     Set tdef = db.TableDefs("bo_cpm_CS01ALL")
>
>     'Get the original field
>     Set fldOld = tdef.Fields("Start")
>
>     'Create the new field
>     Set fldNew = tdef.CreateField("temp", dbDate)
>
>     'Append the field
>     tdef.Fields.Append fldNew
>
>     'Copy the data
>     strSQL = "UPDATE bo_cpm_CS01ALL Set bo_cpm_CS01ALL.Start = temp"
>     db.Execute strSQL, dbFailOnError
>
>     'Delete the original field
>     tdef.Fields.Delete "Start"
>
>     'Rename the new field
>     fldNew.Name = "Start"
>
> End Function


0
Chris
12/1/2004 9:01:34 AM
Try it Chris. I would expect it to work.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Chris Mills" <phad_nospam@cleardotnet.nz> wrote in message
news:ehedYI41EHA.3000@TK2MSFTNGP15.phx.gbl...
> Excellent, Allen!
>
> Would that apply to upgrading a BE via an FE, assuming one had design 
> perms to
> the table? 


0
Allen
12/1/2004 10:36:45 AM
If the code is making changes to the structure or content of the database, I 
find that a DbEngine.Idle is sometimes required. For example, in this 
situation I'd try a DbEngine.Idle between the Fields.Append and db.Execute 
statements.

-- 
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


"Chris Mills" <phad_nospam@cleardotnet.nz> wrote in message 
news:%23w$uHT41EHA.3000@TK2MSFTNGP15.phx.gbl...
>A "Doevents", or some such delay/cleanup, might assist between running code
> and trying to "execute" something. Who can know what order things are 
> executed
> in, if you don't place some "Doevents" amongst it.
>
> Merely a guess!
> Chris
>
> "Josh" <joshua.paquin@tdsecurities.com> wrote in message
> news:ac1dc21f.0411300759.3e7126b4@posting.google.com...
>> Hi,
>>
>> I am trying to write a function in a module in MS Access 2000 that
>> will change the data type of a field called 'Start' in table
>> 'bo_cpm_CS01ALL'.  Here is the code that I have done so far but when I
>> run it nothing happens...no errors or changes to the table.  The code
>> finds the table and field, creates a new field called 'temp' then
>> copys 'Start' to 'temp' then deletes 'Start' and renames 'temp'.  The
>> field 'Start' has data type dbDouble.
>>
>> Any help would be great!!
>>
>> Public Function ChangeFieldType()
>>
>> 'Purpose: Changes a field's datatype
>>
>>     Dim db As DAO.Database
>>     Dim tdef As DAO.TableDef        'Table to modify
>>     Dim fldOld As DAO.Field         'Field to modify
>>     Dim fldNew As DAO.Field         'Destination field
>>     Dim Property As DAO.Property    'Field property
>>     Dim strSQL As String            'SQL string to move the data
>>
>>     Set db = CurrentDb
>>
>>     'Get the table definition
>>     Set tdef = db.TableDefs("bo_cpm_CS01ALL")
>>
>>     'Get the original field
>>     Set fldOld = tdef.Fields("Start")
>>
>>     'Create the new field
>>     Set fldNew = tdef.CreateField("temp", dbDate)
>>
>>     'Append the field
>>     tdef.Fields.Append fldNew
>>
>>     'Copy the data
>>     strSQL = "UPDATE bo_cpm_CS01ALL Set bo_cpm_CS01ALL.Start = temp"
>>     db.Execute strSQL, dbFailOnError
>>
>>     'Delete the original field
>>     tdef.Fields.Delete "Start"
>>
>>     'Rename the new field
>>     fldNew.Name = "Start"
>>
>> End Function
>
> 


0
Brendan
12/1/2004 2:16:49 PM
Reply:

Similar Artilces:

Adding additional data to a pivot chart
Hello, I am trying to add a line or some sort of note to a pivot chart and I'm wondering how to do it. I will have multiple items but the note only occurs on one date for each particular item. For example, let's say that I am keeping track of when I buy an item (month and year) and when I sell an item (month and year). I want to add some sort of note automatically to my pivot charts indicating when I put these items on sale (month and year). For example, maybe I put all RC Cars on sale May-01 and all airplanes on sale Oct-05. How can I get the sale date into my pivot chart? I&#...

moving XML data from client to server vice versa
.... The second time I am sending this email ... .... I want a good solution, please. .... Thanks :) What I need is moving xml data from client to server & vice versa, but not using xml file. I did something like this : In server (VBScript) : ----------------------------- dim xml_list dim newnode xml_list = Server.CreateObject("Microsoft.XMLDOM") newnode = xml_list.createElement("mylist") xml_list.documentElement = newnode itemnode = xml_list.createElement("line") newnode = xml_list.createElement("element_id") textnode = "This is a text"...

Which table to put fields in?
Can someone help me with where certain fields should go (I am self-taught so please bear with me!) I am creating a database to store information regarding nursing home audits. A member of staff will go out to the home with a list of approx 140 questions to check compliance. Each home is audited at least once a year. A different member of staff may go each time. So far I have tables set up with the base information, e.g tblHome - HomeID, Address etc, tblAuditor - Auditor ID, StaffName etc, tblQuestions - QID, QuestText, tblResponse for the yes/no answer and additional comment...

Visual basic and emprt fields
I recently converted from Access 97 to 2003. (we're a little behind the times at my company). Anyway, we have some visual basic code which interfaces with the Access database in order to populate BOM's in AutoCAD, etc. My problem is that the code does not like fields with 'null' values. It didn't seam to mind then in 97? I've since entered a meaningless character in those fields to get the code to function so the company can still run, but is there a 'real' fix? I'm only self taught at VB, but I think it has something to do with an empty records...

Making Data and Axis Labels Different
Hi, I have a simple column chart that uses millions of units on its Y axis and puts a label above each column showing the exact value. At the moment the Y axis shos all the trailing zeros behind each million value, somethiong like: 40000000 36524102 35000000 x .. x .. x .. x 5000000 x colummns here What I would like to show is the Y axis values without the trailing zeros but the data label in full. I seem to be able to do one or the other but not ...

Multiple Instance of same form retrieving different data.
I have a form that shows Batch Jobs by name. Connected to this is a subform which shows unique Batch Job Name & Job Nbr combination records. The Batch Job Name and Job Nbr are the primary keys for the table being sourced by the subform. For Example, I have a form with Job Name of: Batch Job A and a sub form which Shows Batch Job A, 12 Steps & Batch Job A 16 Steps. The Job Nbr is hidden because its an internal number that ties back to source system. What I would like to do is give the user the ability click on the batch Job name in the sub form which will open form Batch...

Additional Fields for Items
I have a couple of questions regarding the items part of RMS. 1. Is there an open through MS or an addon available to add more custom data fields on the item database that would be available to put on labels and sort reports by? To answer in advance I am already using the 3 Sub Descriptions offered, but I need more. 2. When making custom labels, is there a way to get the 'Notes' area from the special page to go onto a label? Thanks for any help in advance Hi Steve, One option is not to use the RMS Labels but use Microsoft Word with Mail Merge and labels. This allows you t...

Combine two sets of overlapping Date/Time data into 3 columns.
I have two sets of data with Date/Time. One set has a fixed interval, the second is more sporadic. I am trying to combine into these sets into one table with the data from one set next to the corresponding data from the second set. For example: A B C D 4/22/2010 9:00 000 4/22/2010 9:01 777 4/22/2010 9:01 111 4/22/2010 9:03 888 4/22/2010 9:02 222 4/22/2010 9:06 999 4/22/2010 9:03 333 4/22/2010 9:04 444 4/22/2010 9:05 555 4/22/2010 9:06 666 Output: A ...

Sort a numeric field
I want to sort the following in "true value" as opposed to the order that is provided below. My import document provides this format. Maybe not the best explanation, but can you help? Thanks as always - 1000 10000 2000 3000 35000 4000 45000 5000 On Sun, 26 Jun 2005 09:16:02 -0700, "sdmccabe" <sdmccabe@discussions.microsoft.com> wrote: >I want to sort the following in "true value" as opposed to the order that is >provided below. My import document provides this format. Maybe not the best >explanation, but can you help? > >Thanks...

Updating mapped fields
I made a mapping from account to a contact. The fields that are mapped are the general adress fields. When I create a new contact from an account the adress values from the parent account are used for the new contact. So far so good. My problem is that I want to automatically update the adress fields of a contact when the values change in the parent account. This isn't done automatically by MS CRM. Is there a way to do this? I've tried to create a manual workflow rule that will do this, but that won't work I think. Does anybody have a solution or some pointer for me? Thanks in a...

Diaglog/Threading Data Exchange Question
Greetings, I have learned a lot from all of you, and I hope to over come my latest road block. I have a smal IRC Chat client I am making. I have a Dialog Box with Control and I use a worker thread for connecting to IRC - That Thread will update controls on the Dialog by sending PostMessages. Now my problem is in the ListBox Control that I have extended to do popup menu with various commands. I need to be able to read that controls methods from within the Thread. so I need to do this from within my IRC Socket thread: (code not exact - pseudo code) CListBoxEx myUsers; //Defined in my ...

CAE macro for green screen updates with Excel data
I am a user of the Personal Communications AS/400 (Client Access Express for Windows) WorkStation Program IBM software. I am able to record a vbscript macro to automate a repetitive update of our ERP using one of the ERP options. I need to modify the macro so that it picks up the data to be entered from an Excel list and "types" it into the appropriate input fields. I need to get it to process the entire list of values row by row. Right now the macro recorder has produced a statement like autECLSession.autECLPS.SendKeys "B11641" which I have to replace with commands to...

Data Forms #3
Hi I have a spreadsheet that I am filling in using a form. I have also a row at the bottom with blank rows in between with a running total. When I print there is a huge gap between the last entry in the form and the totals at the end. Is there a way that the form can enter the next line and push down the totals so that there are no gaps. This is what I have. Name Salary Tax Total Ann 42000 5000 37000 Joe 37500 6000 31500 Total 79500 11000 68500 This is what I want Name Salary Tax Total Ann 42000 5000 37000 Joe 37500 6000 31500 Total 79500 11000...

Use spreadsheet as data source
Hi, We have two workbooks, Bk 1 contains information relating to production scheduling and manufacturing information. Bk2 is a template used to generate a ticket (workbook) containing specific information relating to the individual order. Is it possible to transfer information between the two. Eg on Bk1 there is a packing spec worksheets for items, what size outer to use, and how many items in that outer as well as other information, can we interrogate this (search by item code) and then "autofill" in cells marked "Outer" and "Number" in Bk2 (ticket). Tha...

Separating field information
I need some help. Can this be done with queries? Database has 13,000 records All records are identical except for the History Field that will contain one number 1, 2, 3, or 4 In a query that contains no records with a history of 2 select the records with a history of 1 but not 3 or 4. In a query that contains no records with a history of 1 select the records with a history of 2 but not 3 or 4. Thanks for your help. Marv Trott That is unclear to me. You must be leaving out some necessary detail, since the way I read your post all you need to do is apply criteria of =1 for t...

Totals query and Memo field
I want to query a table and only display the record with the latest date. When I add the Totals row to the query grid, the memo fields cut off at 255 characters. Is there any way around this? The fields display everything in a regular query but I only want the latest date. Thanks, Jim Post your totals query SQL so someone has the best opportunity to help. -- Duane Hookom Microsoft Access MVP "Jim" wrote: > I want to query a table and only display the record with the latest date. > When I add the Totals row to the query grid, the memo fields cut o...

Best way to copy data across ?
End users are using an Access 2003 Database with both front end and back end. There is a requirement to add fields in a particular table and change the forms and reports accordingly. I have made a new copy of both front end and back end while end users are still using the database. I would like to know what is the best way to copy all data from production to the newly developed backend ? (How to import data to table) ? Thanks Why not just alter the existing backend? Kick everyone off the system briefly and make the changes. Alternatively, write VBA code to make the changes for y...

Data Table #2
Hi All, Is it possible to display the Data Table on a Pie chart, as you can in a Bar chart. Excel 2003. Thanks, kfh. Hi, Simple answer, no. Instead create your own. For more information see Tushar's page, http://tushar-mehta.com/excel/newsgroups/data_table/index.htm Cheers Andy k f h wrote: > Hi All, > > Is it possible to display the Data Table on a Pie chart, as you can in a Bar > chart. > > Excel 2003. > > Thanks, > kfh. > -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info Hi Andy, Thanks for the link, is there any way to add the...

chart displaying horizontal line for zero data
Hello, I am using a column chart in Excel. The chart is currently displaying a horizontal line when there are zeros for the data. It is working as it was designed to do... however I would like to make the graph not display a line when there is all zero data. Is there an easy way to fix this problem? Thanks Pseud In place of zeros use =NA() This displays as #N/A and these points are ignored by chart best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email <pseudonym@1NET.gr> wrote in message news:1107198704.841502.289510@c13g2000cwb.googlegroups.com.....

Exporting Data from access and appending to an existing excel sprd
I run a series of queries each month in an access form that I have created. I can export the data into a new excel spreadsheet no problem. I am just wondering whether or not there is a way to export the data into an existing excel file and append it to the bottom of data that has been exported in previous months. It's not really critical, I'm just trying to make things that much easier. Thanks. You can always use the drag and drop technique. Open the Excel file to the desired worksheet, then minimize it to your task bar. Drag a saved query from your database to the...

Pivot tables / source data
Is there a way to take a pivot table and copy and paste it into a new document so that the source data cannot be pulled up by the person looking at it? The reason I need to do this is because I have created a pivot table and included in the source data is very confidential information. I want to be able to send out JUST the actual pivot table, but I don't know how to do this without releasing all of the other information along with it. Is this even possible?? Have you tried doing <copy> (on the table), then moving the cursor to another sheet and Edit | Paste Special | Values (ch...

Collect data from spesific cell in multiple sheets
How can I collect data from cell A1 on sheet 01.01 thru 31.12 (Dates) to summary sheet? I've tried fill and copy formula with different use of ! and $ but I've not been able to solve this by my self. Thank you Hi, The correct syntax is =SUM(Sheet1:Sheet3!A1) Substitute sheet1 & sheet3 for the first/last sheets in the range you want to sum -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Espen Rostad" wrote: > How ...

Transactional Data Through DTS/SQL Scripts
Hi All, One of my client want to create new company in same SQL Server, but with different segments and format of Chart Of Account. Firstly i gave idea to use account modifier to change segments and COA within Old comapny but they require new company. Till now no problem but client also want all historical/open data for from old company to new one. I think i need one table for account mapping with old COA and New COA, then utilize that mapping through SQL Script while transfering data tables through DTS. Modules running are AR,AP,SOP,POP,GL,BR,FA,INV,HR,PAYROLL on GP 8. and SQL 2000 whats ...

Pivor Table Data Field
This is a multi-part message in MIME format. ------=_NextPart_000_000C_01C5A644.DE8D0D20 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have tried to "show" a data field after using "hide" in field = settings. How can I make this reappear? Not using Field drop down = option. The fields orgin is from the excel list and not a calculated = fields. ------=_NextPart_000_000C_01C5A644.DE8D0D20 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC ...

copying data from test databases
Hello: We are getting ready to have our client go-live on GP 10.0. For the last few weeks, we have had to client "play" with their data in GP 10.0 test companies (test databases). We would like to transfer their master data from the test companies to the live companies databases. Before doing so, we will use GP's history removal utilities to get rid of the transactions that we do not want to transfer over. Which is the best and safest way to transfer this master data--by simply restoring a backup of the test databases over to the live databases or by using SQL 2005 Imp...