95 Code Won't Work In 2000

[Keith Willcocks]

I used to use Access 95 VBA but now have to do something in Access 2000.

The following in Declarations:
Dim dbs As Database
Dim rst As Recordset

Followed in a procedure by:
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblMain")

Would open the table for use. However, in Access 2000, this simply results
in error messages. As far as I can see the Database option in "Dim dbs As
Database" no longer works and is giving me Type Mismatch messages. How do
I do this operation in Access 2000 please? Oh, can you keep it simple
please, I haven't touched Access for 6 or 7 years.

 

[Allen Browne]

You need to add the DAO library reference:
http://allenbrowne.com/ser-38.html

 

[Keith Willcocks]

Many thanks for that Allen. I have now installed the DAO Library and
"Database" is now recognised in the Declarations. Curiously though I am
still getting a Type Mismatch message. This is my actual code if you can
see anything daft that I cannot see for the trees:

Declaration:
Dim dbs As Database
Dim rst As Recordset
- - - - - - - - - - - - - - - - - - - - - - - - - - - -
Private Sub cmdIssuesSubmit_Click()
On Error GoTo Err_cmdIssuesSubmit_Click
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblMain")
rst.MoveFirst

Exit_Err_cmdIssuesSubmit_Click:
Exit Sub

Err_cmdIssuesSubmit_Click:
MsgBox Err.Description
Resume Exit_Err_cmdIssuesSubmit_Click

End Sub

Stepping through in debug with F8, the error message Type Mismatch is thrown
up as soon as the line Set rst = dbs.OpenRecordset("tblMain") is executed.
The table has four short date/time fields and all the others are Text.
There is one record in the table with data in the Primary Key (number 1) and
Text in the first two Text fields. I have rigorously checked all the
spellings in the code.

I am totally puzzled by this and can see no reason for it.

Keith Willcocks

 

[Allen Browne]

Change the line:
Dim rst As DAO.Recordset
to:
Dim rst As Recordset

The ADO library has a Recordset object as well. If you are getting a type
mismatch, Access might be giving you the wrong one, so the above
disambiguates.

 

[Arvin Meyer [MVP]]

Allen mean the opposite:

Change the line:

Dim rst As Recordset

to:

Dim rst As DAO.Recordset

 

[Allen Browne]

Thanks, Arvin.

(It's 1:15am here. They should not let me near the keyboard!)

 

[Keith Willcocks]

Oh dear. I was fine with the old 95 version, it's this new fangled stuff
that's messing me up. I made the change to Dim rst As DAO Recordset and
now I am getting the old "Compile error: Expected: end of statement"
message. Sorry to be such a pain, your help and forbearance is greatly
appreciated.

Could there be any other References that I need to include (or exclude)?
The ones I have are:

Visual Basic For Applications
Microsoft Access 10 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library
Data Object Wizard
Microsoft DAO 3.6 Object Library

Keith Willcocks

 

[Allen Browne]

What line of code generates the compile error that an endof statement is
expected?

With the DAO 3.6 library referenced, the line:
Dim rst As DAO.Recordset
is valid (assuming you don't have something else named rst.)

 

[Keith Willcocks]

I feel such a fool now. It was only in your latest email that my less than
20-20 vision realised there was a full stop (period?) between DAO and
Recordset. Now it works a treat. My most sincere thanks.