如何在Microsoft Access 2010中设置与SQL Server 2008的ADODB连接？ [英] How do I setup an ADODB connection to SQL Server 2008 in Microsoft Access 2010?

问题描述

我刚刚在笔记本电脑上安装了SQL Server 2008。我还安装了Microsoft Access 2010。使用VBA，我试图在SQL Server上创建一个到我自己数据库的ADODB连接，但是我找不到正确的代码行：

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:

在下面，它不起作用。
我的计算机的名称是LAPTOPX，数据库的名称是HomeSQL。

When I use this below, it doesn't work. The name of my computer is LAPTOPX and the database is 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.

谢谢！

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

推荐答案

首先，您需要确保已安装SQL Native Client。 参考

First, you need to make sure SQL Native Client is instaled. Reference

标准安全性

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

受信任的连接

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

连接到SQL Server实例
指定的语法对于SQL Server的所有连接字符串，服务器键值中的服务器实例都是相同的。

Connecting to an SQL Server instance The syntax of specifying the server instance in the value of the server key is the same for all connection strings for 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

这篇关于如何在Microsoft Access 2010中设置与SQL Server 2008的ADODB连接？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！