ADO .Open doesn't work when moved between machines

[RRinTetons]

I have a VBA function that accesses a SQL Server db via ADO to wrap a T-SQL udf that retreives a value and return it to Excel 2007. It opens a connection with the following lines:

    Set cnn = New ADODB.Connection
    strConn = "Provider=SQLNCLI10.1;"
    strConn = strConn + "Data Source=myServer;"
    strConn = strConn + "Initial Catalog=myDatabase;"
    strConn = strConn + "Integrated Security=SSPI;"
    strConn = strConn + "Auto Translate=False"
                
    cnn.Open strConn

It works fine on two machines(one XP Pro SP 2, one Vista) and fails on the line

'cnn.Open strConn'

as if the library simply weren't there on two others (both XP Pro SP 2. The function returns #VALUE.

All machines have Microsoft ADO 2.8 (msado15.dll) installed and loaded, no other ADO libraries. I have unregistered and registered that library without making any difference.

All machines can connect to the database through SSMS using the same credentials. Althugh I can't see what difference it makes since the connection never opens, bu the T-SQL the VBA wraps works when executed in SSMS.

Any other suggestions?

-
Richard Ray
Jackson Hole Mountain Resort

 

[SkipVought]

Do you have an SQL SERVER Driver configured on the failing machine?

Check Start > Settings > Control Panel > Administrative Tools > Data Sources (ODBC) for your driver.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RRinTetons]

ding, ding, ding - update

When I put a watch on cnn (the connection I want to open) it reports the following:

: Count : <Provider cannot be found. It may not be properly installed.> : Long : DataAccess.GetCurrPayrollLaborData

for both

Provider=SQLNCLI10.1

and

Provider=sqloledb

Should it be able to retunrn that count when the connection isn't open yet? In which case do I have a missing provider? Or, is it simply not able to do that yet and no error is indicated by that information?

-
Richard Ray
Jackson Hole Mountain Resort

 

[PHV]

strConn = "Provider=SQLNCLI10.1;"
All the machines have the same provider ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Did you set a reference in the VB Editor Tools > References... to that library?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RRinTetons]

Skip -

I was just wondering about how oheck that. Following your directions I see 'SQL Server Native Client 10.0' installed. I changed the Provider in the connection string to

"Provider=SQLNCLI10.0;"

and had a brief moment of hope, but, alas, no joy. Same error.

Where can I see the strings that should be used as the provider value?

-
Richard Ray
Jackson Hole Mountain Resort

 

[SkipVought]

Check out

http://www.carlprothman.net/Default.aspx?tabid=81

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

Have a look here:

http://www.connectionstrings.com/

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[RRinTetons]

Did I read you to say that I should be seeing the providers referenced in Tools -> References? In which case, no, I don't. If you were referring to the ADO 2.8 library, then yes, it's there and the path is to the one that I unregistered and reregistered via regsrvr32.

-
Richard Ray
Jackson Hole Mountain Resort

 

[SkipVought]

The ADO LIBRARY sould be referenced in Tools > References.

The PROVIDER should be configured in the DRIVER.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RRinTetons]

That's what I thought. OK, that's the way it is. The referenc list includes Microsoft ActiveX Data Objects 2.8 Library at a location in Program Files\Common Files\System\ado\msado15.dll. That's the one that I've unregistered and registered.

The provider seems to be the culprit. In the Data Sources I see drivers named:

'SQL Native Client' (SQLNCLI.DLL),
'SQL Native Client 10.0' (SQLNCLI10.DLL) and
'SQL Server' (SQLSRV32.DLL)

I've tried both the name and the filename (withou .dll) as the provider in the connection string, without luck.

-
Richard Ray
Jackson Hole Mountain Resort

 

[RRinTetons]

Whatever the original problem came from, it's solved by simply reinstalling the SQL Client Tools from a SQL Server install disk on the machines having the problems. I'm guessing that cleans up a lot of stuff in the background.

On to the next task...

-
Richard Ray
Jackson Hole Mountain Resort