Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Apr 2003
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    checking if a query produces a result (97)

    Hi
    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
    TIA

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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
    dbs.Execute strSQL
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •