Append query errors. . . .

[Rick]

I have an access (97) database that is growing (49,000K +)
via an append query. Each time the query is run 120 - 250
records are added.

The data itself is back ended in a separate database.

Recently the append query began producing errors, it had
worked laterally hundreds of times, but suddenly it is
producing a variety of errors that indicate size issues.
And they do vary. When I break the data into smaller
portions the query works. When I try to append it all at
once I get errors like "Couldn't read the record;
currently locked by another user" (when I am the only user
in the database) or "Couldn't save; currently locked by
user 'Admin' (that's me!) on machine (Not my machine!?)"

So, I am pretty confident that the issue is somehow
related to size, although the DB is not that big.

Anyone have any suggestions how I might be able to
overcome these obstacle, less clearing out the DB??

Thanks in advance.

Rick

 

[Eric Isaacs]

Rick,

Sorry if this is oversimplifying the problem, but have you
attempted to repair and compact the database? Many times
db bloat is caused by adding/removing records in time.
Access doesn't reclaim that space until the db is
compacted. 49000K is a big db if it's compacted.

Tools-->Database Utilities-->Repair and Compact

If you have a front-end and a backend db (split dbs) make
sure to compact and repair each one.