CurrentDb.RecordsAffected

[jfp]

Does CurrentDb.RecordsAffected work properly with a Make-Table query ?
I have some code which uses CurrentDB.Execute "SELECT ... INTO ... FROM
...". The table is made correctly, but when i then use
CurrentDb.RecordsAffected i get 0 intead of the number of records in the
table. (Code is too messy post unless you insist, but there are no
intervening Execute statements)

 

[Bruce M. Thompson]

Does CurrentDb.RecordsAffected work properly with a Make-Table query ?

I have some code which uses CurrentDB.Execute "SELECT ... INTO ... FROM
..". The table is made correctly, but when i then use
CurrentDb.RecordsAffected i get 0 intead of the number of records in the
table. (Code is too messy post unless you insist, but there are no
intervening Execute statements)

Dim a database object, set your reference, execute your sql and then check the
..RecordsAffected property. If you simply use CurrentDb, the database instance is
lost before you can check the property:

'Dim your object variable
Dim MyDb As Database
'Set to the current database
Set MyDb = CurrentDb()
'Execute the sql
MyDb.Execute "SELECT ... INTO ... etc.
'Now, retrieve the .RecordsAffected property
MsgBox MyDb.RecordsAffected & " records were inserted into the new table."