Vba mac connects to sql server using ActualTech odbc driver

Question

I have been working on my problem for a while, and in fact I really can’t find an exact and working answer, so here I am ... I am new to vba, I have to admit .......

My problem is this:

I am working on Mac OS X Yosemite since ms office 2011 and I am trying to connect my Excel workbook to sql database. This needs to be done through vba, since later I want to read data from the sql database, and also write data to the database. I could not find a way to establish a connection to this database. I even downloaded the actual odbc driver and installed my dsn (not sure if I did it right, although I couldn’t find how to do this with the sql server on the home page ....)

The following code is all I could find, but I still have errors:

strSRV = "mysql01.gutknecht-net.com" strDB = "gi_kunden" sqlLogin = "TEST" 'has to be changed sqlPW = "TEST_PW" 'has to be changed strConn = "ODBC;DSN=" & strSRV & ";UID=" & sqlLogin & ";PWD=" & sqlPW & ";Database=gi_kunden" With Sheets("Firma").ListObjects sqlCommand = "Select * From tbl_firma" .Add(SourceType:=0, Source:=strConn, LinkSource:=True, Destination:=ActiveWorkbook.Sheets("Firma").Range("A2")).QueryTable 'Get an error here .CommandText = Array(sqlCommand) End With With Sheets("Person").ListObjects sqlCommand = "Select * From tbl_person" .Add(SourceType:=0, Source:=strConn, LinkSource:=True, Destination:=ActiveWorkbook.Sheets("Person").Range("A2")).QueryTable 'Get an error here .CommandText = Array(sqlCommand) End With 

Also tried it with the following code:

 strConn = "Provider=SQLNCLI10;" & _ "Server=" & strSRV & ";" & _ "Database=" & strDB & ";" & _ "UID=" & sqlLogin & ";" & _ "PWD=" & sqlPW & ";" 

but it still does not work ...

If anyone could help me, I would really appreciate it. If you need more information, just say so :)

Answer 1

I finally got a job ......... :)

I changed the code from VBA code to extract data from Mysql DB in Mac Excel 2011 and adapted it to the following:

 Sub SqlConnection() Dim sqlstring As String Dim connstring As String Dim sLogin As String sLogin = "Uid=$;Pwd=$;" sqlstringfirma = "select * from gi_kunden.tbl_firma" sqlstringperson = "select * from gi_kunden.tbl_person" connstring = "ODBC;DSN=KundeDB;" & sLogin ActiveWorkbook.Sheets("Firma").Select ActiveSheet.Range("A1:T2000").Clear Dim qt As QueryTable For Each qt In ActiveSheet.QueryTables qt.Delete Next qt With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstringfirma) .BackgroundQuery = False .Refresh End With ActiveWorkbook.Sheets("Person").Select ActiveSheet.Range("A1:T2000").Clear For Each qt In ActiveSheet.QueryTables qt.Delete Next qt With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstringperson) .BackgroundQuery = False .Refresh End With End Sub 

It works great. It seems after hours and hours of surfing and googling ^^ (halleluja !!)

Thanks anyway:)

Answer 2

I finally got a little more information. I managed to get the data via odbc from the sql database manually, and this is the code I got from the record:

 Sub GetFromSQL() ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$E$3"), , xlYes).Name = _ "Table1" Range("Table1[#All]").Select With ActiveSheet.QueryTables.Add(Destination:=Range("Table1[[#Headers],[ID]]" _ )) .PostText = "ExternalData_1" .Name = True .FieldNames = False .RefreshStyle = xlOverwriteCells .RowNumbers = False .FillAdjacentFormulas = False .HasAutoFormat = False .RefreshOnFileOpen = 1 .BackgroundQuery = False .TablesOnlyFromHTML = True .SaveData = True .Refresh BackgroundQuery:=False .UseListObject = True End With End Sub 

The problem is that I can not re-run this macro as part of where I connect to the database, as well as the query is nowhere to be found here.

Does anyone know what I need to add to this code for it to work?

Thankx for your support.

Cu kath