Updating a Microsoft Access 2013 table with data from a pass-through query

Question

I am trying, unsuccessfully so far, to update records in a Microsoft Access 2013 table (called tbl_Data) with data from an AS400 table (LIBRARY.TABLE).

As you can see in my Access 2013 pass-through query below, I am trying to join the access table with the AS400 table using the Prefix & Number fields, and from there, update the access table with Name & Address information from the AS400 table.

Here is my latest attempt:

 UPDATE 
      tbl_Data
 SET
      tbl_Data.FirstName = a.NINMFR,
      tbl_Data.MiddleName = a.NINMMD,
      tbl_Data.LastName = a.NINAML,
      tbl_Data.BuildingNumber = a.NIBLNR,
      tbl_Data.StreetName = a.NISTNM,
      tbl_Data.AptSuite = a."NIAPT#",
      tbl_Data.Address2 = a.NIADR2,
      tbl_Data.City = a.NICITY,
      tbl_Data.State = a.NISTAT,
      tbl_Data.ZipCode = a.NIZIPC
 INNER JOIN
      LIBRARY.TABLE a
 ON 
      tbl_Data.Prefix = a.NIPRFX,
      tbl_Data.Number = a.NIPLNR;

When I run this query, I get an error that says:

 OBDC--call failed.
 [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0199 - Keyword INNER not expected. Valid tokens: USE SKIP WAIT WITH WHERE. (#-199)

I would really appreciate any assistance, as I'm out of ideas.

Thanks!

Answer 1

That is Microsoft specific syntax for an update, it does not work on DB2. Try this:

UPDATE 
    tbl_Data
SET
    (tbl_Data.FirstName,
    tbl_Data.MiddleName,
    tbl_Data.LastName,
    tbl_Data.BuildingNumber,
    tbl_Data.StreetName,
    tbl_Data.AptSuite,
    tbl_Data.Address2,
    tbl_Data.City,
    tbl_Data.State,
    tbl_Data.ZipCode)
    =
    (SELECT 
        a.NINMFR,
        a.NINMMD,
        a.NINAML,
        a.NIBLNR,
        a.NISTNM,
        a."NIAPT#",
        a.NIADR2,
        a.NICITY,
        a.NISTAT,
        a.NIZIPC
    FROM 
        library.table a
    WHERE
        tbl_Data.Prefix = a.NIPRFX,
        tbl_Data.Number = a.NIPLNR)
WHERE
    EXISTS (
        SELECT *
        FROM 
            library.table a
        WHERE
            tbl_Data.Prefix = a.NIPRFX,
            tbl_Data.Number = a.NIPLNR);