Issue on VBA MSAccess execute command

Question

Dear Support,

I have used for a long the below VBA code run from an excel file to insert the content of one table from one MSAccess DB to a different table placed in a different MSAccess DB placed in a different location:

Private Sub ImportTableSQL(SourceDB As String, DestinationDB As String)

    ``Dim CnnSourceDB As ADODB.Connection

    ``Dim RsDestinationTable As ADODB.Recordset

        ``Set CnnSourceDB = New ADODB.Connection

    ``With CnnSourceDB

        ``.Provider = "Microsoft.ACE.OLEDB.12.0"

        ``.ConnectionString = "User ID=Admin;password= ;" & " Data Source=" & Application.ActiveWorkbook.Path & "\" & SourceDB

        ``.CursorLocation = adUseClient

        ``.Open

    ``End With

    ``Set RsDestinationTable = CnnSourceDB.Execute("INSERT INTO Activated_Source_TABLE1 IN '" & Application.ActiveWorkbook.Path & "\PN_ACTIVATED\" & DestinationDB & "' SELECT * FROM Activated_Source_TABLE")

   

    ``Set RsDestinationTable.ActiveConnection = Nothing 'Disconnect it

    ``Set RsDestinationTable = Nothing 'Drop the recordset

    ``CnnSourceDB.Close 'Close the connection - I know I opened it, so I know it's in an open state

    ``Set CnnSourceDB = Nothing 'Dispose

End Sub

My MSAccess DBs are an .accdb format

Today, I don't know why, I'm getting the below error screen:

Any idea on solving this issue?

Thanks

---

Mod moved from : Microsoft 365 and Office | Access | Other | Windows

Answer 1

The error you're encountering, Runtime error '-2147467259 (80004005)' operation not supported for this type of object, often happens when there's an issue with the database provider or the operation you're trying to perform. In your case, this can happen when working with MS Access .accdb files via VBA and ADODB, especially if something changed in the environment or Access itself.

First, make sure you have the correct version of the Microsoft Access Database Engine installed. You're using the provider Microsoft.ACE.OLEDB.12.0, which should work for .accdb files in Access 2007 and newer. If you haven’t installed or updated it yet, you can download and install the appropriate version (32-bit or 64-bit) from Microsoft’s website. After installing, restart Excel and try running your VBA code again.

Second, double-check your connection string. The path to your source and destination databases in the connection string should be formed correctly. If the SourceDB or DestinationDB variables aren’t getting their values correctly, or if the path isn’t valid, that could be causing the issue. You can print out the full connection string to the Immediate Window (Debug.Print) to verify it's correct.

It's also important to verify that the destination database (DestinationDB) is accessible. Ensure that the file is not open elsewhere or locked by another process, which could cause the issue.

Another thing to consider is the usage of the IN clause for the linked table in your query. You're trying to insert data from one Access database to another using a query like this:

INSERT INTO Activated_Source_TABLE1 IN '" & Application.ActiveWorkbook.Path & "\PN_ACTIVATED\" & DestinationDB & "' SELECT * FROM Activated_Source_TABLE"

Sometimes this can cause issues depending on how Access handles linked tables in different versions. You might want to try using a more controlled method, such as using DAO (Data Access Objects) instead of ADODB. With DAO, you can explicitly open both the source and destination databases and transfer records one by one.

Here's an alternative way to handle the import using DAO:

Dim dbSource As DAO.Database
Dim dbDest As DAO.Database
Dim rsSource As DAO.Recordset
Dim rsDest As DAO.Recordset
Dim strSourceDB As String
Dim strDestDB As String

strSourceDB = Application.ActiveWorkbook.Path & "\" & SourceDB
strDestDB = Application.ActiveWorkbook.Path & "\PN_ACTIVATED\" & DestinationDB

Set dbSource = OpenDatabase(strSourceDB)
Set dbDest = OpenDatabase(strDestDB)

Set rsSource = dbSource.OpenRecordset("SELECT * FROM Activated_Source_TABLE", dbOpenSnapshot)
Set rsDest = dbDest.OpenRecordset("Activated_Source_TABLE1", dbOpenDynaset)

Do While Not rsSource.EOF
    rsDest.AddNew
    For i = 0 To rsSource.Fields.Count - 1
        rsDest.Fields(i).Value = rsSource.Fields(i).Value
    Next i
    rsDest.Update
    rsSource.MoveNext
Loop

rsSource.Close
rsDest.Close
dbSource.Close
dbDest.Close

Set rsSource = Nothing
Set rsDest = Nothing
Set dbSource = Nothing
Set dbDest = Nothing

This approach gives you more control and could help avoid some of the issues you’re encountering with ADODB. It also ensures that each record is transferred one by one, which can be more reliable for larger datasets.

You might want to also make sure that there are no permission issues with either of the databases. Sometimes the error can also stem from restricted access to the files, especially if they are located on a network drive or have certain read/write restrictions.

---

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin