ADODB connection to SQLExpress2005

[Reidar]

Is it possible to connect to a SQLExpress2005 from Access2000
database-format?
reidarT

 

[Brendan Reynolds]

I have only tested this in Access 2003, but as it doesn't depend on any new
features, I would expect it to work in Access 2000 as well.

Public Sub TestSqlExpress()

Dim strConnect1 As String
Dim strConnect2 As String

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Debug.Print "Using OLEDB Provider for SQL Server"
strConnect1 = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=pubs;" & _
"Data Source=DBWGQZ0J\SQLEXPRESS"
Set cnn = New ADODB.Connection
With cnn
.ConnectionString = strConnect1
.Open
End With
Set rst = New ADODB.Recordset
With rst
Set .ActiveConnection = cnn
.Source = "SELECT * FROM Authors"
.Open
Do Until .EOF
Debug.Print .Fields(0)
.MoveNext
Loop
.Close
End With
cnn.Close

Debug.Print
Debug.Print "Using SQL Native Client"
strConnect2 = "Provider=SQLNCLI.1;" & _
"Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=pubs;" & _
"Data Source=DBWGQZ0J\SQLEXPRESS"
Set cnn = New ADODB.Connection
With cnn
.ConnectionString = strConnect2
.Open
End With
Set rst = New ADODB.Recordset
With rst
Set .ActiveConnection = cnn
.Source = "SELECT * FROM Authors"
.Open
Do Until .EOF
Debug.Print .Fields(0)
.MoveNext
Loop
.Close
End With
cnn.Close

End Sub