Results 1 to 4 of 4
  1. #1
    Stephen_K1 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2016

    Has anyone created an update query based on null results from a table or another query?

    I manage a database that gets daily updates with new data. Some tables get new records added since the last update while others get wiped completely before adding all data (this is because the elements of the second dataset may change frequently). I do the updates by first capturing the new data into temporary tables by pasting from Excel, then I open a form that has an "On Timer" event to run some VBA that shuttles the data around, ultimately wiping the temporary tables back to their original blank state. In the middle I stop to review the new data to ensure as best possible there are no entry errors from people who insist on ignoring the rules.

    (Note that the MS Access database is only meant to provide additional functionality. The actual source data is coming from Amdocs and transported to Excel via Business Objects - An obvious kludge, but I am at the mercy of our IT groups with regard to what I can do in Amdocs and when it can be done while I am in full control of what/when with regard to the Access data).

    The problem being, that on rare occasions, there will be an error while updating. With error testing off to avoid many repetitive mouse clicks, the whole task starts over. If this happens after one or more of the wrong tables is wiped, I lose all the data. My Band-Aid for this is to exit Access temporarily before executing the final update macros to make a copy of the database that saves my place. Then I reopen and finish from where I left off.

    However, it seems to me a more elegant solution would be to have update queries that check to see if null rows are returned prior to attempting to update. e.g. if the table that should have rows of temporary data does not, then skip deleting everything in the target table, otherwise proceed normally.

    The VBA in this is all at the elementary level. Here is a sample of the sequence at the end:

    Option Compare Database
    Option Explicit
    Private Declare Sub Sleep Lib "kernel32" (ByVal lngMilliSeconds As Long)
    Private Sub Form_Timer()
    ' PostFixed-NewData

    ' Turn off messages to avoid mouse click responses to running sequential maintenance queries
    DoCmd.SetWarnings (0)

    ' Append all new cases to the standard Case table
    DoCmd.OpenQuery "Append New Cases", acViewNormal, acEdit

    ' Delete all previously existing details
    DoCmd.OpenQuery "Delete Old Details", acViewNormal, acEdit

    ' Append all new details to the standard Detail table
    DoCmd.OpenQuery "Append New Details", acViewNormal, acEdit

    ' Append new case notes to the standard Case Notes table
    DoCmd.OpenQuery "Append New Case Notes", acViewNormal, acEdit

    ' Remove all case records from the cases holding area
    DoCmd.OpenQuery "Delete Updated Cases", acViewNormal, acEdit

    ' Remove all detail records from the details holding area
    DoCmd.OpenQuery "Delete Updated Details", acViewNormal, acEdit

    ' Remove all case note records from the case notes holding area
    DoCmd.OpenQuery "Delete Updated Case Notes", acViewNormal, acEdit

    ' Turn messages back on to ensure getting appropriate warnings or other information in response to actions
    DoCmd.SetWarnings (-1)

    End Sub

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    If you are attempting to find out if there are records in a recordset before doing something you can just use

    if dcount("*", "tablename") > 0 then
        'do whatever you're going to do
        msgbox "There are no records in the source table so I'm doing nothing

  3. #3
    Stephen_K1 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2016
    Thanks very much rpeare. I will give that a shot.

  4. #4
    Stephen_K1 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2016


    Quote Originally Posted by Stephen_K1 View Post
    Thanks very much rpeare. I will give that a shot.
    Thanks again. I confirm that adding this test in several appropriate places takes care of the issue and notifies me with the message boxes in the event that I do have an Access crash in the middle of execution again.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 05-21-2015, 11:26 AM
  2. Replies: 24
    Last Post: 04-08-2015, 02:59 PM
  3. Replies: 2
    Last Post: 09-27-2014, 09:48 AM
  4. Replies: 1
    Last Post: 06-03-2014, 06:56 AM
  5. Replies: 1
    Last Post: 04-23-2012, 10:40 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums - Senior Forums