VBA code to fetch data from Mysql DB in Mac Excel 2011

[uni9e]

I was using ADODB code in Windows to fetch data from Mysql Database and it was working fine. However, I can not seem to get my Excel workbook (using ADODB) to work with Excel Mac 2011. After a lot of googling, I found an ODBC connector from Actual tech and I was able to fetch three rows using Microsoft Query. But I want this to be done using VBA code but have been unable to do so. Has anyone gotten this to work? If yes, can you please provide me with a sample code. Thanks in Advance !!

P.S: I know there is a similar existing question but the link provided in the answer is no longer working. hence, I have asked a new question

Also, If anyone needs the link to do it through Microsoft Query, here is the link:Generate a report from a database

Here is the code I use in Windows:

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit;"> Sub getMysqlDBdata()
Dim Cn As Object
Dim sqlQa as string
dim temparray1 as variant
Source = "MySQL"
mysql_driver = "MySQL ODBC 5.2 ANSI Driver"
sqlQa = "select * from homeunion.propertydata;"
Set Cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Cn.Open "Driver={MySQL ODBC 5.2 ANSI Driver };Server= 127.0.01 ;Database= test;UID= root ;PWD= 12345"
rs.Open sqlQa, Cn, adOpenStatic
temparray1 = rs.GetRows()
rs.Close
Set rs = Nothing
End Sub</code>

 

[mole999]

As long as your references exist to ADO etc I use

Sub Master()
    Dim adoConn As New ADODB.Connection
    Dim adoRs As New ADODB.Recordset
    Dim sConn As String
    Dim sSql As String
    Dim sOutput As String
    Dim Var1 as String
    Dim rnStart As Range
    
    Set rnStart = Sheets("Reference Lookup").Range("A24")
    
    sConn = "Provider=SQLOLEDB; Data Source =***** ; Initial Catalog = ******; User Id = *******; Password=*******"

I use http://www.dpriver.com/pp/sqlformat.htm and output as VB/ASP as the output to wrap the entire string

    sSql = Var1

    Set adoConn = New ADODB.Connection
    adoConn.Open sConn

    Set adoRs = New ADODB.Recordset
    adoRs.Open Source:=sSql, _
               ActiveConnection:=adoConn

    On Error Resume Next
    If Not (adoRs.BOF Or adoRs.EOF) Then
        Do While Not adoRs.EOF

            rnStart.CopyFromRecordset adoRs    'rst

            adoRs.MoveNext  'When you code has been running for 1/2 an hour, you
            'probably forgot this line.  I know I forget it all the time.
        Loop

        'Close stuff


        'Get rid of the last comma
        sOutput = Left(sOutput, Len(sOutput) - 1)
    Else
        sOutput = "No Matched Data"
        Sheets("Reference Lookup").Range("A24") = sOutput
    End If

    'Output

    adoRs.Close
    adoConn.Close

    Set adoRs = Nothing
    Set adoConn = Nothing
    
End Sub

 

[uni9e]

Hi,

Thanks for your reply but ADODB objects(ActiveX controls to be more precise) are not supported in MAC. I came across this link which has a sample code that can be used in mac:
QueryTable object not the same as on PC - Microsoft Community

As long as your references exist to ADO etc I use

Sub Master()
    Dim adoConn As New ADODB.Connection
    Dim adoRs As New ADODB.Recordset
    Dim sConn As String
    Dim sSql As String
    Dim sOutput As String
    Dim Var1 as String
    Dim rnStart As Range
    
    Set rnStart = Sheets("Reference Lookup").Range("A24")
    
    sConn = "Provider=SQLOLEDB; Data Source =***** ; Initial Catalog = ******; User Id = *******; Password=*******"

I use http://www.dpriver.com/pp/sqlformat.htm and output as VB/ASP as the output to wrap the entire string

    sSql = Var1

    Set adoConn = New ADODB.Connection
    adoConn.Open sConn

    Set adoRs = New ADODB.Recordset
    adoRs.Open Source:=sSql, _
               ActiveConnection:=adoConn

    On Error Resume Next
    If Not (adoRs.BOF Or adoRs.EOF) Then
        Do While Not adoRs.EOF

            rnStart.CopyFromRecordset adoRs    'rst

            adoRs.MoveNext  'When you code has been running for 1/2 an hour, you
            'probably forgot this line.  I know I forget it all the time.
        Loop

        'Close stuff


        'Get rid of the last comma
        sOutput = Left(sOutput, Len(sOutput) - 1)
    Else
        sOutput = "No Matched Data"
        Sheets("Reference Lookup").Range("A24") = sOutput
    End If

    'Output

    adoRs.Close
    adoConn.Close

    Set adoRs = Nothing
    Set adoConn = Nothing
    
End Sub

 

[mole999]

maybe this has help

osx - Is there a way to get ADODB to work with Excel for Mac 2011? - Stack Overflow

 

[Jim Gordon]

Hi,

I have a detailed tutorial for using ODBC and SQL in VBA on the Mac. There is example code in part 22 of the tutorial. It's here:
Using Excel as a Relational Database