vba 如何创建到 SQL Server 的 ODBC 连接？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16441361/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to create a ODBC connection to SQL Server?
提问by Lan Cui
I try to use Access to call a stored procedure in SQL Server. But have trouble to build the ODBC connection, I do not know am I missing something? Or just need do some set in sql site?
我尝试使用 Access 在 SQL Server 中调用存储过程。但是建立ODBC连接有问题，我不知道我错过了什么？或者只需要在 sql 站点中做一些设置？
I have a screen like this:
我有一个这样的屏幕：
and code behind the OK button is this:
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
回答by Lan Cui
You should pay a visit to ConnectionStringssite for details, however, I wouldn't use ODBC if I were you.
My connection is (for SQL Server 2012):
您应该访问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 SubWhere sDataSourceis "[COMPUTERNAME]\[SQL SERVER INSTANCE]" (same as in e.g. SSMS, it's like "MyHomePC\SQLEXP") and sDBNameis the default catalog, ie default DB to open. You'll need to add reference to Microsoft ActiveX Data Objectsso you can use ADODBConnection, Commandand Recordsetobjects (in Access VB window: "Tools" --> "References...").MSDataShapeis not mandatory but comes handy for hierarchical grids.
EDIT: BTW, from connstr. site: Driver={SQL Server Native Client 11.0};Server=myServerAddress;Database=myDataBase;
Uid=myUsername;Pwd=myPassword;(again, for SQL Server 2012, for 2008 it's "...Client 10.")
sDataSource“[计算机名称]\[SQL SERVER INSTANCE]”在哪里（与例如 SSMS 中的相同，类似于“MyHomePC\SQLEXP”）并且sDBName是默认目录，即要打开的默认数据库。您需要添加对的引用，Microsoft ActiveX Data Objects以便可以使用ADODBConnection,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，它是“...客户端 10。”）
回答by Thorsten Dittmar
This is wrong
这是错误的
strConnect = "ODBC;DRIVER={SQL Server}" _
& ";SERVER=" & strServer _
& ";DATABASE=" & strDatabase _
& ";UID=" & strUID _
& ";PWD=" & strPWD & ";"
It should read
它应该读
strConnect = "DRIVER={SQL Server Native Client 10.0}" _
& ";SERVER=" & strServer _
& ";DATABASE=" & strDatabase _
& ";UID=" & strUID _
& ";PWD=" & strPWD & ";"
