Access 2002 recordset

[beaniebear]

Dim db As Database
Dim rec As Recordset

Set db = CurrentDb()
Set rec = db.openrecordset("Employee")
Do Until rec.EOF
'more code to go here
rec.MoveNext
Loop
rec.Close

Sorry this is probably really simple, but can anyone tell me where I am going wrong? I am getting a user defined type not defined in the line 'dim db as Database'. I've not done any development in Access since using Access 97. Is the code different when using access 2002? Any help with this or pointers to a good web site would be very helpful. Thanks.

 

[JerryKlmns]

In Tools-->Referebces check
Microsoft DAO 3.6 Object Library

And

Dim db As DAO.Database
Dim rec As DAO.Recordset

 

[beaniebear]

Thanks for quick reply. References is greyed out, any ideas?

 

[JerryKlmns]

What about using ADO instead of DAO? This reference is already selected within Access 2002

Dim rec As ADODB.Recordset

Set rec = New ADODB.Recordset
With rec
.ActiveConnection = CurrentProject.Connection
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Source = "Select * From Employee;"
.Open
While not .EOF
'more code to go here
.MoveNext
Wend
.Close
End With
Set rec = Nothing

 

[RoyVidar]

If the references are greyed out, one guess is that you might be running some code - perhaps a timer thingie on a (hidden) form? Make sure no code is running.

Some more explanations - in Access 2000 and 2002, the DAO object library is not checked by default, in 2003 it is (and in 97 it was). This means to use the native Access recordset library, DAO, which you are using, you need to set a reference to it.

Due to the introduction of ADO with the 2000 version, there are two libraries having lot of objects with the same name and similar behaviour, which makes it necessary (say many) to disambiguate the references. JerryKlmns have demonstrated this for both DAO and ADO -> prefixing with the libraries.

Some find it sufficient to just alter the order of references, and ensure the DAO reference is "higher" than the ADO - or just unceck the ADO library. I wouldn't recommend this, but be explicit (but that of course means having the possibility of entering the references in the first place ;-) )

Though I share JerryKlmn's view on ADO, and mainly use that, one need to take into consideration that form recordset of all versions are DAO unless working with an ADP or explicitly assigning recordset, and DAO is a Access/Jet native data access method/library, which means it's faster on almost all operations on Access databases (psst - you probably won't get a dynamic cursor on access tables;-))

You'll probably find more info when searching on different keywords relating to this, start with the errormessage you've got, then ADO vs DAO..., both here and through your favourite web search engine.

Roy-Vidar

 

[beaniebear]

Thanks for all your help. I did have the code running. I used the ADO example and now all works fine.