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"
Resolution:
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=10.0.0.49;Database=bugs;User=root; Password=j2ee911pw;"
'ConnectionString = "DRIVER={MySQL ODBC 5.1 Driver}; SERVER=10.0.0.49; 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
rs.MoveFirst
While Not rs.EOF
MsgBox rs.Fields(0)
rs.MoveNext
Wend
cn.Close
MsgBox "End of program"
End Sub