vba mac connect to sql server with ActualTech odbc

问题:

I have been working on my problem for a while now and I don't really seem to find an accurate and working answer, so here I am... I'm a newby with vba I have to admit.......

My problem is the following:

I'm working on Mac OS X Yosemite with ms office 2011 and I'm trying to connect my Excel Workbook to a sql-database. This has to be done through vba as later on I want to read data from the sql-database as well as write data to the database. I couldn't find a way to establish a connection to that database. I even downloaded the actualtech odbc driver and setup my dsn (not sure if I did it right though, couldn't find how to do it with a sql-server on a homepage....)

The following Code is all I could find but I still get Errors here:

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 didn't work...

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

回答1:

I finally got it working......... :)

I changed the code from VBA code to fetch 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

This works fine it seems.... After hours and hours of surfing and googling ^^ (halleluja!!)

Thanks anyway :)

回答2:

I finally got a little more information. I managed to get the data through odbc from the sql-database manually and this is the code I got from recording it:

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

Problem is, I'm not able to re-run this macro as the part where I connect to the database as well as the query are nowhere to be found here.

Does anyone have a clue, what I need to add to this code, so it'll work?

Thankx for your support.

CU Kath