Applies To:

MySQL 5.1.44

Windows 7

ODBC connection

Microsoft Office 2007 Excel 


Issue Summary:

When trying to connect to a MySQL database using VBA in Excel Visual Basic IDE, an error occurs when you try to open the connection.  Microsoft Visual Basic "Run-time error '-2147467259 (80004005)': Automation Error Unspecified Error"



I pasted my code below, but you might need more than just the code.  

First, verify in Tools->References that Microsoft ActiveX Data Objects 6.0 Library is selected

Second, make sure you have downloaded the database driver.  In my case, it was "Connector / ODBC 5.2.6".  You have the option to download x86 or x64 bit.  Microsoft claims having both doesn't hurt anything.  

After you install the driver, make sure you configure Data Sources (ODBC).  For the 64 bit driver, you can configure this in Control Panel -> Administrative Tools -> Data Sources.  Create a System DSN instead of a user DSN.  

Also, if you downloaded the 32bit you need to configure this in c:\windows\SysWOW64\odbcad32.exe.  Be sure to create a System DSN instead of a user DSN.  This make a difference.  I had to configure the 32 bit driver and also include the database name when you set this up.  It will make your life easier.  

If you cannot connect to the database you have to tell the database to accept connections from people other than localhost.  

Sub ConnectToMySQL()

Dim cn, rs

'You can create your objects this way too

'Dim cn As ADODB.Connection

'Set cn = New ADODB.Connection

'Dim rs As ADODB.Recordset

'Set rs = New ADODB.Recordset

i = 0

'Or you can create your objects this way

Set cn = CreateObject("ADODB.Connection")

Set rs = CreateObject("ADODB.Recordset")

'Any of these connection strings should work

ConnectionString = "Driver={MySQL ODBC 5.1 Driver};Server=BUGZILLA-SERVER; Database=bugs;User=root; Password=j2ee911pw;"

 'ConnectionString = "DRIVER={MySQL ODBC 5.1 Driver}; DSN=BugZilla; SERVER=BUGZILLA-SERVER; PORT=3306; DATABASE=bugs; USER=root; PASSWORD=j2ee911pw; OPTION=3;"

 'ConnectionString = "Driver={MySQL ODBC 5.1 Driver};Server=;Database=bugs;User=root; Password=j2ee911pw;"

 'ConnectionString = "DRIVER={MySQL ODBC 5.1 Driver}; SERVER=; DATABASE=bugs; UID=root;PASSWORD=j2ee911pw;"

'Because we already have a DSN set up there is no need for the long connection strings

'we can use the following since all that info is already stored

'cn.Open "DSN=BugZilla"

'You must use 'DSN=' because the following will not work

      'cn.Open "BugZilla"

'Or you can pass the connection string

cn.Open ConnectionString

rs.Open "select * from attach_data", cn, 3


While Not rs.EOF

    MsgBox rs.Fields(0)




MsgBox "End of program"

End Sub