MS Access UPDATE with JOIN

I’m forced to use MS Access for some legacy system. I don’t know what Microsoft thought when they made it so that MS Access uses a flavor of SQL that’s pretty different from their main product SQL Server. Did they just think nothing or is there some evil intent? Nothing probably. But anyhow, they did it.

In this post, I describe how to do a UPDATE with JOIN query in MS Access.

I want to replicate the following SQL Server query (in TSQL) in MS Access.

-- MSSQL
UPDATE Sachwert SET Wert = '1', DATE = GETDATE()
    FROM Artikel a
    INNER JOIN Sachwert AS s on a.GUID = s.GUID
    INNER JOIN Sgpos AS sgp on sgp.SACHGRUPPE = s.SACHGRUPPE 
    INNER JOIN Catalog c on c.Year = a.Year 
    WHERE a.Artikel = '104905' AND sgp.SACHGRUPPE = 'V1'

For MS Access you have to do these changes:

  1. Put the name of the table where the update should happen before each name of each column which you want to change and remove the name of the table which is located before the SET. This gives
    UPDATE SET Sachwert.Wert='1', Sachwert.DATE = GETDATE().
  2. Put the SET clause after the JOINs, remove the word FROM but leave the word UPDATE at the beginning.
  3. If you have defined an alias, you must use this alias in the rest of the query. You then cannot longer use the original name. In our case, the SET clause must change to SET s.Wert='1', s.DATE = GETDATE() because of the Sachwert AS s.
  4. If you have N JOINs, you have to add N-1 sets of parentheses. The left parentheses must all be directly after the UPDATE, the right ones one after each JOIN-clause – except the last one.
  5. GETDATE() is called NOW() in Access.

So in the end you get this:

-- MS Access
UPDATE 
       ((Artikel a
          INNER JOIN Sachwert AS s on a.GUID = s.GUID)
          INNER JOIN Sgpos AS sgp on sgp.SACHGRUPPE = s.SACHGRUPPE)
          INNER JOIN Catalog c on c.Year = a.Year 
       SET s.Wert = '1', s.DATE = NOW()
       WHERE a.Artikel = '104905' AND sgp.SACHGRUPPE = 'V1'

Glogg, a Logfile Viewer

Glogg is a multi-platform GUI application to browse and search through long or complex log files, in short a logfile viewer. It is designed with programmers and system administrators in mind.

  • It is free to use and under GPL 3.0 or later.
  • Filter and search with regular expressions or wildcards.
  • Selectable font and font size.
  • The f key toggles the follow-mode, which behaves like tail -f.
  • Keyboard commands try to emulate the default bindings used by the classic Unix utilities vi and less.
  • Also standard keyboard bindings are supported. E.g. Ctrl-End goes to the end of the file.

Delete older files with Unix find and xargs

find . -type f -mtime +5  -print0 | xargs -0 rm
find . -type d -depth -print0  | xargs -0 rmdir  2>/dev/null

With these two bash commands, you can recursively delete all files older than 5 days and after that all now empty directories. I use these commands to clean up temp directories.

In the first line -mtime +5 finds all files that are older than 5 days. The + sign is important. Without it, only those being exactly 5 days (5 * 86400 seconds) old would be found.

In the second line -depth makes that child directories are treated before parents. That is important here. Otherwise a directory a which contained only an empty directory b would not be deleted.

rmdir only deletes empty directories. For that you don’t see rmdir's griping about non-empty ones, the 2>/dev/null is used.

Inkscape

Inkscape is a free, platform independent tool to work on vector graphics like svgs. Additionally it has got good commandline batch processing facilities which makes it the tool of choice for programmers.

See an example of how to crop or trim many svg files by commandline.

Inkscape and Eps

Inkscape on Windows cannot handle eps-files by default. To be able to handle eps-files, it needs Ghoststcript. A fine description of what is to do to install Ghostscript so that Inkscape can handle eps files.

To convert a lot of eps files to svg files at once, you can use tclkitsh and the following script:

set pathToInkscape "C:/Program Files/Inkscape/inkscape.exe"
set epsFiles [glob *.eps]

foreach f $epsFiles {
    set fbasename [file rootname [file tail $f]]
    if ![file exists $fbasename.svg] {
        puts  "$pathToInkscape  $f --export-plain-svg=$fbasename.svg"
        exec  $pathToInkscape $f --export-plain-svg=$fbasename.svg
    }
}

How?
1. Save the script as EpsToSvg.tcl.
2. Open the script in an editor and adapt the pathToInkscape. Save.
3. Open a command shell and cd to the directory where your eps files are located.
4. Then run tclkitsh EpsToSvg.tcl.