Hi,

I have an Access Database(stand alone / not split) that includes an automated process for uploading a daily file, and kicking out some reports. The purpose of this database was alway to be a single user platform. When I run this process on the PC I used to develop the database, it works flawlessly, and has done so for over 8 months. When my coworker attempts to run this same process, we get mixed results. On Monday, Tuesday and Wedneday of this week, she ran the process and it completed successfully each time. Today it hung up on her and seemed to be stuck in a loop. We killed the process when we noticed the size of the Access database had ballooned to over 972 MB (normally it's about 60 MB).

I don't think this would be an Access issue, but something local to her PC, but just in case I thought I would check in here to see if I might have missed something. I have uplaoded a word file with my code and a screen shot of the status screen we use to track the progress of the upload.

Any thoughts on something I could be missing?

Dennis

With only a cursory look at your code, I don't see where you are executing any time of looping. I would suspect a query is corrupted, or perhaps it is a data issue. On your co-worker's machine, can you manually run each query? If yes, run each query and check any tables that are updated to see if they are returning the desired results every time.

I am not executing any loops, but the query is doing a simple data load. The log we get each day is in Excel format. Rather than try to work with linked Excel data, we tranfer those worksheets to Access tables. The append query basically says take whatever the Excel has and put in on the corresponding Access table, as long as the ticket number is not blank.

The problem seems to be hit or miss with my coworker. When I had made the original post, she had run the process 3 days straight without issue, and on days 4 and 5 it crashed. Then after a few calls to the help desk and a repair to her installation of Access (which was a total shot in the dark), she has been able to run the process for the last few days.

The real irony is when it is crashing on her. Of the 5 tables being loaded, the one that it fails on is only the 2nd largest. The largest table is over 4000 records and loads without fail, the one that crashes is about 430 records.

Yea, this is one of those "Who Knows?".

But then again... After you process the data that was imported to the table(s), are you deleting the tables? A faster (and I think less-bloating) approach is to have something like CurrentDb.Execute "DELETE FROM YourTableName", dbFailOnError after the importing is done.

Also, it might benefit to Compact/Repair on close of the database:

Public Sub CompactDB()

CommandBars("Menu Bar"). _
Controls("Tools"). _
Controls("Database utilities"). _
Controls("Compact and repair database..."). _
accDoDefaultAction

End Sub

Or you can use the /compact command-line switch to compact the database. Use syntax such as the following on the command line or in the "Target" Property of a Windows shortcut (watch out for word wrap):

"<FullPathToMSOffice>\MSAccess.EXE" "<FullPathToSourceDB>\Source.MDB" /compact "<FullPathToTargetDB>\Target.MDB"

You could have that in a batch file that is run manually or by Windows Scheduler at a time when the PC is on and no one is there.

Here are more possibilities:

Application
• Auto compact and backup
• Compact a MDB Database from the DOS Command Line
• Problems with Compact/Repair and Digitally Signed Certificates
  
  Automation
• Backup, Repair and Compact a List of Access Databases
• How to Compact Databases at a Scheduled Time
  
  Corruption/Repair
• Why Compact and Repair your Microsoft Access Database?
  
  FAQs
• Compact and Repair -- Why? How? and How Often?
  
  Modules
• Getting the Decompile and Compact context menu options