Connecting Excel to Access - DAO Error when trying to connect to Access (Error 3051)

1. SYMPTOMS
When an Excel VBA program trying to establish a connection from Excel to Access by using DAO library.  An error 3051 occur and says "The Microsoft Access database engine cannot open or write to the file...".  This error is occurred by random and same record may be able to write into the database successfully in the second run.  The following is the error message captured by my error handling procedure.

2. CAUSE
Basically this is a user right permission issue or you'll also get error 3051 if your program establish connection to the same database in second time.  Note that there is a little time required to close the database (especially if your database is large in size).  If you set up a loop for heavy reading or writing the database, there may be a chance for your program trying to establish a connection to the database before the previous session is closed completely.

3. RESOLUTION
Set up a wait time after closing the file and let the database connection closed completely before the next session.

The following procedure is used for executing an Action Query

Sub doActQry_DAO(sDB As String, sSQL As String)
    'Description: Execute an action query by using DAO
    'Date: 27-Jan-12
    'Copyright: www.CalvinSir.com
    'Remarks: Student version
    
    'initialization
    Dim db As DAO.Database
    Set db = OpenDatabase(sDB)
    
    'execute the SQL statment
    db.Execute sSQL
    
    db.Close
    Set db = Nothing
    '***************************************
    '*** set wait for 0.3 second to prevent Error 3051 ***
    Application.Wait (Now + TimeSerial(0, 0, 0.3)
    '***************************************
End Function

4. Further Study

How to connect Excel to an Access database?
How to connect Excel to MS-SQL?
How to program Excel VBA to connect database by using ADO or DAO?
How to write Action Queries?
How to write Sub-queries?
How to retrieve total number of records written into the database?
How to I tell if the action query has been executed successfully?
How to prevent crashing my program if I'm deleting a not existing database table?
How to prevent cashing my program if I'm reading an empty data table?
How to load a query results directly in an Excel worksheet

5. Finance VBA, C++ and SQL Training

We're financial experts to provide the following services:

1. Personal and company training on Finance VBA and SQL (both local & remote learning)
2. Financal modelling
3. Bloomberg plug-in and Excel VBA applications development
4. Database system development

Please contact us for more information.

6. Reference

Error message when you start Microsoft FRx: “Run-Time Error '3051': The Microsoft Jet Database Engine Cannot Open the File”