'Dim db As Database' wont compile

[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.

 

[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")

 

[Guest]

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

 

[Guest]

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

 

[Keith Wilby]

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.

Set a reference to the DAO library.

Keith.
www.keithwilby.com

 

[Guest]

You did not say which version of Access you are using. However, versions after
Access97 default ADO instead of DAO. Thus:

instead of "Dim db as Database"; try
"Dim db as DAO.Database"

Also use:
Dim rs as DAO.Recordset

That should do it.

---Phil Szlyk

 

[Douglas J Steele]

As the others have pointed out, it's necessary to have a reference set to
the DAO library before you can use DAO objects. Access 2000 and 2002 do not
have that reference set by default (although Access 2003 does), so it must
be added before you can use your suggestion.