Can't change data type... (1 Viewer)

[MSherfey]

I have a simple table with about 20 or so fields and about 390K records. This same structure is in three local databases (one for each business unit). One of the fields was incorrectly imported as text instead of date. I would go back and re-import the data but that is a complete pain. It comes from about 10 similar txt files per database and I would rather fix the data already in the system than re-import 30 txt files.

I tried to change the data type in DESIGN VIEW but that says I don't have enough disk space or memory to complete the change. Not sure why since I have over 100G free HDD space and 2G RAM Free (4G total).

I then did a quick query to pull out the data from specific years to see if that would help. I pulled out one year, went into DESIGN and same error. Even though I only have 51K records this time.

I then tried ALTER TABLE on the pulled data with:

ALTER TABLE [2000] ALTER COLUMN [Maint Start:] DATE

It looked like it would work when it warned me I didn't have enough memory for an undo. It ran a bit further then came back with a "File sharing lock count exceeded" error and told me to increase the "MaxLocksPerFile" setting in the registry. I increased it from the default 9500 to 128000 and restarted Access. No good. Same error.

HELP! What am I doing wrong? Any ideas on how to get this data type changed without re-importing all the data?

 

[HiTechCoach]

Two way I use:

1) Create a new table and append the data form the old table. Delete the old table and rename the new table to the original name.

2) Add a new field. Run an update query to move the data. Delete the old field. If needed, rename the new field to the original name.

 

[MSherfey]

OMG!!! That was too simple. I thought if you appended the data the field types had to be the same. I feel stupid, but I'll get over it

Thanks for the help!

Not to keep the thread going since the the question is already answered, but why didn't the ALTER TABLE or the DESIGN VIEW work? Does anyone have any idea how the memory works in these processes?

 

[gemma-the-husky]

are you trying to change the data on a backend table, over a network

sometimes you can get round this by copying to your local machine, doing it there, then copying it back.

with big datafiles, this sort of error message IS most probably genuine. I doubt if anyone (try the MVPs) knows what is REALLY happening internally - but access will generally try to create a backup image of whatever its oding, so it can restore the data if the update fails ... and i guess something is failing,

If you ARE doing stuff like this, it is definitely sensible to copy the data file before you start as a precaution.

 

[MSherfey]

No, these are local databases. The biggest is only about 350M (390K records). I didn't think it was that big but maybe it is I guess.

And yes, I always backup my databases anyway. Since they're local I don't have a tape or anything on them. I create a backup in Access and then copy to a network area. I only keep three revisions so I don't use too much space.

I just assumed my system could handle a simple data type change.