VBA MS Excel MySQL Connection with ODBC

[kraquem]

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:

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.

[mancubus]

welcome to the forum.

delete two GoTo statements and their corresponding labels.
"GoTo skipextract", "skipextract:", "GoTo skipwrite", "skipwrite:".

the line(s) between a GoTo statement and its label will not be executed.