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