counting the number of records in an append query

[Paul Ponzelli]

I would like to count the number of records in an append query using VBA
without running the append query. That is, I would like to obtain a count
of the number of records that an append query WOULD append to the
destination table before deciding whether to actually append the records.

I tried using the RecordCount property of the Recordset object, but it seems
you first have to open the recordset in ADO before you obtain a record
count. But since I don't want to run the append query at this point in the
code, I don't want to open the query.

I don't think I can use Dcount() either, because I'm going to be looping
through a number of queries (specified as field values in in a table), and
not all the append queries have the same field names, so I don't know what
value to put in the first argument (the field name) of the Dcount()
function.

How can I get a count of the number of records as I loop through a number of
append queries?

Thanks in advance,

Paul

 

[PC Datasheet]

DCount("*","NameOfAppendQuery")

 

[Paul Ponzelli]

Thanks for the suggestion, PC Datasheet.

However, when I tried using this expression,

DCount("*","NameOfAppendQuery")

I get an Run Time Error '3417' saying:

"An action query cannot be used as a row source."

Any ideas how to overcome this obstacle?

 

[PC Datasheet]

Paul,

What you say here is inconsistent with your original post. You said that you
want to do something like the following pseudocode:
Dim NameOfAppendQuery As String
For Each AppendQuery In ListOfAppendQueries
If DCount("*",NameOfAppendQuery) = YourCriteria Then
DoCmd.SetWarnings False
Run NameOfAppendQuery
DoCmd.SetWarnings True
End If
Next AppendQuery

There is nowhere here where you would use an action query for a row source!

 

[Paul Ponzelli]

The pseudocode you wrote is a good representation of what I'm trying to do -
namely count the number of records (to be appended) by each append query,
and do something if it meets the criteria. But the append query is itself
an action query, and when my code tries to evaluate the DCount() function,
DCount("*",NameOfAppendQuery), Access renders the error message "An action
query cannot be used as a row source."

I'm not myself asserting I'm trying to use the append (action) query as a
row source. That's coming from Access' own error message when I try to run
the code.

One work-around has occurred to me - I could write a corresponding select
query for each append query, and apply the DCount() function to the select
queries, thereby eliminating VBA's objection to using an action query as the
argument. I'd prefer not to have to add a raft of select queries to my
database just to perform this test, so my first choice would be to find a
way to count the records to be appended directly from the append query, but
unless I can either get DCount() to work or find another way to obtain the
number of records from the append queries, I guess I'll have to create the
select queries.

Thank you for the information you've provided thus far. Please let me know
if you can suggest a better solution than adding the select queries to the
database.

Paul

 

[PC Datasheet]

Paul,

Try the following code:
Dim Db As DAO.Database
Dim QDF As QueryDef
Dim Rst As DAO.Recordset
Dim AppendSQL As String
Dim SelectSQL As String
Set Db = CurrentDb
For Each QDF In Db.QueryDefs
If Left(QDF.SQL, 6) = "Insert" Then
AppendSQL = QDF.SQL
SelectSQL = Mid(AppendSQL, InStr(AppendSQL, "Select"))
Set Rst = Db.OpenRecordset(SelectSQL)
Rst.MoveLast
If Rst.RecordCount = <Your Criteria> Then
DoCmd.SetWarnings False
DoCmd.OpenQuery QDF.Name
DoCmd.SetWarnings True
End If
End If
Next QDF

Steve
PC Datasheet

 

[Paul Ponzelli]

Works great, Steve. Nice code.

Thanks for providing another solution for us. (You've done this a couple of
times before).

Paul