我尝试使用Access来调用SQL Server中的存储过程。但是很难建立
ODBC连接,我不知道我缺少什么吗?还是只需要在sql站点中进行一些设置?

我有一个这样的屏幕:



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

最佳答案

您应该访问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是“ [COMPUTERNAME] \ [SQL SERVER INSTANCE]”(与SSMS中的相同,就像“ MyHomePC \ SQLEXP”),而sDBName是默认目录,即要打开的默认DB。您需要添加对Microsoft ActiveX Data Objects的引用,以便可以使用ADODB ConnectionCommandRecordset对象(在Access VB窗口中:“工具”->“参考...”)。 MSDataShape不是强制性的,但对于分层网格非常有用。编辑:BTW,来自connstr。站点:Driver={SQL Server Native Client 11.0};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;(同样,对于SQL Server 2012,对于2008,它是“ ...客户端10”。)

09-26 09:27
查看更多