Re: How to get recordcount or number of records when using linked tables

Robert wrote:
How do you get an accurate count of the number of records returned from
a query when using linked tables.

I have an access 2003 database as a front end to another access 2003
database that contains only tables. I have linked the tables for the
front end to the back end database.
I am trying to set the recordsource of a form to a query established by
the user to narrow the scope but I don't want to display the form if
there are no records that match the query. So,
given

Dim db As Database
Dim rst As Recordset
Dim sql As String
Dim MyRecordSource As String
Dim RecordCountVariable As Integer

Set db = CurrentDb()

Set rst = db.OpenRecordset(sqlF98, dbOpenDynaset, dbReadOnly)

If Not (rst.BOF Or rst.EOF) Then
rst.MoveLast
RecordCountVariable = rst.RecordCount
rst.MoveFirst '(If you are going to do something else
here)
Else
RecordCountVariable = 0
End If
If RecordCountVariable = 0 Then
MsgBox ("No records match search criteria")
Else
Me.RecordSource = sqlF98
End If


Set db = CurrentDb()
Set rst = db.OpenRecordset(sqlF98, dbOpenDynaset, dbReadOnly)
If rst.RecordCount > 0 then rst.MoveLast
msgbox rst.recordcount
rst.close
set rst = Nothing



rst.recordcount always returns zero (0) but I know there are records
because when I set me.recordsource = sqlF98 the filter is set correctly
and I only get a subset of the data.

Help on .recordcount indicates it doesn't work on linked tables. What
property do you use then to get # of records returned in a query?

Thanks in Advance

Robert

.

Relevant Pages

  • Re: DAO takes too much time to link tables
    ... I am trying to link some tables to a back-end database. ... 'Link the tables contained at the given query. ... Dim rst As DAO.Recordset ... Dim success As Boolean ...
    (microsoft.public.access.modulesdaovba)
  • Re: HELP WITH AUTO EXE PROGRAMMING/CODE
    ... Create a separate database for those times when you won't have your database ... create a Query that returns only the Records about which you ... Dim strsubject As String ...
    (comp.databases.ms-access)
  • RE: Need Help | Rolling out new App. | Trouble with some computers.
    ... Option Compare Database ... Dim varReturn As Variant ... Dim lngAuth As Long, strDept As String, strFirst As String, strLast As ... Set rst = db.OpenRecordset ...
    (microsoft.public.access.modulesdaovba)
  • Re: Scroll through records and get match
    ... "User-defined type not defined" at Dim dbs As DAO.Database. ... "Invalid argument" Set rst = dbs.OpenRecordset ... that I posted is a select query. ... cannot use it to open an SQL statement that you generate via code. ...
    (microsoft.public.access.modulesdaovba)
  • RE: Need Help | Rolling out new App. | Trouble with some computers
    ... Option Compare Database ... Dim varReturn As Variant ... Dim lngAuth As Long, strDept As String, strFirst As String, strLast As ... Set rst = db.OpenRecordset ...
    (microsoft.public.access.modulesdaovba)