'Dim db As Database' wont compile

C

CJB

I've been using Access for many years. I usually create a module with
typical code:

Function MyFunction(user_name As String, groups As String) As String

Dim db As Database
Dim rs as Recordset

Set db = CurrentDb
Set rs = db.OpenQuery( etc. )

' Do calcs with field values, etc.

rs.Close

End Function

However I cannot get this to compile. It refuses to recognise:

Dim db As Database

In fact when I type in 'Dim db As' the keyword 'Database' is not even
offered in the menu.

Please what am I doing wrong. I think I have to set up Tools /
References - but which one out of hundreds? I have never had this
problem in the past but then I used Access 8. Now I have Access 9 and
seem to be having far more configuration issues.

Many thanks - CJB.
 
J

John Spencer

A2K and later versions default to ADO which does not have a Database
Object. Database is an Object of DAO which A97 defaults to. To use your
code, you should:

Quoting Doug Steele
Database is a DAO object. By default, Access 2000 uses 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.1
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

(Hopefully this explains to you why you can't just use "DIM Db as Database")
 
F

freakazeud

Hi,
check your references...open vba editor
(ctrl+g)...tools...references...browse to the Microsoft DAO object library.
It is not set by default anymore in ACC2000 and higher.
HTH
Good luck
 
O

Ofer

First check if you have reference to DAO 3.6, if you do, change the code to

Dim db As Dao.Database
Dim rs as Dao.Recordset
 
K

Keith Wilby

CJB said:
I've been using Access for many years. I usually create a module with
typical code:

Function MyFunction(user_name As String, groups As String) As String

Dim db As Database
Dim rs as Recordset

Set db = CurrentDb
Set rs = db.OpenQuery( etc. )

' Do calcs with field values, etc.

rs.Close

End Function

However I cannot get this to compile. It refuses to recognise:

Dim db As Database

In fact when I type in 'Dim db As' the keyword 'Database' is not even
offered in the menu.

Please what am I doing wrong. I think I have to set up Tools /
References - but which one out of hundreds? I have never had this
problem in the past but then I used Access 8. Now I have Access 9 and
seem to be having far more configuration issues.

Many thanks - CJB.
Click to expand...
Set a reference to the DAO library.

Keith.
www.keithwilby.com
 
Top