vba 如何在 Microsoft Access 2010 中设置到 SQL Server 2008 的 ADODB 连接？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24345895/
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 do I setup an ADODB connection to SQL Server 2008 in Microsoft Access 2010?
提问by sungod916
I just installed SQL Server 2008 on my laptop. I also have Microsoft Access 2010 installed. Using VBA, I am trying to create an ADODB connection to my own database on SQL Server but I'm having trouble finding the right line of code:
我刚刚在我的笔记本电脑上安装了 SQL Server 2008。我还安装了 Microsoft Access 2010。使用 VBA，我试图在 SQL Server 上创建到我自己的数据库的 ADODB 连接，但我无法找到正确的代码行：
When I use this below, it doesn't work. The name of my computer is LAPTOPX and the database is HomeSQL.
当我在下面使用它时，它不起作用。我的电脑名称是 LAPTOPX，数据库是 HomeSQL。
I am sure it's super easy but since I'm just starting out I can't seem to find the right way to ask the question.
我相信这非常简单，但由于我刚刚开始，我似乎无法找到正确的提问方式。
Thanks!
谢谢！
Dim DBCONT As Object
Set DBCONT = CreateObject("ADODB.Connection")
Dim strDbPath As String
strDbPath = "LAPTOPX/HomeSQL"
Dim sConn As String
sConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source =" & strDbPath & ";" & _
"Jet OLEDB:Engine Type=5;" & _
"Persist Security Info=False;"
DBCONT.Open sConn
回答by Horaciux
First, you need to make sure SQL Native Client is instaled. Reference
首先，您需要确保安装了 SQL Native Client。参考
SQL Server 2008
SQL Server 2008
Standard security
标准安全
Provider=SQLNCLI10;Server=myServerAddress;Database=myDataBase;Uid=myUsername;
Pwd=myPassword;
Trusted connection
可信连接
Provider=SQLNCLI10;Server=myServerAddress;Database=myDataBase;
Trusted_Connection=yes;
Connecting to an SQL Server instanceThe syntax of specifying the server instance in the value of the server key is the same for all connection strings for SQL Server.
连接到 SQL Server 实例在服务器键的值中指定服务器实例的语法对于 SQL Server 的所有连接字符串都是相同的。
Provider=SQLNCLI10;Server=myServerName\theInstanceName;Database=myDataBase;
Trusted_Connection=yes;
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim sConnString As String
Dim recordsAffected as Long
'Create connection string
sConnString = "Provider=sqloledb; Server=LAPTOPX; Database=HomeSQL; Trusted_Connection=True;"
'Open connection and execute
conn.Open sConnString
'Do your query
With cmd
.ActiveConnection = conn
.CommandType = adCmdText
.CommandText = "Select ...;"
.Execute recordsAffected 'Includes a return parameter to capture the number of records affected
End With
Debug.Print recordsAffected 'Check whether any records were inserted
'Clean up
If CBool(conn.State And adStateOpen) Then conn.Close
Set cmd = Nothing
Set conn = Nothing
回答by Marcin W?asny
This connetion string works under Excel VBA. In MsAccess also should.
此连接字符串在 Excel VBA 下工作。在 MsAccess 中也应该。
dbName = "test" 'your database name
dbFilePath = "C:\db.mdf" 'your path to db file
connStr = "Driver={SQL Server native Client 11.0};" & _
"Server=(LocalDB)\v11.0;" & _
"AttachDBFileName=" & dbFilePath & ";" & _
"Database=" & dbName & ";" & _
"Trusted_Connection=Yes"
Full solution: http://straightitsolutions.blogspot.com/2014/12/how-to-connect-to-sql-server-local.html
完整解决方案：http: //straightitsolutions.blogspot.com/2014/12/how-to-connect-to-sql-server-local.html
