Basic issue: Can't use OpenRecordset properly

[srhansen47]

I'm having difficulty with two pieces of code I see used in this forum all the time. I'm using Access from Office XP. Does that make a difference?

1. dim db as Database
Access gives me an error that it doesn't recognize the user-defined "Database"

2. Set rst1 = CurrentDb.OpenRecordset("Select * from tbl1;&quot
Access says there's a type mismatch (I used: Dim rst1 as Recordset)

Any help appreciated.

 

[cmmrfrds]

You probably need to tell access what TYPE it is.

DAO.recordset
ADODB.recordset

DAO.database

 

[TerpFan2001]

Make sure you have the reference for "Microsoft DAO 3.6 Object Library" selected in your references.

When you open your recordset try adding the type of recordset (i.e. dbOpenSnapshot or dbOpenDynamic)

set rs = CurrentDB.OpenRecordset("SELECT * tbl1", dbOpenSnapshot)

Use dbOpenDynaset if you plan to edit the recordset otherwise you can use dbOpenSnapshot.

 

[srhansen47]

Thanks, both cmmrfrds and TerpFan2001. I've made progress. As suggested, I added the DAO reference and also declared DAO Types.

Now I have a different problem. When I run the code below, I get Run-time error 3001 - Invalid Argument for the "Set rst = db.OpenRecordset("tbl1", dbOpenDynamic)" statement. Any further suggestions?

Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("Select * from tbl1", dbOpenDynamic)
rst.AddNew
rst!fld1 = "test"
rst.Update
rst.Close

Much thanks.