sql-server - 使用宏的 Excel (MAC) 到 SQL Server 连接

Question

我在 MAC 中有一个启用宏的 Excel 表，其中有一些数据并希望将其导出到 SQL Server。我试图先截断目标表然后插入，但我的代码不起作用。该代码需要在 MAC 和 Windows 上运行。请帮忙。

    Sub Export_Click()
    Dim sqlstring As String
    Dim connstring As String
    Dim sLogin As String
    Dim rng As Range
    Dim defaultDate As String
    Dim sql As String, bulkSql As String
    Dim intImportRow As Integer
    Dim Product, Resource As String
    Dim name, address as String

    'sLogin = "Uid=$;Pwd=$;"
    'sqlstringfirma = "SELECT * from tableA"
    connstring = "ODBC;DRIVER=SQL Server;SERVER=ServerIP;DATABASE=databse;UID=uid;PWD=pwd"
    sql_trunc = "truncate table tableA"
    sql_commit = "commit"
    sql_query = "select * from table tableA"

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

    intImportRow = 10
    
Do Until .Cells(intImportRow, 1) = ""
    Product = .Cells(intImportRow, 1)
    Resource = .Cells(intImportRow, 2)
    sql_insert = "insert into tableA (name, address) values ('" & name & "', '" & address & "')"
    
    With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), 
    sql:=sql_insert)
       .BackgroundQuery = False
       .Refr
    
    intImportRow = intImportRow + 1
            Loop
End Sub

标签： sql-serverexcelvbamacosodbc