MySQL connection raises Error 429 activex can't create object

[panoss]

I 'm trying to connect to MySQL database in Access 2007 Win Xp.

 Dim strDataBaseName As String
      Dim strDBCursorType As String
      Dim strDBLockType As String
      Dim strDBOptions As String
      Dim rs As ADODB.Recordset
      Dim cn As ADODB.Connection
  
      On Error GoTo Command1_Click_Error
      Dim b As Long
      strDBCursorType = adOpenDynamic  'CursorType
      strDBLockType = adLockOptimistic   'LockType
      strDBOptions = adCmdText         'Options
  
      Set cn = CreateObject("ADODB.Connection")

The error is raised at: Set cn = CreateObject("ADODB.Connection")

Error 429 activex can't create object.

My references are:
Visual Basic for Applications
Microsoft Access 12.0 object library
OLE automation
Microsoft Office 12.0 Access database engine
Microsoft ActiveX Data Objects 2.7 Library

I tried many things I found in the internet but nothing worked.

Any suggestion is wellcome.

 

[spikepl]

So how about listing what you did try, so we do not have to run down blind alleys?

 

[panoss]

I registered some dlls:
Dao360.dll
scrrun.dll
jscript.dll
vbscript.dll
wshom.ocx
msxml3.dll
shell32.dll
OLEAUT32.DLL

Reinstalled MDAC from XP cd.

I repaired ms Access 2007 (Control Panel -> Add remove Programs -> MS Office Change -> Repair).

Also I reinstalled the whole office, but no success.

And others which I can't recall right now because I 'm struggling with this for days...

 

[JHB]

Could it be because you're missing the word Server in front of it?

Set cn = [B][COLOR=Red]Server.[/COLOR][/B]CreateObject("ADODB.Connection")

I would comment out the error handling until it runs okay.

[B][COLOR=Red]'[/COLOR][/B]On Error GoTo Command1_Click_Error

 

[MarkK]

[COLOR="Green"]'this should create a new instance[/COLOR]
Dim cn As [COLOR="DarkRed"]New[/COLOR] ADODB.Connection

See if that raises the same error.

I think in Access, CreateObject is provided by VBA, so it would be . . .

dim obj as object
set obj = VBA.CreateObject([I]class, [servername][/I])

 

[panoss]

Could it be because you're missing the word Server in front of it?

Set cn = [B][COLOR=Red]Server.[/COLOR][/B]CreateObject("ADODB.Connection")

I would comment out the error handling until it runs okay.

[B][COLOR=Red]'[/COLOR][/B]On Error GoTo Command1_Click_Error

With 'Server': Error 424, object required.
Without 'Server', the good old Error 429.

 

[panoss]

[COLOR=Green]'this should create a new instance[/COLOR]
Dim cn As [COLOR=DarkRed]New[/COLOR] ADODB.Connection

See if that raises the same error.

I think in Access, CreateObject is provided by VBA, so it would be . . .

dim obj as object
set obj = VBA.CreateObject([I]class, [servername][/I])

Yes it raises the same error.

Anyway, I gave up ADO, it doesn't work in this pc.

I made it nice with DAO!! :

Public Function InitConnect() As Boolean
    On Error GoTo ErrHandler

    Dim dbCurrent As DAO.Database
    Dim qdf As DAO.queryDef
    Dim rst As DAO.Recordset
    ServerAddress = "localhost": PortNum = "3306": Opt = "3": DbName = "joomla343": UserName = "root": Password = ""

    strConnection = "ODBC;DRIVER={MySQL ODBC 5.3 Unicode Driver};" & _
                    "Server=" & ServerAddress & ";" & _
                    "Port=" & PortNum & ";" & _
                    "Option=" & Opt & ";" & _
                    "Stmt=;" & _
                    "Database=" & DbName & ";"

    Set dbCurrent = DBEngine(0)(0)
    Set qdf = dbCurrent.CreateQueryDef("")

    With qdf
        .Connect = strConnection & _
                   "Uid=" & UserName & ";" & _
                   "Pwd = " & Password
        .SQL = "SHOW TABLES;"
        Set rst = .OpenRecordset(dbOpenSnapshot, dbSQLPassThrough)
    End With
    
    rst.MoveFirst
    
    Do Until rst.EOF
    
        Debug.Print rst.Fields(0)
        
        rst.MoveNext
    Loop
    
    InitConnect = True

ExitProcedure:
    On Error Resume Next
    Set rst = Nothing
    Set qdf = Nothing
    Set dbCurrent = Nothing
    Exit Function
ErrHandler:
    InitConnect = False
    MsgBox Err.Description & " (" & Err.Number & ") encountered", _
           vbOKOnly + vbCritical, "InitConnect"
    Resume ExitProcedure
    Resume
End Function

 

[AccessBlaster]

Reinstalled MDAC from XP cd.

Try downloading the current version of MDAC from Microsoft.


https://msdn.microsoft.com/en-us/library/ms810805.aspx