Access 2000 db.OpenRecordset Problem

[DonBerry]

I am experimenting with the reocrd set concept in VBA and have been trying to run the following code:

Private Sub Command0_Click()

Dim db As Database
Dim rs As Recordset
Dim intCount As Integer

Set db = CurrentDb()
Set rs = db.OpenRecordset("tblInvoices&quot

intCount = rs.RecordCount
MsgBox "There are " & intCount & " records in the table"
rs.Close

End Sub

It fails in a runtime error 13, type mismatch every time and the debuger highlights the [Set set rs = db.Open etc.] line.

I have no clue what's wrong. Any ideas?

 

[Aivars]

Hi!

I encountered this problem,too. Access 2000 don't want to open recordsets of tables. Use SQL clauses:

Dim db As Database
Dim rs As Recordset
Dim intCount As Integer
dim strSQL as String

strSQL = "Select tblInvoices.* From tblInvoices;"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL )

intCount = rs.RecordCount
MsgBox "There are " & intCount & " records in the table"
rs.Close

Aivars

 

[DonBerry]

Aivars,

Thanks for the quick reply. I tried your suggestion but still get the "type mismatch" error on the same line. Can I be missing a refference add-in I need? Any other ideas?

Thanks Don

 

[TheInsider]

You are trying to use a DAO recordset, Access 2000 defaults to ADO. Go to Tools/References in the code window and check Microsoft DAO 3.51 (or 3.6) Object Library. Then declare your recordset as follows:
Dim rs as DAO.Recordset Rob Marriott
rob@career-connections.net

 

[DonBerry]

Rob,

Thank you for your response. You were correct and my problem went away. Should one be using ADO in Access 2000? What's the best approach?

Thanks,

Don

 

[tlbroadbent]

You might consider using DCount() domain function to count records. You could replace all of the code in your module with one line.

MsgBox "There are " & DCount("*", "tblInvoices&quot & " records in the table"
Terry

;-) I never worry about the future. It comes soon enough. -Albert Einstein

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[DonBerry]

Terry,

Thanks for the reply. I'm not really trying to count records. I'm just experimenting with and trying to learn how to deal with recordsets directly in VBA. I'm familiar with DCount(), DSum(), DVar etc. I was just trying a code exaple out of a book and could not get it to work. In the past I have mostly manipulated records inside of forms using VBA and event driven procedures.

Don

 

[DonBerry]

Terry,

Thanks for the reply. I'm not really trying to count records. I'm just experimenting with and trying to learn how to deal with recordsets directly in VBA. I'm familiar with DCount(), DSum(), DVar() etc. I was just trying a code exaple out of a book and could not get it to work. In the past I have mostly manipulated records inside of forms using VBA and event driven procedures.

Don

 

[TheInsider]

Yes, I would recommend using ADO in Access 2000. DAO will still work fine if you add the reference to it, however, DAO is becoming obsolete. I, myself, have spent quite some time with DAO and very little with ADO. The following is an excerpt of a post by Javrix in reply to my thread on which database to use as an e-commerce backend (thread333-81302).


I thought I should bring this up. ODBC is slowly getting outdated. The new thing is OLE-DB. I recommend you learn this over ODBC, since OLE-DB can also use ODBC.

OLE-DB is much more powerful and can communicate with many databases. If your going into databases, I would also recommend you learn ADO. You use ADO to talk to OLE-DB, which then communicates with the actual database.
Rob Marriott
rob@career-connections.net