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

[Robert]

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
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

[insomniux]

Isn't it so that
" If Not (rst.BOF Or rst.EOF) Then "
always evaluates to True since you are at rst.BOF directly after
opening the recordset? Or am I wrong.
What happens if you use AND, since in an empty record set BOF = EOF (or
....)

Why don't you use DCount() or SELECT count() FROM .... to get the
number of records in the database. If the dataset is large your
approach may take some time since you have to move to the last record
and back in order to be able to count them.
Mike

[Robert]

Borrowing some code from another post on recordcount, I inserted the
following immediately after the set rst.openrecordset

strMsg = "BOF: " & rst.BOF & ". EOF: " & rst.EOF & _
". RecordCount: " & rst.RecordCount
MsgBox strMsg
Me.RecordSource = sqlF98
strMsg = BOF: True. EOF: True. ReocrdCount: 0

However, when I set the form's recordsource to rst, I get 16 records
displayed. what is going on?

[arthurjr07]

As Mike said..
You have to move to last record and back
again before u used the recordcount property

like this

rst.MoveLast
rst.MoveFirst
rst.RecordCount

[salad]

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

[Robert]

rst.movelast generates an error.

Run time error 3021
No Current Record.
The recordset is not returning records yet when I set the form property
recordsource to the same SQL query, I get 16 records.

My sql query is as follows

SELECT [tbl1-Msg].MsgId, [tbl1-Msg].MsgName, [tbl1-Msg].MsgNote,
[tbl3-Msg-2-Wrd].MsgWrdNum, [tbl3-Msg-2-Wrd].WrdId,
[tbl5-Wrd-2-Bit].BitNum, [tbl5-Wrd-2-Bit].BitId,
[tbl5-Wrd-2-Bit].BitEncode, [tbl5-Wrd-2-Bit].BitEncWord,
[tbl5-Wrd-2-Bit].BitDesc, [tbl5-Wrd-2-Bit].BitAdvisory FROM ([tbl1-Msg]
INNER JOIN [tbl3-Msg-2-Wrd] ON [tbl1-Msg].MsgId=[tbl3-Msg-2-Wrd].MsgId)
INNER JOIN [tbl5-Wrd-2-Bit] ON
[tbl3-Msg-2-Wrd].WrdId=[tbl5-Wrd-2-Bit].WrdId where [tbl1-Msg].MsgId
Like '%test%';
I cut that right from the SQL view of a defined query in the database.
WHen I execute it, I get 16 records that match the MsgId like "test".

So, the rst = db.openrecordset(sql) doesn't return any records. Why?

[Lyle Fairfield]

From
Like '%test%';
one would guess you have opted for Ansi 92.

There are potential problems with this if it is not used carefully.
Here are some outlined in the help file:

********
The two ANSI SQL query modes (ANSI SQL query mode: One of two types of
SQL syntax: ANSI-89 SQL (also called Microsoft Jet SQL and ANSI SQL),
which is the traditional Jet SQL syntax; and ANSI-92 SQL, which has new
and different reserved words, syntax rules, and wildcard characters.),
ANSI-89 and ANSI-92, are not compatible. When you create a Microsoft
Access database (Microsoft Access database: A collection of data and
objects (such as tables, queries, or forms) that is related to a
particular topic or purpose. The Microsoft Jet database engine manages
the data.), you need to decide which query mode you are going to use,
because mixing queries (query: A question about the data stored in your
tables, or a request to perform an action on the data. A query can
bring together data from multiple tables to serve as the source of data
for a form, report, or data access page.) created in both query modes
could produce runtime errors or unexpected results. The range of data
types, reserved words, and wildcard characters are different in each
query mode.

Example of a query using wildcard characters

A query that uses wildcard characters in a criteria expression can
produce different results under each query mode. For example, if you
run:

An ANSI-89 SQL query in a database set to ANSI-92 query mode, such as:
SELECT * FROM Customers WHERE Country Like 'U*'

It returns all customers from a country/region named "U*", not all
countries/regions beginning with the letter "U", because the asterisk
(*) is not a wildcard character in ANSI-92 SQL.

An ANSI-92 SQL query in a database set to ANSI-89 query mode, such as:
SELECT * FROM Customers WHERE Country Like 'U%'

It returns all customers from a country/region named "U%", not all
countries/regions beginning with the letter "U", because the percent
sign (%) is not a wildcard character in ANSI-89 SQL.

Example of a query with a duplicate field and alias name

If a query uses an alias that is the same as a base column name and you
create a calculated field using the ambiguous name, the query will
produce different results under each query mode. For example, if you
run the following query:

SELECT Orders.OrderID AS Freight, Orders.Freight, [Freight]*1.1 AS [New
Freight] FROM Orders;

Under ANSI-92 SQL, Access calculates the New Freight value using the
Freight field, and therefore increases each Freight by 10%. Under
ANSI-89 SQL, Access calculates the New Freight value using the Freight
alias, and therefore increases each OrderID by 10%.

How to avoid problems caused by mixing queries under different ANSI SQL
query modes in the same database

In general, avoid doing the following to prevent problems caused by
mixing queries created under different ANSI SQL query modes:

Converting an Access database set to ANSI-92 SQL query mode from 2002 -
2003 file format to 2000 or 97 file format. ANSI-92 SQL query mode is
only supported in the 2002 - 2003 file format.
Changing the ANSI SQL query mode for the current database after you've
created one or more queries. If you must do this, retest the existing
queries to ensure that they still run or produce expected results, and
rewrite the queries if necessary.
Importing queries created under one ANSI SQL query mode into an Access
database set to another mode, or exporting queries created under one
ANSI SQL query mode to an Access database set to another mode.
Changing the ANSI SQL query mode in an existing application. This could
break your application and require rewriting your application.
In Access 2000, you can only programmatically change the ANSI SQL query
mode and any queries created under ANSI-92 mode were hidden in the
Database window (Database window: The window that appears when you open
an Access database or an Access project. It displays shortcuts for
creating new database objects and opening existing objects.). In Access
2002 or later, you or a user can change ANSI SQL query mode through the
user interface and ANSI-92 queries are no longer hidden in the Database
window, so you should help prevent accidental or intentional changes to
the ANSI SQL query mode of your application by helping protect your
code and helping prevent the changing of the query mode through the
application's user interface.

Using ambiguous aliases and column names. To avoid confusion, ensure
that aliases and column names are always unique in an SQL statement.
**********
It's quite difficult to guess what is wrong from afar. It is more
difficult when you post code snippets rather than entire modules.

But if I had to guess I would guess that the problem involves Like
'%test%'. I would alos guess that my guess had a 1/1000 th chance of
being correct.

You have said, "I cut that right from the SQL view of a defined query
in the database". I have never seen a saved or wizard created query
string use a lower case "where". How did you achieve this?

In any case BOF (or EOF) when a recordset is opened is entirely
sufficient for checking for the existence of records in a recordset.
Those who recommend more to "be sure" should also check George Bush's
approval rating, the condition of Mount St Helena and Paris Hilton's
latest boy friend just in case these may be pertinent as well.

[Robert]

Lyle gets the prize and his 1/1000 chance was exactly correct. Seems I
have an 2002/2003 database and was using a DAO recordset with the % in
the query. Well that returned zero records that match. However, when I
set the form.recordsource to the sql query with the % in the like
clause, I get a valid SQL query and it returns records.
I changed the % to * in the set rst = and I get 16 records but I get
invalid property errors when trying to set the form.recordsource to
rst.

I know I have a problem between ADO and DAO objects. I am now trying to
determine which type I would like to use. It seems from the discussions
on this forum that DAO is on the way out but not dead. However most
examples I find on the web and in help are from DAO methods. This
database is currently static and in MS Access. I don't see it migrating
to SQL server anytime soon but change is always a constant so who
knows. How difficult is it to change from DAO to ADO or vice versa?

I would prefer to stay in the most current technology but it really
seems to be getting in the way.

Suggestions?

[Lyle Fairfield]

"Robert" <ro**************@boeing.com> wrote in
news:11*********************@j55g2000cwa.googlegro ups.com:

Lyle gets the prize and his 1/1000 chance was exactly correct. Seems I
have an 2002/2003 database and was using a DAO recordset with the % in
the query. Well that returned zero records that match. However, when I
set the form.recordsource to the sql query with the % in the like
clause, I get a valid SQL query and it returns records.
I changed the % to * in the set rst = and I get 16 records but I get
invalid property errors when trying to set the form.recordsource to
rst.

I know I have a problem between ADO and DAO objects. I am now trying to
determine which type I would like to use. It seems from the discussions
on this forum that DAO is on the way out but not dead. However most
examples I find on the web and in help are from DAO methods. This
database is currently static and in MS Access. I don't see it migrating
to SQL server anytime soon but change is always a constant so who
knows. How difficult is it to change from DAO to ADO or vice versa?

I would prefer to stay in the most current technology but it really
seems to be getting in the way.

Suggestions?

If you are going Access to Jet(Access) and binding your forms and reports
then TTBOMK you have to use DAO. (Well, there's another way but it's
arcane and I don't know of anyone else who does it, and the definition of
"binding" is stretched.)

Until we see ACE who can know for sure what will be appropriate? I know
we have predictions, statements of intention, etc. These are great but
can we be sure everything will be as it is planned?

You can set a form's recordset to an ADO recordset in Access ... hmmmmmmm
2002 at least ... not sure if it goes back to 2000, and a report's
recordset in an ADP in 2003, and there are some advantages to this,
including being able to edit the form within a transaction and
disconnecting the recordset.

But the recordset's properties must be set to specific values. This is an
example of the Transaction thing.

Dim c As ADODB.Connection

Private Sub Form_Close()
With c
If MsgBox("Save Changes?", vbYesNo Or vbQuestion) = vbYes Then
.CommitTrans
Else
.RollbackTrans
End If
End With
End Sub

Private Sub Form_Open(Cancel As Integer)
Dim r As ADODB.Recordset

Set c = CurrentProject.Connection
c.BeginTrans

Set r = New ADODB.Recordset

With r
.CursorLocation = adUseClient
.LockType = adLockBatchOptimistic
.CursorType = adOpenStatic
.ActiveConnection = c
.Open "SELECT * FROM Table1"
End With

Set Me.Recordset = r
End Sub

'I'm undecided about the necessity of this

Private Sub Form_Error(DataErr As Integer, Response As Integer)
c.RollbackTrans
End Sub

Is this worthwhile? For me it's OK because I try to run ADO exclusively.
But I'm doing this mostly to see if it can be done, and how well. Even so
Access does DAO things behind the scenes. So for most, with Access FE,
JET BE I'd recommend DAO (which should not be construed that I recommend
manipulating DAO recordsets; I recommend executing SQL instead and this
will almost always be simpler, safer and quicker.)

--
Lyle Fairfield

[David W. Fenton]

"Lyle Fairfield" <ly***********@aim.com> wrote in
news:11**********************@j55g2000cwa.googlegr oups.com:

In any case BOF (or EOF) when a recordset is opened is entirely
sufficient for checking for the existence of records in a
recordset. Those who recommend more to "be sure" should also check
George Bush's approval rating, the condition of Mount St Helena
and Paris Hilton's latest boy friend just in case these may be
pertinent as well.

Has anyone recommended doing that plus something else?

Or is it that people like me recommend *not* doing two tests, but
instead doing just one (i.e., testing if RecordCount > 0)?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

"Robert" <ro**************@boeing.com> wrote in
news:11*********************@j55g2000cwa.googlegro ups.com:

I know I have a problem between ADO and DAO objects. I am now
trying to determine which type I would like to use. It seems from
the discussions on this forum that DAO is on the way out but not
dead. However most examples I find on the web and in help are from
DAO methods. This database is currently static and in MS Access. I
don't see it migrating to SQL server anytime soon but change is
always a constant so who knows. How difficult is it to change from
DAO to ADO or vice versa?

I would convert back to ANSI-89 since the benefit of ANSI-92 in
Access seems to me only in interfacing with non-Access technologies
(such as ADO or a server back end). The other benefit is that the
help in this newsgroup will then not need to be adapted to a
different SQL variant.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/