count number of inserted records

[gojohnnygogogogo]

Hello,
I am running a query to insert records into a table, once that is run I want to run another query to show how many records were just inserted.
any ideas ?

 

[benjamenus]

A couple - not sure how much use they are!

1) You'd need to have an 'add date' field on your table. Once you have added your records, you can count all those with that days time. You'd also be able to look at how many records were added in previous batches.

2) Have a 'history table' - then add records to your main table. Calculate the difference:
records in main minus records in history = records added

I'll be keeping an eye on this post to see if a more elegant solution is offered.

 

[gojohnnygogogogo]

thank you, number 1 is the best idea I think, then count the MAX(AddDate ).

cheers

 

[gojohnnygogogogo]

I have found a problem with that, if 0 records are added, then using MAX will show that last lot of records that were added.

how can get around this ?

 

[benjamenus]

Depends on the frequency of update. If you are adding records daily (and you always run your count query immediately after update) the you can add the following to you count query:

AND AddDate = Now() ... i.e. todays date

Though thinking about it, that would negate the need to use the MAX function.

To get round your problem you could assign a BatchID (auto)number. It would probably have to be in a different table (BATCH). It would also have to be recorded against records in your MAIN table Even if no records are added to MAIN, a new record would be added to BATCH when you run your update/append query.

You then count the number of records in MAIN with a BatchID = to the BATCH.MAX(BatchID). If no records were added in MAIN, then it would return zero.

Hmmm, getting a bit more complex - but I think that would work?

 

[gojohnnygogogogo]

thank you for extra ideas.
I think just using the adddate = now() would work, because I am running the insert query, then the count query from a macro, and I'll only be adding at most 50 records at a time !

thanks for the help. !

 

[gojohnnygogogogo]

I am now displaying the result on a form using an If statement, can you se ewhy this doesn't work ?

=IIf([CountofSheetID]>0,[CountofSheetID] & " records added.","no records to add&quot

?

 

[cmmrfrds]

I am not sure on DAO but on ADO there is an option that will return the records inserted on the execute method. Some psuedo code.

dim recordsAffected as integer
dim sql as string

sql = "insert some records"
cn.execute(sql), recordsAffected

debug.print "number inserted = " recordsAffected