如何设置在Microsoft Access 2010的ADODB连接到SQL Server 2008

我刚刚安装了SQL Server 2008上我的笔记本电脑。 我也有Microsoft Access 2010安装。 使用VBA，我想创建SQL Server上的ADODB连接到我自己的数据库，但我无法找到的代码行权：

当我使用这下面，这是行不通的。 我的计算机的名称是LAPTOPX和数据库是HomeSQL。

我相信它的超级容易的，但因为我刚开始我似乎无法找到正确的方式来问这个问题。

谢谢！

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

Answer 1:

首先，你需要确保SQL Native Client的是instaled。 参考

SQL Server 2008中

标准安全

Provider=SQLNCLI10;Server=myServerAddress;Database=myDataBase;Uid=myUsername;
Pwd=myPassword;

受信任的连接

Provider=SQLNCLI10;Server=myServerAddress;Database=myDataBase;
Trusted_Connection=yes;

连接到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

Answer 2:

这引黄串在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"

完整的解决方案： http://straightitsolutions.blogspot.com/2014/12/how-to-connect-to-sql-server-local.html

文章来源: How do I setup an ADODB connection to SQL Server 2008 in Microsoft Access 2010?