Access 2007 - Cannot open database.

[RL]

Morning,

We have an Access 2007 tool. End users work with static data from one of the
tables within that database. Each night, a macro runs a truncate query,
followed by an append query and a number of update queries to refresh the
data in that table.

The table has 12 fields. The append query appends 7 million-ish records into
5 of those fields. The other 7 are subsequently populated using the update
queries.

I know it's not a text book normalised database, but I tried different ways
to make the data available and this way, although slow to update overnight,
was by far the fastest performance for the end user.

However, I have started receiving the following error message when the
append query runs:

"Cannot open database ''. It may not be a database that your application
recognizes, or the file may be corrupt."

I have done some testing and the query runs fine for 5 or 100 records. As
soon as I try to run the entire query, or 5+% of it, that error message
appears.

Any ideas what could be causing this problem?

RL

 

[John Spencer]

Have you compacted the database? Deleting records from the database does not
free up the space that the records used.

If all the data in the database is replaced each night, it might be worthwhile
to delete the entire database and create a new one each night.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[RL]

Morning John.

Thank you very much for the advice. I'm pretty new to Access, so hadn't come
accross compacting before! I've read up on it this moring however and that
does sound very much like it could be the issue, so thanks for pointing me in
that direction.

I have added the following three run command lines to the autoexec macro;
Backup
CompactDatabase
Repair Database

Should that do the trick?!

RL

 

[RL]

Ignore my last message!!

Access won't let me compact or repair as part of a macro. However, run a
repair manually, the whole update takes between 1 and 2 minutes - rather than
the 3.5 hours that the last successful overnight update took. Incredible!

Massive thanks again for the original advice! Any ideas how I can automate
it to run that compact and repair periodically, or shall I just add it to my
to do list once a week?!

RL