Problem in DAO code

[George Papadopoulos]

I am trying out an simple subroutine. My database has a table named
'Books'. I have written the code below.

Sub exaRecordsets()

Dim db As Database
Dim rsTable As Recordset
Dim rsDyna As Recordset
Dim rsSnap As Recordset

Set db = CurrentDb

' Open table-type recordset
Set rsTable = db.OpenRecordset("BOOKS")
Debug.Print "TableCount: " & rsTable.RecordCount

' Open dynaset-table recordset
Set rsDyna = db.OpenRecordset("Books", dbOpenDynaset)
Debug.Print "DynaCount: " & rsDyna.RecordCount
rsDyna.MoveLast
Debug.Print "DynaCount: " & rsDyna.RecordCount

' Open snapshot-type recordset
Set rsSnap = db.OpenRecordset("Books", dbOpenSnapshot)
Debug.Print "SnapCount: " & rsSnap.RecordCount
rsSnap.MoveLast
Debug.Print "SnapCount: " & rsSnap.RecordCount

' Close all
rsTable.Close
rsDyna.Close
rsSnap.Close

End Sub

which is included in a module named 'Module1'. I was trying to test the code
by calling the subroutine. Unfortunately I get the error 'Type mismatch'.
The problematic line has been traced to be

Set rsTable = db.OpenRecordset("BOOKS"). I believe that my database has not
been opened correctly by the statement 'db = CurrentdB'.

Any suggestions?

George Papadopoulos

 

[PC Datasheet]

Open to your code. Click on Tools - References. Uncheck Microsoft ADO. Scroll
down and find Microsoft DAO and check the version for the version of Access you
are using: 3.51 for Access2000, 3.6 for AccessXP. Close and your code will work
fine.

 

[Naresh Nichani MVP]

Hi:

Try changing these lines --
Dim db As DAO.Database
Dim rsTable As DAO.Recordset
Dim rsDyna As DAO.Recordset
Dim rsSnap As DAO.Recordset

Put the DAO before them to see if it works.

Regards,

Naresh Nichani
Microsoft Access MVP

 

[yakule]

you need to provide an sql statement in you OpenRecordset line lik
this:

[vb]
set rsTable=db.OpenRecordset("SELECT * FROM Books")
[/vb]

if you just want to give the table name you should use .TableDef
instead like this:

[vb]
set rsTable=db.TableDefs("Books")
[/vb

 

[George Papadopoulos]

thx, everyone. I hadn`t unchecked the ADO libraries. I was also using DAO
3.6 on Access 2000. After switching to DAO 3.51 everything worked.

Thx, again

George Papadopoulos