ADO connection error - Run-time error ‘3709’:

[seanybravo]

I am using this code in a sub procedure to connect to and generate a recordset in my VB project.

Dim adoRSFields As New ADODB.Recordset
adoRSFields.CursorLocation = adUseClient

adoRSFields.Open "SELECT * FROM tblFields WHERE fldExtraFieldName IS NOT NUll", cnData

The connection string is stored in cnData however this is not filled until a database has been chosen in my project. What I cant understand is that the sub procedure is not and will not be called before cnData is set up. So why am I getting the error:

Run-time error ‘3709’:

The connection cannot be used to perform this operation. Its either closed or invalid in this context.

:when I try and run the project?

I have used a similar setup on another form and it works however it is a sub form that is not loaded until cnData is set up this may be why it works OK.

Any explanations and ways round the problem will be greatly appreciated.

TIA.

 

[jebenson]

The connection string is stored in cnData...

In the Recordset's Open method the second parameter is the ActiveConnection - that is, a Connection object that has been opened. The Connection object uses the ConnectionString, not the recordset.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[Dashley]

Tia,

You have to crank open the connection before the RS open call.
Hope this helps.


DIM adoRSFields as ADODB.Recordset
DIM MyConnection as Connection
DIM MYSql as string

Set adoRSFields As New ADODB.Recordset
Set MyConnection As New ADODB.Connection

MYSQL= "SELECT * FROM tblFields WHERE fldExtraFieldName IS NOT NUll"

MyConnection.OPen "your cnData string goes here "

With adoRSFields
If adoRSFields.state=1 then adoRSFields.close
.active connection = MyConnection
.cursorloaction=adduseclient
.source = MySql
.Open
End width

 

[seanybravo]

Thank you both for your replies. The problem is that the connection string does not get configured and opened untill the user make a decision on what database they want to work on. I get the error when I try to run the project any ideas for a work around?

Thanks for your time

Sean

 

[jebenson]

Try this before opening the recordset:

If cnData.State = adStateOpen Then
adoRSFields.Open "SELECT * FROM tblFields WHERE fldExtraFieldName IS NOT NUll", cnData
EndIf

That way the recordset won't be opened until the connection is open.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[Dashley]

Sean,

I think I get it now. The project wont even run -period -right.

If this is the case then yes you can work around it several ways.

You can "if" around it or "if" it to sub procedure.

If DBSelction is true then go run a sub
If DBSELECTION <>"" then go run a sub

You can also set a flag boolean style.
IF DBSELECION <> "" then
MYFLAG=TRUE
ELSE
MYFALG =FALSE
END IF

Hope this helps

Dan

 

[seanybravo]

Thanks for your help both that cracked it.

Sean

 

[Dashley]

Anytime Sean,

Hope you have a Happy Thanksgiving



Dan