DAO in Access 2000

[Tony Nichols]

I have written exact code in Access 2000 that I have in Access 97 using the
DAO and receive an error 13 'Type mismatch' when the recordset object is
being initiated.

I tried converting a test db from Access97 to Access 2000 and the code
worked fine.

Can anyone tell me what I have to do to use the DAO in Access 2000 and not
have problems. It seems the conversion wizard makes some changes to allow
the code to work but I cannot determine what the changes are.


I have posted the code below:
Public Sub DeleteProd()
Dim db As Database
Dim rec As Recordset
Dim sMsg As String
On Error GoTo ErrHandler
Set db = CurrentDb()
Set rec = db.OpenRecordset("SalesTotalOld", dbOpenDynaset)

With rec
.MoveLast
.MoveFirst

Do Until .EOF
.Delete
.MoveNext
Loop
End With

rec.Close
db.Close
Set rec = Nothing
Set db = Nothing
Exit Sub
ErrHandler:
Select Case Err.Number

Case 3218, 3021
Resume

Case Else
sMsg = "Error " & Err.Number & " " & Err.Description
MsgBox sMsg, vbOKOnly, "Error"
Exit Sub

End Select



End Sub


Thanks,
Tony Nichols

 

[Ken Snell [MVP]]

Open Visual Basic Editor. Click Tools | References and select the DAO
library.

ACCESS 2000 and 2002 do not include the DAO library by default; you must
manually select it. MS corrected this "oversight" in ACCESS 2003.

 

[Nick via AccessMonster.com]

Also, once you set a reference to the DAO library, it helps to disambiguate
your references.

Instead of using:

Dim db as Database

Use this:

Dim db as DAO.Database

That way, Access won't browse the ADO library and (possibly) create errors
later on; it will go straight to the DAO library. I usually use the DAO
declare for databases, tabledefs, and recordsets.

Nick

 

[Tony Nichols]

Ken,

I did not detail in my original post that I had already selected the DAO
reference and still have the problem.

I went the the original database (non-converted via wizard) and set the
references to the same files as in the converted db but the problem still
persist.

It is as if I cannot write a new piece of code to fix the problem.

Thanks for any further input or experience of a like problem.

Tony Nichols

 

[Ken Snell [MVP]]

As Nick notes, disambiguate the Recordset dim statement:

Dim rec As DAO.Recordset

ADO library also has a Recordset object, and it's likely that ADO library is
higher priority in your reference list than DAO, so ADO is searched first.

 

[Tony Nichols]

Thanks Nick,

Your comments about declaring the variables in an unambigous manner worked.

Thanks again.
Tony