Deleting records in Large Table

[indyaries]

Greetings,

(Access 97 SR1)
I have a finance & acctg database that has three tables in it (Army, DoD, Other). Using macro to;
1. Link to external info.
2. Delete the records in the permanent tables above.
3. Append new records from linked info into the appropriate table above.

PROBLEM

The Army table contains over 150K records now. Every day it will grow larger. When the macro begins to delete the records in the Army table prior to the append, an error is generated. The user said that someone told him that the table is so large that the app is hanging when deleting.

Is there some method to perform these deletes daily without having the system crash?

TIA !!

Bob in Indy

 

[Fornatian]

Are the tables stored on a server because with Access, the entire dataset is downloaded and then queries are run locally, whereas with SQLServer, the processing is done at serverside, so your friend may have a valid point.

Can you access the database from the server machine and run the deletes and appends from there?

Could your Army table be slimmed down to prevent the amount of 'baggage' transported between the host and client. (e.g.rarely used fields moved to another table with a one2one relationship)

 

[FoFa]

have you tried deleting the table and then using a Make Table query instead?

 

[indyaries]

Fornatian,

The Access database indeed resides on a shared drive.


FoFa,

I've not tried the Make Table query...but I'll give it a whirl.

Thanks to both of you. I'll let you know what I come up with.

Bob in Indy

 

[neileg]

Do you have to import the data at all? Is it possible to link to the data in whatever format you already have it in so that you just overwrite these files with the new versions and remove the problem altogether?

 

[indyaries]

Do you have to import the data at all?

Neil,

I suspect that the proponent agency will balk at "linking", but I'll advance the idea. The app runs pretty slow right now...will be much slower linking.

Thanks for the suggestion!!

Bob in Indy