How do I setup an ADODB connection to SQL Server 2008 in Microsoft Access 2010?

Question

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:

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.

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

You are using the wrong connection string, it is for connecting to an access file. You are looking for a connection string to connect to SQL Server 2008. — Horaciux
– Horaciux, Commented Jun 21, 2014 at 22:08
just a stupid question: why not using DAO, which is the native method in Access ? — Patrick Honorez
– Patrick Honorez, Commented Jun 21, 2014 at 22:18

Horaciux · Accepted Answer · 2014-06-21 22:11:23Z

3

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

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 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;

Source

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

edited Jun 21, 2014 at 22:11

answered Jun 21, 2014 at 21:57

Horaciux

6,5152 gold badges26 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Horaciux Over a year ago

@sungod916 make sure HomeSQL is the name of the database, not the SQL Server Instance.

Gareth Over a year ago

Might want to add to this answer that Microsoft ActiveX 2.x needs to be included as a reference within the project.

Marcin Własny · Accepted Answer · 2014-12-01 12:51:08Z

1

This connetion string works under Excel VBA. In MsAccess also should.

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

edited Dec 1, 2014 at 12:51

answered Dec 1, 2014 at 11:12

Marcin Własny

516 bronze badges

1 Comment

Seb D. Over a year ago

Please don't just link to a possible solution, but include the relevant parts.

Collectives™ on Stack Overflow

How do I setup an ADODB connection to SQL Server 2008 in Microsoft Access 2010?

2 Answers 2

SQL Server 2008

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

SQL Server 2008

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related