issue changing datatype in table

[Gareth Keenan]

Ahoj!

I'm have a problem I need help with(duh.) My database consists of one large table of approx 3.9m rows and about 12 different columns. One of my columns is of a data type "date/time" but i need the format of this column to be "text"

The problem is that when I right click the table/go to design view/ and try to change the data type, I get the error "Microsoft Access can't change the data type. There isn't enough disk space or memory."

Which means i can't take the easy way out. Is there a way I can just create a new identical table and change the data type of my one column? I was thinking there might be something in SQL I could do, or some how in creating an identical table where only the field type has changed. I should note, I looked up this using the access in file help, and it appears the ALTER TABLE SQL will not work on my machine.

I'm really at a loss, because the dataset was created from a txt file which as been lost to the ether, and I can't import the database into excel using a macro I didn't write unless the format of my column is text.



Any suggestions?

Cheers,

Gareth Keenan

[pbaldy]

The second easiest way out might be to export a query rather than the table. In the query, for that field, use the Format() function to format the date/time as desired. The Format() function returns text, so Excel should see it as text.

Not sure why ALTER TABLE wouldn't work, other than it might return that same error. I have 2007 on a machine and ALTER TABLE works fine on it. You could probably do the identical table bit and use an append query to copy the data over after changing the data type of that field.

[Gareth Keenan]

Solution found.

For one field, I just created a new field(with the correct data type) and populated the new field with an update query to be equal to the field I wanted. Then delete the old column and rename the new column.

What I'm actually in the process of doing is exporting the table to a text file, then re-importing and renaming all of the fields the correct data type.

I didn't know I could export to a text file, but if you right click on the table you can export.

Problem solved.

Thanks for chiming in pbaldy!

Gareth Keenan

[pbaldy]

Glad you got it working, and welcome to the site by the way!