Alter table - out of resource or locking cells

[gaj104]

Hi

I have a table with about 45,000 rows, which is created from a direct query.

Unfortunately some of the fields are text rather than numerical. Therefore I run a set of alter queries.

However the way I'm running them makes the system debug due to insufficient resources or maximum locking.

I'm using the following process.

Dim db As Database
Set db = Access.CurrentDb

thedate = InputBox("What Date would you like to run the CRF for? (DD/MM/YYYY)", "Run CRF On Date")

db.Execute "DROP TABLE CRF"
db.Execute "SELECT * INTO ---hidden for confidentiality--  WHERE (((crf_finance_table.LOAD_DATE)=#" & Format(thedate, "MM/DD/YYYY") & "#));"


'convert CRF to numbers

'where macro crashes!!
db.Execute "ALTER TABLE CRF ALTER COLUMN DESC1 LONG"
db.Execute "ALTER TABLE CRF ALTER COLUMN CUSTOMER_NO LONG"

Any sugesstions how to get around this problem?

Thanks

 

[SydneyGeek]

Maybe worth a go -- treat the one with the text fields as a temp table.
I have found sometimes that appending to another table with the correct data types will fix it on the fly (eg, Excel data in the wrong format, pulled into Access).

Not always, but often enough to give it a go...

Denis