Connetcion Excel MySQL with ADODB

Hello to all participants, must extract an excel sheet data from MySQL once extracted these data excel sheet processed with many functions, the result of that calculation must be stored in MySQL. the first thing I'm doing is connect me to MySQL so I found this page:

http://forums.mysql.com/read.php?10,100302

the example I'm using this part of code:

Option Explicit 
Option Base 1 

Sub excelmysql() 
' VBA to perform various actions on MySQL tables using VBA 
' Majority of the original code adapted from Carlmack http://www.ozgrid.com/forum/showthread.php?t=46893 

' 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 
' Close connections 
On Error Resume Next 
rs.Close 
Set rs = Nothing 
conn.Close 
Set conn = Nothing 
On Error GoTo 0
End Sub

When you run the application with F5 gives me Automation error at run time.

I'm using the ODBC connector of MySQL 5.3.6 MySQL version 5.7 Excel 2010 version previously used version 2016 that he thought that the error had to do with the version. Version OS Windos 7 Microsoft Activex Data Object 2.8 Library"

Thank for your time a attention.

Best regards.

0
edit delete
are you sure?
asked Americo Bravo

3 answers

HOL I answer myself remove the current version of the ODBC and install version 3.5 as indicated in the commentary of the code, already not gives me error, but when you try to run the query does not display any data nor gives error.

Option Explicit 
Option Base 1 

Sub excelmysql() 
' VBA to perform various actions on MySQL tables using VBA 
' Majority of the original code adapted from Carlmack http://www.ozgrid.com/forum/showthread.php?t=46893 

' 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 = "origen" 
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 " & table1 ' 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

Thaks for your time.

Best regards.

1
edit delete
are you sure?
answered Americo Bravo

You can easily download tinder app for pc without any 3rd Party software.

0
edit delete
are you sure?
answered Lynette Spicer

Could you please share video tutorial or any link which is all about Connection Excel My SQL with ADODB it will help for my website UK Dissertation anyone please.

0
edit delete
are you sure?
answered Sarah Taylor

Post an answer but please login first

Post Answer
  1. Please login to post answer

Similar Questions

Tagsfor this question

Forumall questions