Access query to update or append PART 2


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

LVL 10
Who is Participating?
Dale FyeCommented:
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.
etech0Author Commented:
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!
Dale FyeCommented:
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
etech0Author Commented:
@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!
Dale FyeCommented:
glad to help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.