MySQL Forums
Forum List  »  Connector/ODBC

Excel vba ADODB with MySQL
Posted by: AMERICO BRAVO ASTROÑA
Date: October 27, 2016 11:45AM

Hi I need to connect to a MySQL DB from excel, I found this example:

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

modify it to my requirements, but at the time of run it does nothing, nor it gives me 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

'---------------------------------------------
' Extract MySQL table data to first worksheet in the workbook
GoTo skipextract
Set rs = New ADODB.Recordset
sqlstr = "select dato1 from datos where IdDato=1" ' 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:

'-----------------------------------------------------------------------
' Close connections
On Error Resume Next
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
On Error GoTo 0
End Sub
=============================================

I much appreciate your attention and time.

Best regards.

Options: ReplyQuote


Subject
Written By
Posted
Excel vba ADODB with MySQL
October 27, 2016 11:45AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.