vba - Is there a way to get ADODB to work with Excel for Mac 2011?

Question

I can not seem to get my Excel workbook (using ADODB) to work with Excel Mac 2011. I am using ADODB code. Are there add-ins available? Even from a third-party? Has anyone gotten this to work?

Answer 1

ADODB is NOT supported in Mac Excel 2011, but ODBC works in conjunction with a 3rd party driver.

I got my ODBC drivers from ActualTech. Download and install their program and you'll have the necessary drivers for connecting to SQL servers and databases (Free to try, $35 to purchase).

The following code creates a connection to a mySQL database, and returns information from the database into Cell A1:

Dim connstring as String
Dim sqlstring as String

connstring = "ODBC;DRIVER={Actual Open Source Databases};" _
& "SERVER=<server_location>;DATABASE=<database>;" _
& "UID=<userID>;PWD=<password>;Port=3306"
sqlstring = "select * from <database_table>"

With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)
  .BackgroundQuery = False
  .Refresh
End With