I had previously only tested this in an MDB, but on reading your post I
tested it in an ADP (Access 2003) and it works for me in the ADP same as in
the MDB. It first tests using the OLEDB provider for SQL Server, but read on
and you will see that it also tests using the native client.
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
--
Brendan Reynolds
Access MVP
Gabriele Bertolucci said:
[CUT]
OK for encryption feature. It's it does not work in my case. Right.
The question with the native provider SQLNCLI is more interesting: can
we use with ADP? From your test, it appears that maybe we cannot use
it with ADP. However, all the advanced features of SQLNCLI are
probably unavailable under ADP, so there is no real advantage to use
this provider under ADP.
OK for SQL Express new features. They are not available from ADP. Right.
But the matter is you cannot connect to SQL Express from an ADP with the
following connection string:
"Provider=SQLNCLI;Server=myserver\sqlexpress;Database=mydatabase;UID=sa;PWD=sapassword;"
you will get the following runtime error: -2147467259
"Method 'OpenConnection' of object '_CurrentProject' unsuccessful"
or
"Automation error"
If you want to use some of these features with ADP, you should open
your own ADO connection and access it with VBA code.
No. The runtime error described is given from VBA ADO OpenConnection.
I don't understand why it seems to be impossible to use SQL Native Client
from Access XP ADP, while Microsoft encourages to use it from a VB6
project.
If I'm not wrong, VB6 and VBA dlls have been unified some times ago.
So, what's the matter?
