Help - no variable type Database

[Chuck]

I have a lot of code written as follows:
Dim db as Database
Dim rst as Recordset
rst = db.OpenRecordset(<source>,<type>)

All of a sudden I'm getting an error msg saying that 'Database' is not a
defined type. And sure enough, when I now write
Dim db as<space>, the list of varialble types does not include Database.
This is true in Access 2002 and Access2003. What have I missed??

Chuck

 

[Douglas J. Steele]

Database is a DAO object. By default, both Access 2000 and 2002 use ADO.

With any code module open, select Tools | References from the menu bar,
scroll through the list of available references until you find the one for
Microsoft DAO 3.6 Object Library, and select it. If you're not going to be
using ADO, uncheck the reference to Microsoft ActiveX Data Objects 2.x
Library

If you have both references, you'll find that you'll need to "disambiguate"
certain declarations, because objects with the same names exist in the 2
models. For example, to ensure that you get a DAO recordset, you'll need to
use Dim rsCurr as DAO.Recordset (to guarantee an ADO recordset, you'd use
Dim rsCurr As ADODB.Recordset)

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset

 

[Dirk Goldgar]

I have a lot of code written as follows:
Dim db as Database
Dim rst as Recordset
rst = db.OpenRecordset(<source>,<type>)

All of a sudden I'm getting an error msg saying that 'Database' is
not a defined type. And sure enough, when I now write
Dim db as<space>, the list of varialble types does not include
Database. This is true in Access 2002 and Access2003. What have I
missed??

You need to open the References dialog from the VB Editor (Tools ->
References...) and add a reference to the Microsoft DAO 3.6 Object
Library. Then you need to do one (or two) of three things:

a) Remove the existing reference to the Microsoft ActiveX Data Objects
2.x Library (if you don't plan to use it), or

b) Move the DAO reference higher in the list that the ADO reference, or

c) Qualify your declarations of all objects from these libraries with
either DAO or ADODB, as appropriate; like this:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim cn As ADODB.Connection
Dim rs2 As ADODB.Recordset

That's because these libraries have some objects in common, so if you
keep both references you must ensure that Access (and you!) know which
library you're pulling from.

 

[Chuck]

You need to open the References dialog from the VB Editor (Tools ->
References...) and add a reference to the Microsoft DAO 3.6 Object
Library. Then you need to do one (or two) of three things:

a) Remove the existing reference to the Microsoft ActiveX Data Objects
2.x Library (if you don't plan to use it), or

b) Move the DAO reference higher in the list that the ADO reference, or

c) Qualify your declarations of all objects from these libraries with
either DAO or ADODB, as appropriate; like this:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim cn As ADODB.Connection
Dim rs2 As ADODB.Recordset

That's because these libraries have some objects in common, so if you
keep both references you must ensure that Access (and you!) know which
library you're pulling from.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Dirk & Douglas,

Many thanks. That, of course, did it. Can you recommend a good overview
of the different Object models for Access. I have Feddema's "DAO Object
Model" but it is difficult reading without a good overview. Reminds me of
the old saying:"You can't see the forest for the trees."

Chuck

 

[Chuck]

What I really found strange was this suddenly occurring. I've been using
the same code on the same machines for years ... and I upgraded to WinXP
months ago ... without having this problem. Could a MS Access update cause
this to occur? Can't remember downloading any in the immediate past,
though.

Bewildered,
Chuck

 

[Dirk Goldgar]

What I really found strange was this suddenly occurring. I've been
using the same code on the same machines for years ... and I upgraded
to WinXP months ago ... without having this problem. Could a MS
Access update cause this to occur? Can't remember downloading any in
the immediate past, though.

Every time you create a new database in Access 2000 or 2002, you have to
set the DAO reference if you want to declare DAO objects. Are you
saying that the reference was suddenly lost from an existing database
that previously had it? I don't see how that could be, short of user
action. On the other hand, the reference could conceivably be broken by
an OS upgrade. I wouldn't expect it to just disappear in that case,
though; I'd expect it to show up as MISSING in the References dialog.

 

[Douglas J. Steele]

Dirk & Douglas,

Many thanks. That, of course, did it. Can you recommend a good overview
of the different Object models for Access. I have Feddema's "DAO Object
Model" but it is difficult reading without a good overview. Reminds me of
the old saying:"You can't see the forest for the trees."

Actually, ADO isn't really an Access object: it has its own life. The fact
that most (if not all) COM objects are essentially stand-alone makes it
really difficult to suggest anything.