MS Access project with SQL Server - Connection to SQL Express

Author

Connection to SQL Express

Gabriele Bertolucci

2006-03-06, 7:30 pm

I'm using Access XP.

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

" Provider=SQLNCLI;Ser
ver=mycomputer\sqlex
press;Database=datab
asename;UID=sa;PWD=s
apassword;MarsConn=y
es;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\sq
lexpress;User
ID=sa;Password=sapas
sword;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.

--
PBsoft di Gabriele Bertolucci
www.pbsoft.it

Sylvain Lafontaine

2006-03-06, 7:30 pm

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.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

"Gabriele Bertolucci" <info@REMOVEpbsoft.it> wrote in message
news:875ea355af2e8c8
0d015d2e52c0@news.virgilio.it...
> I'm using Access XP.
>
> May you tell me why, if I use the following connection string:
>
> " Provider=SQLNCLI;Ser
ver=mycomputer\sqlex
press;Database=datab
asename;UID=sa;PWD=s
apassword;MarsConn=y
es;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\sq
lexpress;User ID=sa;Password=sapas
sword;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.
>
> --
> PBsoft di Gabriele Bertolucci
> www.pbsoft.it
>
>

aaron.kempf@gmail.com

2006-03-06, 7:30 pm

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

Gabriele Bertolucci

2006-03-06, 7:30 pm

[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;Ser
ver=myserver\sqlexpr
ess;Database=mydatab
ase;UID=sa;PWD=sapas
sword;"

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?

--
PBsoft di Gabriele Bertolucci
www.pbsoft.it

Brendan Reynolds

2006-03-06, 7:30 pm

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\SQLE
XPRESS"
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\SQLE
XPRESS"
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" <info@REMOVEpbsoft.it> wrote in message
news:875ea355b23b8c8
0f1bb572b2a2@news.virgilio.it...
> [CUT]
>
> OK for encryption feature. It's it does not work in my case. Right.
>
> 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;Ser
ver=myserver\sqlexpr
ess;Database=mydatab
ase;UID=sa;PWD=sapas
sword;"
>
> you will get the following runtime error: -2147467259
> "Method 'OpenConnection' of object '_CurrentProject' unsuccessful"
> or
> "Automation error"
>
>
> 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?
>
> --
> PBsoft di Gabriele Bertolucci
> www.pbsoft.it
>
>

Gabriele Bertolucci

2006-03-06, 7:30 pm

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

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.

--
PBsoft di Gabriele Bertolucci
www.pbsoft.it

aaron.kempf@gmail.com

2006-03-07, 3:24 am

Microsoft just needs to fix it.

It was unacceptable between SQL 2000 and Access 2000.

and it is unacceptable now.

Gabriele Bertolucci wrote:
>
> 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.
>
> --
> PBsoft di Gabriele Bertolucci
> www.pbsoft.it

Sponsored Links