VBA MS Excel MySQL Connection with ODBC
Hi I have several days and nights trying to get Exchange data between Excel and MySQL, my knowledge of English and VBA is not advanced by that request benevolence.
After you install the ODBC 3.5 connector and add the reference to the Activex to run the code does not give me any error, but not extracted from the Mysql database, this is the modified code:
Code:
Option Explicit
Option Base 1 Sub excelmysql()
VBA to perform various actions on MySQL tables using VBA
PLEASE DO THE FOLLOWING BEFORE EXECUTING CODE:
1)In VBE you need to go Tools/References and check Microsoft Active X Data Objects 2.x library
2)Install MySQL ODBC 3.51 Driver. See dev.mysql.com/downloads/connector/odbc/3.51.html or google MySQL ODBC 3.51 Driver
-
Connection variables
Dim conn As New ADODB.Connection
Dim server_name As String
Dim database_name As String
Dim user_id As String
Dim password As String
Table action variables
Dim i As Long counter
Dim sqlstr As String SQL to perform various actions
Dim table1 As String, table2 As String
Dim field1 As String, field2 As String
Dim rs As ADODB.Recordset
Dim vtype As Variant
-
Establish connection to the database
server_name = 127.0.0.1 Enter your server name here if running from a local computer use 127.0.0.1
database_name = pruebas Enter your database name here
user_id = prueba enter your user ID here
password = 12345678 Enter your password here
Set conn = New ADODB.Connection
conn.Open DRIVER={MySQL ODBC 3.51 Driver} _
& ;SERVER= & server_name _
& ;DATABASE= & database_name _
& ;UID= & user_id _
& ;PWD= & password _
& ;OPTION=16427 Option 16427 = Convert LongLong to Int: This just helps makes sure that large numeric results get properly interpreted
-
Various Actions
Define variables (not all of the variables will be required for each action)
vtype = Array(Text, LongText, Int(10), Float, Double, Date, Time) array of commonly used MySQL variable types
table1 = prueba
table2 = resultado
field1 = dato1
field2 = dato2
Extract MySQL table data to first worksheet in the workbook
GoTo skipextract
Set rs = New ADODB.Recordset
sqlstr = SELECT * FROM tabla1 extracts all data
rs.Open sqlstr, conn, adOpenStatic
With Worksheets(Hoja1).Cells(A1) Enter your sheet name and range here
.ClearContents
.CopyFromRecordset rs
End With
skipextract:
Write new entries to a table from the first sheet of the workbook
GoTo skipwrite
With Sheets(1)
For i = 1 To 10000
If Cells(i, 1) = Then Exit For
sqlstr = INSERT INTO & table2 & SET _
& field1 & = & Cells(i, 1) & , _
& field2 & = & Cells(i, 2) &
conn.Execute sqlstr
Next i
End With
skipwrite:
Close connections
On Error Resume Next
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
On Error GoTo 0
End Sub
the version of excel is the 2010, the version of Mysql is the 5.7
I much appreciate your time and patience.
