Fields data type (Access97)

Topic

New Reply

How do I change a fields datatype in a table in code.
I want to change 1 field from text to number (DbLong)
Thanks,
Scott

Reply | Quote

Replies

First you make a backup of your database (or if it is split of the back-end database).
Then if you are paranoid, you make a second backup somewhere else.
Then you go into the database container window, and open the table in design mode, and change the field type and try to save the table. If you have non-numeric data in the field (anything not numbers or periods or commas, etc.) Access will complain and say it can’t save the data in the format you requested. Otherwise, it should be good to go at that point.

Reply | Quote

As you may have surmised from Wendell’s reply, the answer to “How do I change a field’s data type in a table in code?” is “You can’t”. When you change the data type interactively, Access is doing things behind the scenes you can’t do in code, at least not directly.
The Type property of a DAO Field object is read/write until the object has been appended to the Fields collection, after that it is read-only. So you can’t use DAO to change the field type of an existing field.

To simulate the interactive behaviour in code, you’d have to do the following:

1. Create a new field of type dbLong; give it a temporary name, and append it to the Fields collection.
2. Execute an update SQL instruction that sets the value of the new field that of the old field (converted from string to long integer).
3. Delete the old field.
4. Rename the new field to the name of the old field.

If the text field is part of an index or relation, you’d have to do even more work…

Reply | Quote

Thanks Wendel, Hans
After reading your replies I ended up doing something different.
Created a temporary table to hold the data then append the data to the table with the correct datatype.
Finally delete the temporary table.
Thanks for your help,
Scott

Reply | Quote