使用ActualTech odbc驱动程序连接到SQL Server-Mac上的VBA Office 2011 [英] Connect to sql server with ActualTech odbc Driver - VBA Office 2011 on Mac

问题描述

我正在使用MS Office 2011在Mac OS X Yosemite上工作.我正在尝试将Excel Workbook连接到sql数据库.这必须通过VBA完成，因为稍后我要读取数据以及将数据写入数据库.

I'm working on Mac OS X Yosemite with MS Office 2011. I'm trying to connect my Excel Workbook to a sql-database. This has to be done through VBA as later I want to read as well as write data to the database.

我找不到建立与该数据库的连接的方法.我下载了actualtech odbc驱动程序并设置了dsn(虽然不确定我是否做对了，但找不到主页上的sql-server如何做到这一点).

I couldn't find a way to establish a connection to that database. I 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. I get errors:

strSRV = "server_name"
strDB = "database_name"
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

还尝试了以下代码:

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

推荐答案

我终于开始使用它了.........:)

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!!)

仍然感谢:)

这篇关于使用ActualTech odbc驱动程序连接到SQL Server-Mac上的VBA Office 2011的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！