Error 429 when connect DAO Access

Question

I have the follow VBA code which I use to connect to a MDB data base but I am getting a 429 error when I try to connect DAO.OpenDatabase(DbFile)

I was not getting this error before but now it is happening. On my other computer this connection works fine.

I checked the path of MDB and it is correct.

The references I am using are:
- Visual Basic for Applications
- Microsoft Excel 15.0 Object Library
- OLE Automation
- Microsoft Office 15.0 Object Library
- Microsoft DAO 3.6 Object Library

I am using Excel 2013 (in other computer is Excel 2010) but as I said it was working before on 2013.

Does anyone knows how to get this problem solved, please?

Private Sub IniciaDB()

Dim rs As Recordset
Dim fld As Variant
Dim DbFile as String

DbFile = "D:\Documentos\PMbox\PPMdatabase2.MDB"

On Error Resume Next
Set OpenConn = DAO.OpenDatabase(DbFile)
If Err.Number = 3024 Then MsgBox "Check connection string in the VBA StaticClass object", vbOKOnly

Set ObjAccess = CreateObject("Access.Application")
ObjAccess.Visible = False
ObjAccess.OpenCurrentDatabase (DbFile)

'runSQL ("INSERT INTO tabela_teste VALUES ('My name', 34)")
Set rs = runCursorSQL("SELECT * FROM tabela_teste")

Do While Not rs.EOF
    For Each fld In rs.Fields
        Debug.Print fld.Value & ";";
    Next
    rs.MoveNext
Loop

closeResources

End Sub

The error description is: "ActiveX component can't create object" — Braulio
I might suggest you to use a lower version of DAO library. It is likely that another machine has necessary things installed to be able to open MDB whereas this one might not. Does your machine have similar version of MS-Access on both machines? — shahkalpesh
There is no other DAO version to select. I just removed the DAO reference and then I added it again, when I add I got a error "Error in loading DLL (Error 48)" it seems that the DAO DLL has a problem in my computer :/ — Braulio

Jiri Tersel Jiri Tersel · Accepted Answer · 2015-11-18T15:27:48

I had been struggling with the same issue for quite a long time.
It turned out that my Office was 64bit and so the referenced Microsoft DAO 3.6 Object Library didn't work.

Playing around with the references, it turned out that using Microsoft Office 15.0 Access database engine Object (ACEDAO.DLL) instead of the DAO360 solved the issue.

Link to a similar answer that would have solved my issue if I found it sooner Writing Excel VBA to receive data from Access

Error 429 when connect DAO Access

4 Answers