Can't have 2 connection objects?

[ober0330]

I have this in my declarations:

Public CN As ADODB.Connection
Public RS As ADODB.Recordset

I've used this code before, but only with 1 connection. I open the connection like this:

Set CN = New ADODB.Connection
CN.Provider = "Provider=SQLOLEDB.1;Password=xxxx;Persist Security Info=True;User ID=labuser;Initial Catalog=Nonconf;Data Source=myserver"
CN.Open

My problem is, I have to open a connection to a different database while this connection is open. So I copied the declarations of the CN and RS objects and added a "2" after each. Now it won't even open the first connection claiming "Provider not found, It may not be installed". If I remove the declaration of the second connection object, it works fine. I cannot reuse the same connection on 2 different databases can I?

What am I doing wrong? Why won't it allow me to have 2 connection objects?

Using: VB6, SP5 w/ ADO and SQL Server 2K

[ober0330]

Update... now the connection object won't work at all

[Jlarini]

ober0330,

I never had this problem...

I misunderstood something, so I'll ask you to make it crystal clear and try to help you, if I can...

You have 2 connections, right ?

When it's only one connection there's no problem, but when you create the second one, it crashs....

My questions are:

1) Both connetions are pointing for the same DB ? (I think it doesn't but...)

2) No connection happens ? (nor 1st or 2nd) ?


edit:

Can you, please, post your code ?

JL

[ober0330]

Ok, it doesn't seem to be a problem with the 2 connections anymore. I've deleted the code for the second connection and I still get the error "Provider cannot be found. It may not be properly installed."

I don't understand what could have happened. It worked just a few minutes ago (before I started messing around with the 2 connections. And the 2 connections are to 2 different databases, not one.

The code involved is posted above. That's all there is to it. When it gets to the "CN.Open", it gives the above error.

[ober0330]

I've now opened up a new project, tried creating a connection, and I STILL get this error. Have I corrupted something????

[Jlarini]

As I see no reason for that problem...
I'll give you a silly (maybe) suggestion:

Did you restarted your computer after the problem ?!?!?

It's the Bill Gates' solution!!!

I think it's a bet... (maybe not the good one...)

JL

[ober0330]

I've restarted, installed the MDAC 2.8 components over again, downloaded the MDAC 2.8 SDK....

I think I've hosed my system somehow.

[techgnome]

Originally posted by ober0330
I have this in my declarations:

Public CN As ADODB.Connection
Public RS As ADODB.Recordset

I've used this code before, but only with 1 connection. I open the connection like this:

Set CN = New ADODB.Connection
CN.[b]Provider [/b]= "Provider=SQLOLEDB.1;Password=xxxx;Persist Security Info=True;User ID=labuser;Initial Catalog=Nonconf;Data Source=myserver"
CN.Open

My problem is, I have to open a connection to a different database while this connection is open. So I copied the declarations of the CN and RS objects and added a "2" after each. Now it won't even open the first connection claiming "Provider not found, It may not be installed". If I remove the declaration of the second connection object, it works fine. I cannot reuse the same connection on 2 different databases can I?

What am I doing wrong? Why won't it allow me to have 2 connection objects?

Using: VB6, SP5 w/ ADO and SQL Server 2K

You have got it all cocked up. Try setting that as the ConnectionString intead of the Provider.

TG