Run-time error 429 ActiveX Component can't create object

[Trebby]

Hi All,

Sorry still very much a novice to VBA and after some expert advice if possible?

When I go to run a particular macro I get eh following message

Run-time error 429 ActiveX Component can't create object

When I click on debug it takes me to the code Set db = OpenDatabase(ActiveWorkbook.Path & "\Bookings.mdb")

Do you know what the cause of this might be?

Thank you

 

[Norie]

Where are you running this code?

If it's in Excel VBA and you are trying to automate Access then I'm pretty sure there's something missing.

Can you post the rest of the code?

 

[Trebby]

Hi Norie,

Thanks for replying and sorry for the delayed response.

I don't think the overall code is incorrect but the database calling code Set db = OpenDatabase(ActiveWorkbook.Path & "\Bookings.mdb"). Since we've updated the company version from 2007 to 2013 we've had a few object issues (one being the calendar form). I am wondering if this code needs to be updated? The paths haven't changed, and if being honest all that has changed was the excel versions.

 

[Trebby]

Below is a code that is run via combo box and pulls the date/year from the database.

Sub updateyear()

'Dim db As Database
Dim rst As Recordset
Dim dat(6000, 2)
Dim yea(5000)
Dim wee(5000)
Dim ist As String
Dim dt As Date
    
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
    
    Set db = OpenDatabase(ActiveWorkbook.Path & "\Bookings.mdb")
    Set rst = db.OpenRecordset("Dates")
        
Application.Calculation = xlManual
Application.ScreenUpdating = False
rst.MoveFirst
i = 1
Do Until rst.EOF
If rst.fields(0) <> "" Then
    dat(i, 1) = rst.fields(0) '
    dat(i, 2) = rst.fields(1) '
    i = i + 1
    Linenum = Linenum + 1
End If
    rst.MoveNext
Loop
rst.Close
db.Close
Set rst = Nothing
Set db = Nothing
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic

'Create list of unique Years
a = 1
b = 0
Do
    c = 0
    Do
    If dat(a, 2) = wee(c) Then Exit Do
    c = c + 1
    Loop Until c = b + 1
    
    If c = b + 1 Then
    If Sheets("name").ComboBox1 = dat(a, 1) Then wee(b + 1) = dat(a, 2)
    b = b + 1
    End If
    
a = a + 1
Loop Until dat(a, 1) = ""
       
    Sheets("name").ComboBox2.Clear
    d = 1
    Do
    If wee(d) <> "" Then
    With Sheets("name").ComboBox2
       .AddItem wee(d)
    End With
    dt = Format(Right(wee(d), 10), "dd/mm/yyyy")
    If Date >= dt And Date <= dt + 7 Then ist = wee(d)
    End If
    d = d + 1
    Loop Until d = b
    
Sheets("name").ComboBox2.Value = ist
Call updatehome
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

end sub

The line that is bold and underlined is the object error - runtime 424,etc.

 

[Norie]

Where are you running this code?

 

[Trebby]

Via a combo box. When the workbook is opened it runs.

Both the workbook and database are located on the same path.

 

[Norie]

So you are running from Excel?

If that's the case OpenDatabase isn't actually part of Excel VBA and you would need a reference to, I think, the Microsoft DAO Object library.

Even with that you wouldn't just use OpenDatabase on it's own.

What are you actually trying to do with the code?

 

[Trebby]

Yep excel. Pulling data from the database and populating cells. As well as sending information back to the dataBase and the populating another sheet when promoted. It's a booking system that is used by different locations but managed centrally. It was working fine 18 months ago prior to the software version update. What would you suggest in using instead as a path?

 

[Norie]

Not sure what you mean by 'path' but I would probably suggest switching to using ADO, I believe that code uses DAO.

Then you could write code that reads/writes data to/from the database without actually directly opening it.

There are also some other things to consider, here you are using a loop to populate an array with the data from the recordset 'Dates'.

Do Until rst.EOF
If rst.fields(0) <> "" Then
    dat(i, 1) = rst.fields(0) '
    dat(i, 2) = rst.fields(1) '
    i = i + 1
    Linenum = Linenum + 1
End If
    rst.MoveNext
Loop

In ADO you can return all the records to an array using GetRows.

Also, what exactly are you doing here?

'Create list of unique Years
a = 1
b = 0
Do
    c = 0
    Do
    If dat(a, 2) = wee(c) Then Exit Do
    c = c + 1
    Loop Until c = b + 1
    
    If c = b + 1 Then
    If Sheets("name").ComboBox1 = dat(a, 1) Then wee(b + 1) = dat(a, 2)
    b = b + 1
    End If
    
a = a + 1
Loop Until dat(a, 1) = ""

The comments mention something about getting unique dates and if that's the case then it might be possible to do with a query in the database, or an SQL statement in the code.

 

[Trebby]

Thanks. It's not my code so currently flicking through it to see what is going on. I think the code you mentioned above is looking for a list of dates.

I suppose my initial question was more about the path and what I mean by that is the location if which both the sheet and database are stored. I was wondering if the code had expired.

Would you be able to suggestion a new/edited code from what I sent

Thanks for the help and support so far.