VBA problem - error 429

[Anchundan]

I've got a problem with one of my spreadsheets, which I never had before. I used to work on my old computers, but won't work on my new laptop, or a friend's laptop. I scoured the internet to no avail, and can't fix my problem.

It's simply an Excel spreadsheet which uses DAO to access a database (Access 2003). Whenever it gets to 'OpenDatabase' it has the error message "Run-time error '429'; ActiveX component can't create object".

I've re-written the code to illustrate the problem :

Sub GetBarges()

Dim DB As Database
Dim RS As Recordset

' C:\Database\Barge.mdb
' C:\Database\Barge-2003.mdb
' C:\Database\Project48-2003.mdb


' Open the Barge database, move down to the relevent barge
Set DB = OpenDatabase("C:\Database\Barge-2003.mdb")
Set RS = DB.OpenRecordset("Barges")
Do Until RS.EOF = True
Debug.Print RS.Fields("BargeName")
RS.MoveNext
Loop

RS.Close
DB.Close

End Sub​

I have referenced Microsoft DAO 3.6 Object Library, and the file exists in
C:\Program Files (x86)\Common Files\microsoft shared\DAO dao360.dll is version 3.60.9756.0.

Weirdly, when I run it in VB6, it works properly, using the same DAO360.dll.

Any ideas?

 

[andshrew]

What version of office are you using, and what references do you have set up?

You could try explicitly declaring your object types as there may be a conflict if you also have references to ADO:

Dim db As DAO.Database
Dim rs as DAO.Recordset

You could also try the following code which will confirm if Excel can actually access the DAO library.

Sub testDAO()

    On Error GoTo err
    MsgBox DBEngine.Version, vbOKOnly, "DAO Version"
    Exit Sub
err:
    MsgBox err.Number & ": " & err.Description, vbCritical + vbOKOnly, "Error"
    
End Sub

 

[dazzerd]

Yeh if you are using 2010 just specifying "database" as your object will confuse things. Database is the standard name for MS Access 2010 14 object library.

Fug knows what the object actually does as I have never bothered to investigate it but as above make sure you reference dao 3.6 and suffix the objects correctly.

 

[Anchundan]

What version of office are you using, and what references do you have set up?

You could try explicitly declaring your object types as there may be a conflict if you also have references to ADO:

Dim db As DAO.Database
Dim rs as DAO.Recordset

You could also try the following code which will confirm if Excel can actually access the DAO library.

Sub testDAO()

    On Error GoTo err
    MsgBox DBEngine.Version, vbOKOnly, "DAO Version"
    Exit Sub
err:
    MsgBox err.Number & ": " & err.Description, vbCritical + vbOKOnly, "Error"
    
End Sub

I'm using MS Office Professional Plus 2010, Version 14.0.7116.500 (64-bit).

References :

Visual Basic for Applications
Microsoft Excel 14.0 Object Library
Microsoft DAO 3.6 Object Library.

I've tried :

Dim db As DAO.Database
Dim rs as DAO.Recordset

But I just get the same error.

I also tried the test code, which generates the error : 429: ActiveX component can't create object.

I tried the same spreadsheet on a brand new desktop computer, where I had just installed Office 2010 (same version as I have, but I didn't install SP2) and the code worked. It has the same version of DAO360.dll, so I'm really confused.



EDIT: I just went back to that other desktop and installed Office SP2. The code still works. I noticed that the desktop is running Office 32-bit not 64-bit. So it seems the code does not work on 64-bit versions of Office 2010. I'm not sure why.

 

[andshrew]

EDIT: I just went back to that other desktop and installed Office SP2. The code still works. I noticed that the desktop is running Office 32-bit not 64-bit. So it seems the code does not work on 64-bit versions of Office 2010. I'm not sure why.

The reason is that there isn't a 64bit version of DAO. Easiest solution is to re-install using the 32bit version of Office (there's really very little benefit of running 64bit unless your working with massive spreadsheets).

Otherwise you would need to change your code from DAO to ADO which as far as I know works. You would need to remove references to DAO and replace with Microsoft ActiveX Data Objects Library.

 

[Anchundan]

Thanks for the replies. Unfortunately, that's the main reason I use Office - massive spreadsheets with hundreds of worksheets!! I think I'll rewrite the code for ADO!