Help with recordset please

[Shadow]

Hi to all,

I'm new to this list and I'm not sure if it's the right place for asking
this question. Any kind of help is much appreciated.


In MS-Access Xp, I've created a new table and I'm trying to access to
the recordset of this table through VB. I'm using the following code for
this purpose.

Sub test()
Dim dbs As database
Dim rs As Recordset
Set dbs = CurrentDb()
Set rs = dbs.openrecordset("mytbl")
MsgBox rs.RecordCount
End Sub

I'm receiving a "Compile Error" on the second (Dim dbs As database) and
third line(Dim rst As Recordset) . If I change it to (Dim dbs) and (Dim
rst) the code runs just fine and gives me the No. of recordcount. Why
should I remove (as database) and (as recordset) to make the code run?

1- After typing (Dim dbs as) Database doesn't exists in the list.
2- After typing (msgbox rst.) the list of properties and methods of rst
doesn't appear.

Thanks for any possible help in advance.

Ghalamkari

 

[Douglas J. Steele]

Database is a DAO object. By default, Access 2002 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.5
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

 

[Shadow]

Hi Douglas and thanks for your response

Unfortunately, my knowledge on ADO and DAO is not enough. At present I'm
looking for some sources to study more.
In case of your suggestion, I have the following four items selected in my
tools/refrences menu.

Visual Basic for Applications
Microsoft Access 9.0 Object Library
OLE Automation
Microsoft DAO 3.6 Object Library

a screen shot of my references :
http://snow.prohosting.com/~mninshdw/images/ado.jpg
(my question was about Office Xp. I'm using Xp at home and Office 2000 pro
at work. in both cases I have the same problem)

When I create a new database using Access' wizard ( file - New - Database
tab and select one of the existing templates ) (dim dbs As DataBase)
doesn't show any error.
And after (rst.) a list of the possible properties and methods are
appearing.
But in a new database (with the same references selected) I receive this
error.


I would appreciate for any further sollutions.

Thanks for every second you spend to track where I'm mistaking.


Ghalamkari

PS: If necessary, you can contact me with (e-mail address removed)

 

[Douglas J. Steele]

Sorry, I have no idea. For that matter, I can't understand how creating a
new database wouldn't have an error with Database, since new databases don't
have a reference to DAO.

Your references do look okay (and, BTW, thanks for sending a link, rather
than including the image into the post)

If no one else offers any suggestions in the next day, try reposting your
question, with a "2nd post:" tag on it. (And maybe try
microsoft.public.access.modulesdaovba)