toni.sanches
17-08-2015 17:59:31
Montei um pequeno cadastro que está em perfeito funcionamento no MySQL.
Estou tentando acesso de conexão ODBC entre Excel e MySQL via VBA através do código abaixo, no entanto estou obtendo como resposta o erro "Erro de sistema &H80004005 (-2147467259). Erro não especificado".
No caso, estou utilizando Microsoft ActiveX Data Objects 6.1 Library e Windows 8 64 bits.
Segue o código:
Public cn As ADODB.Connection
'Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
Sub ADOExcelSQLServer()
'
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim SQLStr As String
Server_Name = "127.0.0.1" ' Enter your server name here
Database_Name = "cadastro" ' Enter your database name here
User_ID = "root" ' enter your user ID here
Password = "<digitei minha senha>" ' Enter your password here
SQLStr = "SELECT * FROM cadastro" ' Enter your SQL here
Set cn = New ADODB.Connection
cn.Open "Driver={MySQL ODBC 5.3 Unicode Driver};Server=" & Server_Name & ";Database=" & Database_Name & _
";Uid=" & User_ID & ";Pwd=" & Password & ";"
End Sub
Private Sub CommandButton21_Click()
' Create a recordset object.
Dim rsMaterialsdb As ADODB.Recordset
Set rsMaterialsdb = New ADODB.Recordset
'connect to your mysql server
ADOExcelSQLServer
With rsMaterialsdb
' Assign the Connection object.
.ActiveConnection = cn
' Extract the required records.
.Open "SELECT * FROM cadastro"
' Copy the records into cell O6 on Sheet1.
Sheet1.Range("C1").CopyFromRecordset rsMaterialsdb
' Tidy up
.Close
End With
rsMaterialsdb.Close 'close recordset
cn.Close 'close connect to db
End Sub
Desde já agradeço a força