vba mac连接到sql server与ActualTech odbc驱动程序 [英] vba mac connect to sql server with ActualTech odbc Driver

问题描述

我一直在努力解决我的问题一段时间，我似乎并没有找到一个准确和工作的答案，所以在这里我是...我是一个新的vba我不得不承认... ....

我的问题如下：

我正在使用Mac OS X优胜美地ms office 2011，我正在尝试将我的Excel工作簿连接到一个sql数据库。这必须通过vba完成，稍后我想从sql-database读取数据以及将数据写入数据库。我找不到建立与该数据库的连接的方法。我甚至下载了actualtech odbc驱动程序并设置了我的dsn（不知道如果我做了对，但是找不到如何使用主页上的sql-server进行操作....）

以下代码是我可以找到的，但我仍然在这里收到错误：

  strSRV =mysql01 
 strDB =gi_kunden
 sqlLogin =TEST必须更改
 sqlPW =TEST_PW必须更改
 
 strConn =ODBC; DSN =& strSRV& ; UID =& sqlLogin& ; PWD =& sqlPW& ;数据库= 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'在这里获取错误
 .CommandText = Array（sqlCommand）
 End With 
 
带表格（Person）ListObjects 
 sqlCommand =选择*从tbl_person
 .Add（SourceType：= 0，Source：= strConn，LinkSource：= True ，Destination：= ActiveWorkbook.Sheets（Person）。Range（A2））QueryTable'在这里获取错误
 .CommandText = Array（sqlCommand）
结束
  

还尝试了以下代码：

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

但它仍然没有工作...

如果有人能帮助我，我将非常感谢。如果您需要更多信息，请说：）

解决方案

我终于得到它的工作........ 。：）

我将代码从在Mac Excel 2011中从Mysql DB获取数据的VBA代码，并将其修改为以下内容：

  Sub SqlConnection（）
 Dim sqlstring As String 
 Dim connstring As String 
 Dim sLogin As String 
 sLogon = UID = $; PWD = $; 
 sqlstringfirma =select * from gi_kunden.tbl_firma
 sqlstringperson =select * from gi_kunden.tbl_person
 connstring =ODBC; DSN = KundeDB &安培; sLogon 
 
 ActiveWorkbook.Sheets（Firma）。选择
 ActiveSheet.Range（A1：T2000）。清除
 
 Dim qt As QueryTable 
对于每个qt在ActiveSheet.QueryTables 
 qt.Delete 
下一个qt 
使用ActiveSheet.QueryTables.Add（Connection：= connstring，Destination：= Range（A1），Sql：= sqlstringfirma）
 .BackgroundQuery = False 
 .Refresh 
结束
 
 ActiveWorkbook.Sheets（Person）。选择
 ActiveSheet.Range（A1 ：T2000）。清除
 
对于每个qt在ActiveSheet.QueryTables 
 qt.Delete 
下一个qt 
使用ActiveSheet.QueryTables.Add（Connection：= connstring，目的地：=范围（A1），Sql：= sqlstringperson）
 .BackgroundQuery = False 
 .Refresh 
结束
 End Sub 
  

这工作正常似乎....经过几个小时的冲浪和谷歌搜索^^（halleluja !!）

无论如何感谢：）

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

解决方案

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
    sLogon = "Uid=$;Pwd=$;"
    sqlstringfirma = "select * from gi_kunden.tbl_firma"
    sqlstringperson = "select * from gi_kunden.tbl_person"
    connstring = "ODBC;DSN=KundeDB;" & sLogon

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

这篇关于vba mac连接到sql server与ActualTech odbc驱动程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！