Results 1 to 2 of 2
2003-05-05, 17:29 #1
- Join Date
- Apr 2003
- Thanked 0 Times in 0 Posts
checking if a query produces a result (97)
I am looking for a simple way of checking if a query such as an append query when writing to a table produces a result or if nothing is found?
I was thinking of checking the eof and bof in a recordset then writing that to the table but that seems like a lot of work because I need to do this a few times.
Any help is greatly appreciated
2003-05-05, 17:44 #2
- Join Date
- Mar 2002
- Thanked 30 Times in 30 Posts
Re: checking if a query produces a result (97)
If you execute a query interactively, you'll get a message box stating the number of records involved.
If you execute an action query in code using the Execute method of the DAO Database object, you can use the RecordsAffected method to find out how many records were involved:
Dim dbs As DAO.Database
Dim strSQL As String
strSQL = "INSERT INTO tblSomething ( Field1, Field2 ) SELECT Field1, Field2 FROM tblOther"
Set dbs = CurrentDb
MsgBox "The number of records affected is " & dbs.RecordsAffected
Set dbs = Nothing
If you want to test in code how many records will be affected before you execute the append query, you will have to construct the equivalent selection query and open a recordset based on that. This won't necessarily give you the correct answer: the selection query can't tell you if records won't be appended because of key conflicts etc.