Connection object problem..please advise

Ok, guys...I have a question that is baffling me. I am relatively new to DB programming in VB, and I can't open a recordset. I am hoping that you can tell me why. Here is a brief sample of the code I am using...see any problems?

Dim cnShop As Connection
Dim rsShop As Recordset

Private Sub Form_Load()
Set cnShop = New Connection
cnShop.CursorLocation = adUseClient
cnShop.ConnectionString "Provider=Microsoft.Jet.OLEBD.3.51" & _ "Data Source = My Documents\Shop.mdb"

Set rsShop = New Recordset
rsShop.Open "SELECT * FROM Customers ORDER BY LastName", cnShop, adOpenStatic, adLockOptimistic
End Sub


Now I keep getting a message that says, "The application requested an operation or an object with a reference to a closed or invalid connection object" (The lines of code, of course, are broken up with the "& _" in the proper place, not as they have appeared in this text box, and the data base is located at the Data Source I have specified)

I cannot figure this out...it seems that I have set the connection and the recordset correctly. I have the Microsoft ActiveX Data Objects and Recordsets 2.1 Libraries referenced in the project. Is there some error in this snippet of code? I have tried running the project using this code (although I added an ErrorHandler and a sub procedure that populated a combo box from the recordset, but I keep coming up with the same error? Any advice?

Thanks....

FLL

Dim cnShop As New ADODB.Connection
Dim rsShop As New ADODB.Recordset

Private Sub Form_Load()

cnShop.Open "DSNName"
rsShop.Open "SELECT * FROM Customers ORDER BY LastName", cnShop, adOpenStatic, adLockOptimistic

End Sub

you have to add a DSN referencing to your data base

Fll - I don't think it's the missing DSN because you're not using ODBC. What's missing is a semi colon ; between the Provider and the Datasource. try replacing the connectionstring with -

"Provider=Microsoft.Jet.OLEBD.3.51;" & _ "Data Source = My Documents\Shop.mdb"

Or simply, you need to read the error message and do what is suggests. The message states that you are attempting an action on a closed connection. The reason being it has never been opened.

Dim cnShop As Connection
Dim rsShop As Recordset

Private Sub Form_Load()
Set cnShop = New Connection
cnShop.CursorLocation = adUseClient
cnShop.ConnectionString "Provider=Microsoft.Jet.OLEBD.3.51" & _ "Data Source = My Documents\Shop.mdb"

'Here we open the connection
cnShop.Open

Set rsShop = New Recordset
rsShop.Open "SELECT * FROM Customers ORDER BY LastName", cnShop, adOpenStatic, adLockOptimistic

'Then just to be good you should close and destroy the objects.

rsShop.Close
cnShop.Close
Set rsShop = Nothing
Set cnShop = Nothing
End Sub

Thanks for the replies...in my haste, I did leave out a ";" in my question, but it is there in my code. Also, Tim, I left out my open command. I have written a few extra lines of what I actually did...so maybe this will help in the diagnosis:

Dim cnShop As Connection
Dim rsShop As Recordset
Dim strConnect As String

Private Sub Form_Load()
Set cnShop = New Connection
cnShop.CursorLocation = adUseClient
strConnect = "Provider=Microsoft.Jet.OLEBD.3.51;" & _ "Data Source = My Documents\Shop.mdb"

cnShop.Open strConnect

Set rsShop = New Recordset
rsShop.Open "SELECT * FROM Customers ORDER BY LastName", cnShop, adOpenStatic, adLockOptimistic
End Sub

This is a more accurate representation of what I have done...any suggestions?

Thanks...

Are you still getting the same problem?

If so, one other thing to check is that you do not have references to DAO as well as ADO in this project. ADO and DAO both have recordset objects and connection objects. As you are not explicitly declaring the Connection and Recordset as ADODB.Connection / ADODB.Recordset, there may be some confusion within Vb as to which type of object you are attempting to reference. I always ensure that I am explicitly referencing the objects in this way, ADODB.Recordset versus DAO.Recordset. Though generally I only use ADO in my projects and never reference the DAO libraries.

Hello, Tim. I apologize for the delay in the response, as I have had a busy end of the week (and weekend) at work, and do appreciate the advice you rendered. I ended up trashing the original code and recoding. From the comparison of the print-out of the original and the current code I have running the project, there is little difference. I cannot determine exactly what the problem was. I can say with certainty that the program was not letting me use the same connection object with more than one procedure (which is what I think was the problem). Why that was, I do not know. This one works fine (and I do use the same global connection for multiple connections to the DB). Being relatively new to ADO programming with VB, I am assuming it was an error on my part somewhere in the code, though I can't identify exactly where that was. Again, though, your help is appreciated. I am posting another question in hopes that someone may answer it...concerning Access.

Thanks...

FLL