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

Solution 1

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

Solution 2

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