连接到VBA上的本机客户端11

Question

我有一些工作良好的vba代码，但我现在必须从不同的数据库运行此代码的数据。新的数据库驱动程序是SQL Server Native Client 11。我怀疑连接字符串有问题，因为它是一个不同的驱动程序。如果我只是替换"provider="{SQL Server Native Client 11.0}或者是否需要声明所有的新变量，等等…

Private Sub sqlQueryScript()
On Error GoTo CleanUp
'Declare variables'
    Dim user As String
    Dim conn As Object
    Dim rs As Object
    Dim strSql As String
    Set conn = CreateObject("ADODB.Connection")
    
'Open receipt connection'
    user = Environ("username")
    conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:Users" & user & "Desktopdb" & dbName
    conn.Open
    
'Set and Excecute SQL Command'
    strSql = query
'Open Recordset'
    Set rs = conn.Execute(strSql)
    rs.Close
    rs.Open strSql
    
'Copy Data to Excel'
    Set data = Workbooks(fName).Sheets(fac)
    data.Range("A2").CopyFromRecordset rs
    
'Copy Header to Excel
    For i = 0 To rs.Fields.Count - 1
        data.Cells(1, i + 1).Value = rs.Fields(i).Name
    Next i
CleanUp:
'Close Connection'
    conn.Close
End Sub

Answer 1

您可以尝试使用以下连接字符串连接Windows身份验证:

Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DatabaseName;Data Source=ServerName

Answer 2

SQL Server Native Client 11 Ole db provider的连接字符串如下:

Provider=SQLNCLI11.1;Integrated Security=SSPI;Initial Catalog=DatabaseName;Data Source=ServerNameInstanceName

如果SQL Server实例是默认实例，则只使用服务器名称Data Source=ServerName

你不需要再改变任何东西了。也许你需要更改查询以与SQL Server兼容。