Determine Number of Rows in DoCmd.RunSQL Statement 2

[TomHW]

Is there any way to determine the number of rows that are about to be appended to a table using an insert query through DoCmd.RunSQL [query] ?

If I don't turn warnings off, Access will visually display how many rows are about to be appended, but is there any way to access this in the code?

Thanks,
Tom

 

[Remou]

You can use the RecordsAffected property:

strSQL = "INSERT INTO tblA ( Field1, Field2 ) "
strSQL = strSQL & "SELECT tblB.Field1, tblB.Field2 "
strSQL = strSQL & "FROM tblB;"

Set qdf = CurrentDb.QueryDefs("qryQuery")
qdf.SQL = strSQL
qdf.Execute

MsgBox "Added " & qdf.RecordsAffected

 

[PaulBricker]

What does your Insert statement look like.
You might create a recordset first, that looks at the data you want to insert and then use the rst.RecordCount to see how many rows you are inserting.


Paul

 

[TomHW]

Thanks for the replies.

Remou:

I haven't used DAO before so this idea never occurred to me. Since I have a dynamic SQL query that is run hundreds of times I decided to try a temporary QueryDef using the following instead but am having a problem -

dim qdf as DAO.QueryDef
dim strSQL as String

strSQL = "INSERT INTO tblA SELECT * FROM tblB;"
Set qdf = CurrentDb.CreateQueryDef("", strSQL)
qdf.Execute

MsgBox "Added " & qdf.RecordsAffected

I keep getting an error at the qdf.Execute line saying that 3 arguments are expected. This may just be my complete inexperience with DAO and QueryDefs, but any help would be appreciated.





PaulBricker:

I thought about this, but running the query from which the insert draws its data for each item (of which there are around 500-600) takes around 6 seconds. Doubling this by having to execute the query in a recordset just to find out the number of rows returned and then running the query again in the insert statement would be out of the question. At this point I do not know how to insert data from a recordset into another table without looping through each row of the recordset and using RunSQL with an insert query for each row or opening a recordset on the table and manually adding each row of the dynamic recordset to it, each of which would also add considerably to the length of time this overall process takes to run. If you know of any way to easily add the data from a dynamic recordset to a table, please let me know.




Thanks,
Tom

 

[Remou]

TomHW
I have tried your code and it works for me. Is it possible that there is an error in your SQL statement?

As an aside, you may wish to add dbFailOnError.
qdf.Execute dbFailOnError

 

[PaulBricker]

I think if you can use Remou's method that would be best. If you use the recordset, you don't have to loop thru all the records.

Dim rst as DAO.Recordset
Set rst = CurrentDb.OpenRecordset("Select * From TblB"),dbOpenDynaset)
rst.MoveLast
MsgBox "The records effected is " & rst.RecordCount
REST OF CODE HERE

You just move to the end and then see what the count is. The one advantage it might have is you can read the count before the insert statement is run if for some reason you want to stop the process for some reason.

Paul

 

[danvlas]

You may also consider CurrentDb.Execute "queryname", lngRecordsAffected

But in the end, the RecordsAffected is the answer...

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[TomHW]

Remou:

You are correct. I ran the code with the specific example I posted and it worked perfectly. The problem with my SQL statement was along the lines:

INSERT INTO tblA SELECT * FROM [qryA];

The SQL statement worked with RunSQL, but with a QueryDef it did not like that the data behind FROM was from a query and not a table. I'm sure there is some way around this, but as this needed to get out, I simply inserted the data into an empty temp table, tblB, and then used the code above to insert everything from tblB into tblA, using RecordsAffected to determine if no data was inserted. Thank you for the help!




PaulBricker:

I would not have had to loop through all of the records in a recordset, but the query above, [qryA], takes around 6 seconds to run and having to run it over 500 times really adds up. By using the recordset, I would have either had to run the query twice, once for the insert and once for the recordset, or come up with some way to open the recordset and then transfer that data to tblA. Looping through the recordset and adding each individually is the only way I know how to do that, and that takes long enough to make a significant impact on performance. Your help is appreciated.


Thanks,
Tom