Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Access query to update or append PART 2

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

This is a continuation of http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27816078.html.

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
  • 3
  • 2
LVL 49

Accepted Solution

Dale Fye earned 2000 total points
ID: 38336552
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

ID: 38336572
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 49

Expert Comment

by:Dale Fye
ID: 38336650
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

ID: 38336868
@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 49

Expert Comment

by:Dale Fye
ID: 38337200
glad to help.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

564 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