如何创建一个连接到SQL Server的ODBC连接？

Question

如何创建一个连接到SQL Server的ODBC连接？

sqlsql-servervbams-access

3

我尝试使用Access调用SQL Server中的存储过程。但是在建立ODBC连接时遇到了麻烦，不知道我是否漏掉了什么？或者只需要在SQL站点上进行一些设置？

我有一个屏幕如下所示：

enter image description here

OK按钮后面的代码是这样的：

      Dim dbPUBS As dao.Database
      Dim tdfPUBS As dao.TableDef
      Dim qdfPUBS As dao.QueryDef
      Dim strMsg As String
      Dim strSQL As String

  ' Check for existence of Server, Database and User Name.
          ' If missing, inform user and exit.

             If IsNull(Me!txtServer) Then
        strMsg = "Enter name of your company's Server." & _
            & "(See your database administrator)"
        MsgBox strMsg, vbInformation, "Missing Data"
        Me!txtServer.SetFocus
    ElseIf IsNull(Me!txtDatabase) Then
        strMsg = "Enter name of database. (Example: xxxx)"
        MsgBox strMsg, vbInformation, "Missing Data"
        Me!txtDatabase.SetFocus
    ElseIf IsNull(Me!txtUID) Then
        strMsg = "Enter user login.  (Example: xx)" = ""
        MsgBox strMsg, vbInformation, "Missing Data"
        Me!txtDatabase.SetFocus
    Else
        strServer   = Me!txtServer
        strDatabase = Me!txtDatabase
        strUID      = Me!txtUID
        ' Password may be NULL, so provide for that possibility
        strPWD      = Nz(Me!txtPWD, "")

        ' Prepare connection string
        strConnect = "ODBC;DRIVER={SQL Server}" _
                & ";SERVER=" & strServer _
                & ";DATABASE=" & strDatabase _
                & ";UID=" & strUID _
                & ";PWD=" & strPWD & ";"
    End If


            Private Function ValidateConnectString() As Boolean
           On Error Resume Next

            Err.Clear
            DoCmd.Hourglass True

       ' Assume success

       ValidateConnectString = True

' Create test Query and set properties

        Set qdfPUBS = dbPUBS.CreateQueryDef("")
         qdfPUBS.Connect = strConnect
        qdfPUBS.ReturnsRecords = False
        qdfPUBS.ODBCTimeout = 5

' Attempt to delete a record that doesn't exist

          qdfPUBS.SQL = "DELETE FROM Authors WHERE au_lname = 'Lesandrini'"

' Simply test one Pass Through query to see that previous
' connect string is still valid (server has not changed)

           qdfPUBS.Execute

' If there was an error, connection failed

          If Err.Number Then ValidateConnectString = False

          Set qdfPUBS = Nothing
          DoCmd.Hourglass False

End Function

- Lan Cui

1

你本可以用谷歌搜索一下，就能找到这个链接：http://www.sqlstrings.com/SQL-Server-connection-strings.htm - Thorsten Dittmar

谢谢，我的连接字符串已经设置好了。但是它不起作用。我只是有点困惑。抱歉。 - Lan Cui

1

但是根据我引用的页面，您的连接字符串似乎有误。例如，它不应该写成 DRIVER={SQL SERVER} 而应该写成 DRIVER={SQLServer}！ - Thorsten Dittmar

2个回答

1

这是错误的

    strConnect = "ODBC;DRIVER={SQL Server}" _
            & ";SERVER=" & strServer _
            & ";DATABASE=" & strDatabase _
            & ";UID=" & strUID _
            & ";PWD=" & strPWD & ";"

它应该读取

    strConnect = "DRIVER={SQL Server Native Client 10.0}" _
            & ";SERVER=" & strServer _
            & ";DATABASE=" & strDatabase _
            & ";UID=" & strUID _
            & ";PWD=" & strPWD & ";"

- Thorsten Dittmar

谢谢，抱歉连接仍然失败。 - Lan Cui

我已经编辑了我的回答。这是为了连接SQL Server 2008。请访问http://www.connectionstrings.com/获取更多详细信息。 - Thorsten Dittmar

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- user2299169 · Accepted Answer

你应该访问ConnectionStrings网站获取详细信息，不过如果我是你，我不会使用ODBC。
我的连接方式（适用于SQL Server 2012）：

Private oCon As ADODB.Connection

Public Sub InitConnection(ByRef sDataSource As String, ByRef sDBName As String)
Dim sConStr As String

    Set oCon = New ADODB.Connection
    sConStr = "Provider=MSDataShape;Data Provider=SQLNCLI11;" & _
              "Integrated Security=SSPI;Persist Security Info=False;Data Source=" & _
               sDataSource & ";Initial Catalog=" & sDBName
    On Error Resume Next
        Call oCon.Open(sConStr)
        If (Err.Number = 0) Then
            'all OK
        Else
            'Show Error Message / Throw / Sink / etc
        End If
    On Error GoTo 0
End Sub

其中 sDataSource 是 "[计算机名称]\[SQL SERVER 实例]"（与 SSMS 中的相同，类似于 "MyHomePC\SQLEXP"），sDBName 是默认的目录，即要打开的默认数据库。您需要添加对 Microsoft ActiveX 数据对象 的引用，以便可以使用 ADODB 的 Connection、Command 和 Recordset 对象（在 Access VB 窗口中："工具" --> "引用..."）。
MSDataShape 不是必需的，但对于分层网格来说非常方便。

编辑：顺便提一下，从 connstr. 网站上看：Driver={SQL Server Native Client 11.0};Server=myServerAddress;Database=myDataBase; Uid=myUsername;Pwd=myPassword; （同样适用于 SQL Server 2012，对于 2008 版本则为 "...Client 10."）