Database and OpenRecordSet mismatch in XP

[stevievee]

Hi all,

I am using VBA in Office XP for the first time.

Firstly, I could not Dim a variable as Database, so I added the DAO 3.6 as a reference. This may be my mistake, I'm not sure.

I then tried to set a RecordSet variable with db.OpenRecordset, and this gave me a mismatch error. What am I doing wrong? This was never a problem when using Access 97.

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from Statements")

I guess the reference to DAO3.6 may be causing the problem, but I don't know what the correct reference should be. I already have VBA and Access10 references selected, but these do not make the Database Object available.

Thanks in advance.

 

[Mile-O]

When using DAO in a later version:

Dim db as DAO.Database

 

[stevievee]

I don't actually want to use DAO, I'm only accessing the current database (I've edited my original post to show the code). I don't think the DAO reference is the one I want, I was comparing old Access 97 code to try and work out what was different, and this was all I could come up with.

I need the Database object reference that allows this code to run:


Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from Statements")

Cheers.

 

[stevievee]

I have worked it out, after reading Mile-O-Phile's post again. You use DAO.Database, as stated, but then you also need to use DAO.Recordset as well. A plain RecordSet object is obviously not compatible anymore.

Dim db as DAO.Database
Dim rs as DAO.RecordSet

set db = CurrentDB
set rs = db.OpenRecordSet("...

 

[AndyP]

I had the same problem with XP and it was to do with the references. The ones i have are:
Visual Basic For Applications
Microsoft Access 10.0 Object Library
Microsoft DAO 3.6 Object Library

The declaration statements are
dim db as Database
dim rs as Recordset

Set db = DBEngine.Workspaces(0).Databases(0)
Set rs = db.OpenRecordset("name of table/query in parenthesises")

This has worked for me without the need to declare as DAO.Database.

Cheers,
Andy