*RESOLVED* Checking the state of a ado connection

[mdsoren]

I am getting an error when I try to set the active connection property of a command to an existing connection, err msg is "object already in collection". But if I pause the code before the line executes and chk the connection property it appears to be "= Nothing".

Am I missing something? When I am done with a recordset, I set the command's (which create's the rec set) activeconnection property = Nothing. I thot this released the connection, which would then require it to be reset the next time the rec set is updated.

The connection to the Access dbase is opened when the app starts and remains open until the app shuts down, I just set the active connection property of all my commands to "Nothing" when the recordsets are not actively in use.

What am I doing wrong? I ain't no database/VB expert by any means (< stating the obvious!)

[kleinma]

activeconnection points to a connection object... so if the activeconnection is your connection object and you set it to nothing then you are actually setting your connection object to nothing... make sense?

[mdsoren]

I am afraid it doesn't!

I create a rec set, populate my form and set the activeconnection property of the command = Nothing. Then if the user does edit the recordset, on the save subroutine I ...

cmdMain.activeconnection = cnn

This is the same line I use when I create the rec set. cnn is my connection string which is set when the app first loads and the connection string never changes, so I thot I just needed to set the active connection property from cnn (connected) to Nothing (disconnected) and back and forth as needed. Is this a wrong? (everything I know about VB I have taught myself, so I will just blame the teacher!)

[kleinma]

Originally posted by mdsoren
I am afraid it doesn't!

I create a rec set, populate my form and set the activeconnection property of the command = Nothing. Then if the user does edit the recordset, on the save subroutine I ...

cmdMain.activeconnection = cnn

This is the same line I use when I create the rec set. cnn is my connection string which is set when the app first loads and the connection string never changes, so I thot I just needed to set the active connection property from cnn (connected) to Nothing (disconnected) and back and forth as needed. Is this a wrong? (everything I know about VB I have taught myself, so I will just blame the teacher!)

setting an object to nothing eliminates it as a set object... cnn.close is the method that closes the database.. but if you open it when the app opens and you said you want it to remain open while the app is open then why would you be closing it..


see i assume you have declared your connection object as such
Dim cnn as ADODB.Connection

and lets say you have a command object
Dim cmd as ADODB.Command

so you open your connection and all that fun stuff.

now... cmd.ActiveConnection is a property.. not an object.. what that means is cmd.ActiveConnection is not a connection itself.. it is just pointing to the connection you have.. which is cnn.. so if you set cmd.activeconnection = nothing.. you just destroyed your connection to the database...

[techyspecy]

Originally posted by kleinma
setting an object to nothing eliminates it as a set object... cnn.close is the method that closes the database.. but if you open it when the app opens and you said you want it to remain open while the app is open then why would you be closing it..


see i assume you have declared your connection object as such
Dim cnn as ADODB.Connection

and lets say you have a command object
Dim cmd as ADODB.Command

so you open your connection and all that fun stuff.

now... cmd.ActiveConnection is a property.. not an object.. what that means is cmd.ActiveConnection is not a connection itself.. it is just pointing to the connection you have.. which is cnn.. so if you set cmd.activeconnection = nothing.. you just destroyed your connection to the database...

Hang On Hang On !

Are you saying if he did

set command.Activeconnection = nothing

that will also
destroy "Connection" Object ?????

[mdsoren]

OK, I understand what you're saying, which can only lead me to the following... what is the "object" which is "already in the collection"?

here's the flow of what happens:

Set cnn = New ADODB.Connection
strDS = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\crexdata.dat;"
cnn.Open strDS
.
.
.
(user clicks on form to initiate recset)
.
.
.
cmdMain.ActiveConnection = cnn
.
.
.
(create rec set, populate form, get rid of connection)
.
.
.
cmdMain.ActiveConnection = Nothing
.
.
.
(User edits rec set and calls save sub, need to re-connect)
.
.
.
cmdMain.ActiveConnection = cnn

That final line is the one which is generating the err msg, "Object already in collection". I resume next and the save completes without further error. My current solution is to On Error Resume Next, but I would like to understand why this is happening.

Does this help you?

TIA,
MDS

[kleinma]

i can't even get your code to run.. you should have to set the active connection objects..

as in

SET cmdMain.ActiveConnection = cnn

when i do just

cmdMain.ActiveConnection = cnn

i get an Object variable not set error...

[mdsoren]

You are correct sir!

Set cmdMain.ActiveConnection = cnn

I did NOT have exactly the same line in my populating sequence as I did when I re-est the connection the in save subroutine.

I still don't understand why this didn't get caught by either the compiler or completion "help" in the IDE. But in any event, that stops the error! I stare at these things for so long sometimes I miss the obvious.

THANX!
MDS

[kleinma]

Originally posted by mdsoren
You are correct sir!

Set cmdMain.ActiveConnection = cnn

I did NOT have exactly the same line in my populating sequence as I did when I re-est the connection the in save subroutine.

I still don't understand why this didn't get caught by either the compiler or completion "help" in the IDE. But in any event, that stops the error! I stare at these things for so long sometimes I miss the obvious.

THANX!
MDS

it doesn't give you a syntax error in VB because the compiler doesn't look at your variables to see what data type they are.. just like you can do

dim x as integer
x = "hello"

the compiler will accept it because it is only checking syntax.. this here would be a logic error.. same with the set thing..