We help IT Professionals succeed at work.

Number of records appended

Tom Knowlton
Tom Knowlton asked
on
Is there a way to find out how many records an append action query has appended to a table after it runs?

For example:

intCountBefore = DCount("EntryID", "tblIUISend")
DoCmd.RunSQL "myAppendQuery"
intCountAfter = DCount("EntryID", "tblIUISend")

Except I am not confident the count is correct:

intCountBefore = DCount("EntryID", "tblIUISend")
DoCmd.RunSQL "myAppendQuery"
'Suppose some other records are added here...
intCountAfter = DCount("EntryID", "tblIUISend") ' would not be correct.

Does this make sense?
Comment
Watch Question

Run your sql by way of a query def object and then check the records affected property. Something like this:


Public Function test()
Dim qdef As QueryDef
Dim Recs As Integer

Set qdef = CurrentDb.QueryDefs("MyAppendQry")
qdef.Execute
Recs = qdef.RecordsAffected

Qdef.Close
Set qdef = Nothing
End Function


Note: this will only work with action queries . . .
Tom KnowltonWeb developer

Author

Commented:
Does the qdef.Execute take as long to run as the actual query would?
Or this if you don't already have a query saved . . .

Public Function test()
Dim qdef As QueryDef
Dim Recs As Integer

Set qdef = CurrentDb.CreateQueryDef("")
qdef.SQL = "MyAppendStatement"
qdef.Execute
Recs = qdef.RecordsAffected

qdef.Close
Set qdef = Nothing
End Function
Yeah, it should . . .
try something like:

dim dbs as database
dim rst as recordset
dim x as long

set dbs = currentdb
set rst = dbs.openrecordset("myAppendQuery")

rst.movelast
x = rst.recordcount

msgbox("There were " & x & " records affected.",vbokonly)
rst.close
set rst = nothing
set dbs = nothing

you could also use this same logic to count the records before and after.
Did the second one work knowlton? It seemed like you were executing SQL on the fly the way you were using the RunSQL method there.  The second one should work for you if you paste your sql statement where I wrote "MyAppendStatement" . . . Good luck . . .
PsychoDazey: db.openrecordset won't work on action queries . . . only selects . . . check the help files.
Tom KnowltonWeb developer

Author

Commented:
Yeah, DoCmd.RunSQL should have been DoCmd.OpenQuery, I think.

Here is my current code:

intCountBefore = DCount("EntryID", "tblIUISend")
DoCmd.OpenQuery "qryAppendIUISendWithInvalid"
intCountAfter = DCount("EntryID", "tblIUISend")

MsgBox intCountAfter - intCountBefore & " records appended"
Tom KnowltonWeb developer

Author

Commented:
spcmnspff:

I think your example probably works fine.

I have not had a chance to check it yet.

Thanks,

Tom
This should work . . . run this in a separate module while stepping through(Press f8) and check the value of recs when you step past it . . .


Public Function test()
Dim qdef As QueryDef
Dim Recs As Integer

Set qdef = CurrentDb.QueryDefs("qryAppendIUISendWithInvalid")
qdef.Execute
Recs = qdef.RecordsAffected

Qdef.Close
Set qdef = Nothing
End Function
Tom KnowltonWeb developer

Author

Commented:
spcmnspff:

I tried your sample code.

My VBA environment does not know what type QueryDef is.

Tom
Are you using Access 97?  Maybe there's a reference we can set.
Tom KnowltonWeb developer

Author

Commented:
Access 2000, actually.
Open up your module and then go to the menu bar.  Click tools/references... and check the box next to "Microsoft DAO 3.51 Object Library".  That should do it . . .

Explore More ContentExplore courses, solutions, and other research materials related to this topic.