Getting the number of records returned from a query?

[lifter10]

Is there a way to capture the number of records returned for a specific query by placing it in a variable, table, etc.?

Here is what I'm trying to do. I have a process that imports files one at a time into a specific table <Import Table>. With this table, I need to take out specific rows of data, but I also need to log these rows in another table <Log Table>. I am currently using an append query to log the records <Log Table> and using delete query to delete the records <Import Table>. I want a check to make sure that the number of records deleted equals the number of records appended.

I have had problems before where I am trying to make reports that capture changes to a table throughout a process and this is the reason for the check. If anyone has a better process, I would appreciate the help.

Thanks,

Chris

 

[scriverb]

Try using the DCount function. By using the wildcard "*" you can get a count of all records.

DCount("*", "[i]yourtablename[/i]")

Post back if you have any questions.


[b][COLOR=006633]Bob Scriver[/color][/b]
[img]http://home.nyc.rr.com/aceman1/Graphics/MIState1.gif[/img] [b][COLOR=white 006633]MSU Spartan[/color][/b]

 

[BNPMike]

If instead of deleting them, you first mark them as deleted - either with a 'deleted' field or by creating a temporary table to hold the ids of the ones to be deleted, you can do an outer join and select any records in one table but not in the other. Then use SQL to carry out the deletions - it will report errors if it encounters any.

 

[JonFer]

One option is to write a select query (Select * from ...) with the criteria for the records that are moving. Run your append first and then the delete, both using the select query in the FROM clause instead of the table. Then you know the same records are included in both, barring any user updates to the table in between the two queries.

I've never used the locking option "All records" in my queries so I don't know if there are problems with it but you could look into that as part of this option, too.

 

[JonFer]

If you use VBA, you could also start a transaction and use the DAO QueryDef.Execute method to run the two queries. The DAO QueryDef object has a "RecordsAffected" property that you can compare after both queries execute and then do a commit or rollback.

 

[lifter10]

Thanks everyone for the responses! I'm looking to do this check in VB, so I think Jon's last response might fit me best. Thanks again. Chris

 

[scriverb]

Here is VBA code to obtain your counts and run your queries and then to analyze that everything worked okay. Just add your code to the else portion of the IF statement to provide messages and corrective action to the data tables as they don't balance.

Dim vImportBegCount as Long
Dim vImportAfterDelete as Long
Dim vLogCount as Long

vImportBegCount = DCount("*", "[i]Import Table[/i]")
DoCmd.Setwarnings False
DoCmd.OpenQuery "qryAppendToLog"
vLogCount = DCount("*", "[i]Log Table[/i]")
DoCmd.OpenQuery "qryDeleteFromImport"
vImportAfterDelete = DCount("*", "[i]Import Table[/i]")
If vImportBegCount = vImportAfterDelete + vLogCount then
   [highlight]'Everything balances[/highlight]
else
   [highlight]'Counts do not balance
   'code to fix the problem whatever that is[/highlight]
End If

As long as the Append and Delete queries are using the same criteria and the tables are structured the same there really should be no need for this checking as the records should always balance. The Append query is the only place that this may be a problem and that is only if the table has indexes that may have duplication problems. But as long as they are set to allow dups then everything appended should also always be deleted.

Post back if you have any questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]