How do I change data type in large table

This article seems to describe your situation.  http://support.microsoft.com/kb/209940/EN-US/

Greg

JestersGrind,

Thank you for the response. I tried this already and I cannot find the registry key as suggested. I narrowed the string down to engines\jet and that is not found.

So, still searching for an anwer.

Thanks,

Dave

Can you copy the database, then open the copy and make the new field with the correct variable, then copy and paste the contents of the old column, then delete the old column and save the database?

Might be a workaround?

Inov8iv

This does not work. II insert a column into the table and then try to change the field format. I receive the same error message. The files are quite large, 1.4m to 1.6m records.

Thanks, I'm still looking for a solution.

Dave

Try this:

Create a new empty database. Use the command below to copy the table structure only to the new database:

DoCmd.TransferDatabase acExport, , "C:\MyPath\MyNewDB.mdb", acTable, "MyTableName", "MyTableName", True

Then go into the new database make the changes to the field.

Then do an Append Query to the new table from the current database -- converting the changed field as necessary, then delete the table in the current database , do a compact and repair. Then copy it back with

DoCmd.TransferDatabase acImport, , "C:\MyPath\MyNewDB.mdb", acTable, "MyTableName", "MyTableName"

All that you should have to do is add a column with the datatype of your choice then use an update query to update the new column from the old then drop the old column.

All you have to do to repeat is use a DDL query to add a column then an update query and another DDL to drop the old column...