MDAC 2.7; Unknown error when opening connection

[digowa]

Hi,

I have a small VB 6.0 application using MDAC to communicate with a SQL-Server. The lines below
set up the connection. Now on some computers the
dbccc.open results in an "unknown error" with
"server does not exist" or "database xy does not exist".
Both is not the truth, because an ODBC-connection on the
same computer to the same server and database works fine.
My first idea was a wrong MDAC version. But now I replaced
"MDAC 2.7 Refresh" with "MDAC 2.7 SP1 Refresh" and the result is still the same. Additional on some other computers the same program does work absolut fine. Do I have to go through the whole registry to remove all entries
from MDAC or does anyone have a good idea?

Set dbCCC = New ADODB.Connection
dbCCC.CommandTimeout = 100
dbCCC.CursorLocation = adUseClient
dbCCC.ConnectionString = "Provider=SQLOLEDB;Server=" & dbServer & ";Database=" & sDatabase
If bTrusted Then
dbCCC.Open "Provider=SQLOLEDB;Trusted_Connection=yes;Server=" & dbServer & ";Database=" & sDatabase
Else
dbCCC.Open "Provider=SQLOLEDB;Server=" & dbServer & ";Database=" & sDatabase, sUsername, sPassword
End If

 

[PCLewis]

1) Which Open line gives the error? Both maybe?

In general, I always write my code with early bound objects suchs as you have doen, then, once the module is all debugged and working I change all references to late binding and add some debug infor for my log file to make sure I know what version of MDAC etc the user may be using.

The good thing about late binding is that you can remove the reference to ADODB in your references. This is good because it means you do not have to rely on a particular version. If you know your code will not work on too low a version you can still detect that and take action.

  Dim conn As Object
  Set conn = CreateObject("ADODB.connection")
  Debug.Print conn.Version

The connection object version value is the MDAC version e.g. 2.7

It's not guaranteed to solve the problem but I think it's worth a shot for you to try this out.

After you make this code change, remove the project's reference to "ActiveX Data Objects..."

Dim dbCCC as Object
Set dbCCC = CreateObject("ADODB.Connection")
dbCCC.CommandTimeout = 100
dbCCC.CursorLocation = 3
dbCCC.ConnectionString = "Provider=SQLOLEDB;Server=" & dbServer & ";Database=" & sDatabase
If bTrusted Then
 dbCCC.Open "Provider=SQLOLEDB;Trusted_Connection=yes;Server=" & dbServer & ";Database=" & sDatabase
Else
    dbCCC.Open "Provider=SQLOLEDB;Server=" & dbServer & ";Database=" & sDatabase, sUsername, sPassword
End If

 

[dilettante]

Have you considered sticking with early binding (many advantages, as we all know) but referencing the ADO 2.0 Type Library?

http://support.microsoft.com/kb/q259379/

This has drawbacks, such as letting go of newer items like the Record object. Of course trying to support older MDACs means you can't rely on the newer stuff anyway.

The whole reason for this TypeLib is to make it easy to support varying MDAC installations: it's a sort of "indirect" way to get to a working subset of the latest MDAC the user has installed.


Of course I don't think any of this addresses the original question at the head of this thrad.

 

[gmmastros]

I think you have a connection string problem. Please go to

http://www.ConnectionStrings.com

There are many examples to help you out.

This is what I notice with your code.

First, you are using ADODB.Connection object, so obviously, ADO is used. According to ConnectionStrings.com, you should be using 'Data Source' instead of 'Server', 'Initial Catalog' instead of 'Database', and 'Integrated Security=SSPI' instead of 'Trusted_Connection=yes'.

Your connection string looks appropriate for a .net SqlConnection, not an ADO connection string.

Another interesting observation regarding your code... You set the connection string, and then you pass it in through the open function. with ADO, the connection string parameter is option in the open function.

Here's how I open a connection.

Dim DB as ADODB.Connection

Set DB = CreateObject("ADODB.Connection")

DB.ConnectionString = "......"
DB.CursorLocation = adUseClient
Call DB.Open



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[digowa]

Thanks for all answers. I changed to late binding, also adding some debug information, but the result is really strange. The application has been installed on several computers (about 8 computers). All have MDAC 2.7 Refresh installed (only 1 MDAC 2.7 SP1 Refresh). On some computers all works fine, on others exactly the same error message, exactly when the connection.open command is executed. I thought about replacing the MDAC 2.7 Refresh, which contains known bugs, with MDAC 2.7 SP1 Refresh, but this does not change the result.

I think the only thing which is now different between the several computers, is the windows registry. Could this
registry contain some keys which effect the function of MDAC?

Dieter