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  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?