Connection to SQL Express

  • Thread starter Thread starter Gabriele Bertolucci
  • Start date Start date
G

Gabriele Bertolucci

I'm using Access XP.

May you tell me why, if I use the following connection string:

"Provider=SQLNCLI;Server=mycomputer\sqlexpress;Database=databasename;UID=sa;PWD=sapassword;MarsConn=yes;Encrypt=yes"

I continue to receive a "Specified SQL Server not found" error,

while, if I use the following one:

"Provider=SQLOLEDB.1;Persist Security Info=False;Data Source=mycomputer\sqlexpress;User
ID=sa;Password=sapassword;Initial Catalog=databasename"

it seems all ok?

Obviously I tried also not to specify MARS and encryption parameters, but
got same results.
I tried also to specify ".\sqlexpress" as server name, but problem stays up.

I hope you can help me.
 
SQL-Server use SSL to perform its encryption; so the use of encryption
requires that a valid certificate from a known CA authority exist on the
server and that the root certificate for this CA authority is already
installed on the client machine.

This is the same requirement has for HTTPS://; so if you cannot make a
connection with HTTPS on the server, then you cannot use encryption with
SQL-Server.

(There is one exception to this: the old Multiprotocol Net-Library for
SQL-Server offers support for its own encryption algorithm but I don't know
if this old protocol is still supported with SQL-2005 Express.)

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.

If you want to use some of these features with ADP, you should open your own
ADO connection and access it with VBA code.
 
MS is a bunch of friggin drunk retards for not giving us a realworld
option for using ADP 2003 against SQL 2005.

I mean; the product manager than made that decision should be shot on
live television.

I mean.. Server-based Crosstab Query Wizard, anybody?

HELLO IS THERE ANYBODY IN REDMOND WITH A CLUE??

i mean-- what better way to force adoption of Office 2003??
 
[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.
Click to expand...

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.
Click to expand...

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?
 
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.
Click to expand...

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.
Click to expand...

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?
Click to expand...
 
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.
Click to expand...

It is correct.

With ADP you CANNOT use SQL Native Client for CurrentProject.ConnectionString.

With ADO you CAN declare a Connection Object and open it via SQL Native Client.

As Giorgio Rancati told me in microsoft.public.it.office.access (italian
newsgroup), Access XP and Access 2003 ADPs use the following provider:

Provider=Microsoft.Access.OLEDB.10.0

while Access 2000 ADPs di use the following provider:

Provider=MsDataShape.1

All Access version ADPs user SQLOLEDB as Data Provider.
That should be the reason for SQLNCLI not to work correctly with CurrentProject.Connection
in ADPs.
 
Microsoft just needs to fix it.

It was unacceptable between SQL 2000 and Access 2000.

and it is unacceptable now.
 
Back
Top