Access query to update or append PART 2

Posted on 2012-08-27
Last Modified: 2012-08-27

This is a continuation of

In that question, one of the Experts gave me some SQL for an UPSERT query - a combination of UPDATE and INSERT. At this point, I noticed that sometimes it doesn't work, so I wanted to verify the syntax and make sure that I have it right.

Here's my SQL - can anyone help?

UPDATE MSysObjects LEFT JOIN ExportObjExportedT 
          ON MSysObjects.Name = ExportObjExportedT.ObjectName 
SET ExportObjExportedT.ObjectName = [MSysObjects].[Name], 
         ExportObjExportedT.LastExported = now() 
WHERE objectname = 'LogOutF';

Open in new window

Question by:etech0
    LVL 47

    Accepted Solution

    As soon as you apply the WHERE clause to a field on the Right side (ExportObjExportedT) of this join, it basically invalidates the LEFT JOIN and turns it into an INNER JOIN.

    Not sure I get the whole point of this syntax anyway.

    if you already know [ObjectName] ("LogOutF") then you don't need the UPSERT syntax, just use:
    UPDATE ExportObjExportedT
    SET LastExported = Now()
    WHERE ObjectName = "LogOutF"

    Open in new window

    However, if you are not certain that LogOutF has already been exported, then you could change the syntax to:
    UPDATE MSysObjects 
    LEFT JOIN ExportObjExportedT 
    ON MSysObjects.Name = ExportObjExportedT.ObjectName 
    SET ExportObjExportedT.ObjectName = [MSysObjects].[Name]
         , ExportObjExportedT.LastExported = now() 
    WHERE MSysObjects.Name = 'LogOutF';

    Open in new window

    That should update the record if it already exists and append it if it doesn't.
    LVL 10

    Author Comment

    I'm not sure I understand your SQL, so let me clarify the point of mine and then maybe we can figure each other out.

    The point of my SQL is this:
    I'm using VBA to export certain objects in my database to another database on a regular basis. I set up this SQL so that after every object that gets exported, it should update the 'LastExported' field to the current date/time. That way, the next time I open this exporting system (that's supported by a number of queries), I'll only have to choose from the objects who's modified date is later than it's lastexported date.
    What this SQL should do is: If the particular object (in this case LogoutF) is already in the ExportObjExportedT table, it should update its LastExported field to Now(). If it's not in the table yet (ie: it's the first time I'm exporting it because it's a new form), then it should add it to the table, and set LastExported as well.
    I hope I made that clear. Thanks!
    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    Yep, that's what my modification should accomplish, but given your purpose, I would think that you might have a query that you use to create a recordset that looks something like:

    SELECT MSysObjects.Name
    LEFT JOIN ExportObjExportedT
    ON MSysObjects.Name = ExportObjExportedT.ObjectName
    WHERE mSysObjects.DateUpdated > NZ(ExportObjExportedT.LastExported, #1/1/2000#)

    This would identify all of those objects that need to be exported, although I assume that you would add another criteria in the query on the [Type] field to ensure you are only selecting the appropriate object types you are looking for.

    I would build a recordset from this SQL, and loop through the list of objects to be exported.  Inside that loop, I would build a SQL string which would do the UPSERT query like:

    strSQL = "UPDATE MSysObjects " _
               & "LEFT JOIN ExportObjExportedT " _
               & "ON MSysObjects.Name = ExportObjExportedT.ObjectName " _
               & "SET ExportObjExportedT.ObjectName = [MSysObjects].[Name] " _
                    & ", ExportObjExportedT.LastExported = now() " _
               & "WHERE MSysObjects.Name = " & chr$(34) & rs![Name] & chr$(34)
    currentdb.execute strsql, dbfailonerror
    LVL 10

    Author Comment

    @fyed: Yes - your modification fixed it. Thanks!

    With regard to your recordset recommendation, I actually already created something similar to that. The reason I don't want to do it totally via code is that which objects I'm exporting can vary. What I have is a table called ExportObjListToExportT. When I open my export form, it runs two queries - a delete query to wipe out old data, and a complicated append query. The append query appends object names to the table, filtering out certain object types (based on another table), filtering out objects that were exported after they were last modified, and filtering out certain objects that I know I never want to export.

    It then displays this newly created list of objects in a subform on the export form. I check off the ones I want to export (I also created check all and uncheck all buttons), and click GO, and watch the magic!

    It's saved me tons of time already - exporting a bunch of objects manually was a nightmare!

    Thanks for your help in working out this bug!
    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    glad to help.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Suggested Solutions

    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now