Issue changing column data type

[John Parrish]

I have a client that operates off of an access 97 database. I am in the
process of migrating them to a more appropriate winforms and sql server
solution, but for the time being I am also attempting to support their
current system.

They have a table that for whatever reason was created with 3 columns that
should have been of type "currency" but instead were created as text. I am
trying to change the column type to currency so that I can get them some
totals on a crystal report I have written for them. I have already scrubbed
all values that would cause a datatype mismatch error, but when I attempt to
issue an alter table/alter column statement, I get an error stating that the
maximum number of file locks was exceeded.

I have modified the system registry jet 4.0 value for maximum locks to 5
million and still cannot get the statement to execute. I was wondering if
anyone has any suggestions on how to handle a column type conversion on a
table that has 20+ thousand records?

If I pump all of the records into a temp table, then change the column types
and insert them back into the table, would that possibly work? One problem
would be the auto-number column, I know methods to handle autonumber
creation / conversion in SQL server, but not access.

Regards

John Parrish

 

[Allen Browne]

Try adding a Currency field to the table.
Use an Update query to populate it.
After you are sure it is all correct, you can delete the text field.

 

[giorgio rancati]

Hi John,

open the database in Exclusive mode and execute the alter table.
The Open Exlusive database doesn't lock the pages .

bye

 

[John Parrish]

Just goes to show what some different thinking will do. I hadn't considered
that, and unfortunately worked past it by running my own totals. I did just
try this out, and it worked without a hitch. I even ran it on a db that had
columns that weren't perfect.. i.e. values like "216.00."

Thanks!

John Parrish