Open recordset in Access VBA 1

[Dilettant]

I need to open and manipulate a recordset with VBA code running in Access but can’t seem to do it. To find out why, I did the following on the basis of Paul McFedries’ Absolute Beginner’s Guide to VBA:

1. Created a blank Access file
2. Created a form with a single command button
3. Saved by exiting and reopening the Access file (db1)
4. Imported a table from another Access program (tblReserv)
5. Referenced ADO 2.7 library
6. Added an Access Driver (.mdb) data source name selecting the filename, via the administrative tools/data source dialog box
7. Entered the following code into the form’s class module:

Private Sub Command0_Click()
Dim rs As Recordset
Dim strSELECT As String
Set rs = CreateObject("ADODB.Recordset")
strSELECT = "SELECT * FROM tblReserv WHERE EventID=30"
rs.Open strSELECT, "db1", adOpenKeyset
MsgBox rs("Guest1") & ", " & rs("Guest2")
rs.Close
Set rs = Nothing
End Sub

Upon executing I get the following error message: “Run time error 3706. Provider cannot be found. It may not be properly installed.” It occurs at the line rs.Open….

What am I doing wrong??? Most grateful for any help.

 

[SkipVought]

Hi,

Do you want to query the CurrentDB or some other database?

The probelm is woth the Open statement. The SECOND argument is the Connection Object. You have a string.

Skip,

_[red][/red]

 

[Dilettant]

Skip;

Thanks ever so much for your response. Yes I want to work with the CurrentDB. The string "db1" is the name I gave the data source in step 6 of what I did. I thought that defines the connection. If not, what should I do??

 

[SkipVought]

something like...

    rs.Open strSELECT, AccessConnection, adOpenKeyset

Check HELP

Skip,

_[red][/red]

 

[Dilettant]

Thanks again, but received Error 3001 message : Arguments wrong type, out of range or in conflict.

 

[Dilettant]

Skip:

I found the answer:

Dim cnn As Connection
Set cnn = CurrentProject.Connection

and use cnn as the second argument

 

[SkipVought]

Glad I helped you find the Connection Object.

Skip,

_[red][/red]