Likely cause of Access Error? -- attempting to update Table A from corresponding Table B value

Factual Questions
1

I’m not an Access geek. I’m a FileMaker geek and a sort of middle-school-level SQL geek, therefore with some good fundamentals for understanding databases in general, but definitely not Access-specific stuff.

I fetched the values for four columns’ worth of values using an outside-of-Access technique, and placed the results in a CSV (comma-separated-values) file in a folder.

Figured out – eventually – how to import the CSV file as a new table in Access, by typing “Import Text File” in a prompt area that said “What do you want to do?” and walking through the import wizard thingie. Technically I went with “Link to the data source by creating a linked table” so the data itself isn’t really IN the Access db.

Now I’m stuck, trying to tell Access to update the existing records in a table called “Programs” with the values that are in the CSV file.

Name of source table: GoatOutPrograms

Names of fields to be updated in Programs: Latitude, Longitude, XCoordinate, YCoordinate

Names of source fields in GoatOutPrograms to populate the above with: Latitudex, Longitudex, XCoordinatex, YCoordinatex

Names of fields that define the relationship between Programs and GoatOutPrograms:

Programs.Program_ID = GoatOutPrograms.Program_IDx

SQL statement I’ve cobbled together that isn’t working:

UPDATE Programs INNER JOIN GoatOutPrograms ON Programs.Program_ID = GoatOutPrograms.Program_IDx SET Programs.Latitude = GoatOutPrograms.Latitudex, Programs.Longitude = GoatOutPrograms.Longitudex, Programs.XCoordinate = GoatOutPrograms.XCoordinatex, Programs.YCoordinate = YCoordinatex
WHERE (((Programs.Latitude) Is Null) AND ((Programs.Program_ID)=[GoatOutPrograms].[Program_IDx]));

The cobbling-together process was a combination of freehand typing and then dragging a connector in the graphic-object display of the Access query when I saw that my first attempt had not associated Program_ID with Program_IDx properly – and Access created the “INNER JOIN GoatOutPrograms ON Programs.Program_ID = GoatOutPrograms.Program_IDx” clause. Access also added the raft of parentheses and square brackets that I did not type.

It “runs” but after it has run the rows in Programs do not have their four columns updated.

[rant]
God I hate Access. This would take 30 seconds if I could the tools I know how to use. I suppose my lack of familiarity with Access isn’t a failure on its part but I have to say it’s one of the most user-hostile environments I’ve encountered.
[/rant]

2

Try this (apologies if there are typos or it doesn’t work immediately; I don’t have a database engine on this computer):

UPDATE Programs 
SET Latitude = GOP.Latitudex, 
    Longitude = GOP.Longitudex,
    XCoordinate = GOP.XCoordinatex,
    YCoordinate = GOP.YCoordinatex 
FROM (
    SELECT Program_IDx, Latitudex, Longitudex, XCoordinatex, YCoordinatex
    FROM GoatOutPrograms) AS GOP
WHERE 
    GOP.Program_IDx = Programs.Program_ID
    AND Programs.Latitude IS NULL

I like the `UPDATE … SET … FROM (SELECT…) pattern in this case.

3

I plugged your query directly into ChatGPT, and it came up with this:

UPDATE Programs
SET Latitude = GoatOutPrograms.Latitudex,
    Longitude = GoatOutPrograms.Longitudex,
    XCoordinate = GoatOutPrograms.XCoordinatex,
    YCoordinate = GoatOutPrograms.YCoordinatex
FROM Programs
INNER JOIN GoatOutPrograms ON Programs.Program_ID = GoatOutPrograms.Program_IDx;

Pretty similar to hostility’s version, aside from the lack of null check. I’d posit that’s probably not necessary; it just means there will be some redundant writes to those rows. It uses “FROM Programs” instead of “FROM GoatOutPrograms”, but the inner join means it’ll only update the intersection of the two anyway.

4

I’ll try them tomorrow!

5

I admire your hardcore attitude, but Access really wants you to do a make-table query (if you want results in a new table), or an update query (add records to an existing table.

So, start a “normal” Select query and get the records you want from your GoatOutPrograms table, then change the Select query to a make or update query and Access will ask what table you want to pump the query results into.

6

I recognize all of those words as components of the English language, but I haven’t the vaguest idea what you just said.

7

MS Access complains as follows: Syntax error (missing operator) in query expression ‘FROM (
SELECT Program_IDx, Latitudex, Longitudex, XCoordinatex, YCoordinatex
FROM GoatOutPrograms) AS GOP’

8

Access doesn’t care for that one either:

Syntax error (missing operator) in query expression ‘GoatOutPrograms.Ycoordinatex FROM Programs
INNER JOIN GoatOutPrograms ON Programs.Program_ID = GoatOutPrograms.Program_IDx’

9

I gather that you think I’m trying something unduly complex and sophisticated and futzy. Every goddam thing I ever try to do in Access is unduly complex and futzy, and shouldn’t be.

I don’t know what you mean by “get the records you want from your GoatOutPrograms table”. You mean do a Find for the ones that contain data that I want to use? That would be all of them.

I don’t know what you mean by “change the Select query to a make or update query” – I started with an UPDATE query!

I don’t know what you mean by “pump the query result into” — I mean, it’s a brilliantly evocative image and get the sense of it immediately, but it doesn’t translate into a syntax or button-push or menu item or any other verb that, click for click and keystroke for keystroke, I know how to do any better than I know how to “import matching values from external spreadsheet”.

10

nm nm nm

11

You’re not using an ancient version of Access are you?

Using a SubQuery in the FROM clause was not possible until Access 2000 version.

12

Sorry, it should work in other relational database engines (barring a typo or errant parenthesis). It has been a while since I specifically used Access. I could’ve sworn the subquery would work in Access.

How about trying this (it’s pretty close to your original):

UPDATE Programs
INNER JOIN GoatOutPrograms GOP on GOP.Program_IDx = Programs.Program_ID 
SET Latitude = GOP.Latitudex, 
    Longitude = GOP.Longitudex,
    XCoordinate = GOP.XCoordinatex,
    YCoordinate = GOP.YCoordinatex 
WHERE Programs.Latitude IS NULL
13

hostility’s latest — compiles, runs, but doesn’t update the target table. Don’t know what the heck it’s doing instead, or thinks it’s doing instead.

I don’t suppose I can just delete the entire contents of the table, then import a version that contains all the records with the inserted values, and have it accept the import of Program_ID (which it normally assigns itself) so that existing relationships to Program_ID in other tables doesn’t break?

14

Hmm… I could have FileMaker calculate the SQL to set the values of Latitude, Longitude, XCoordinate, YCoordinate, export all the commands as a text file, copy, and paste somewhere… I’ve done that to update SQL, never tried it for Access.

15

Hmm, it should definitely work. Do you have any lists, forms, views, queries, etc. open that reference the Programs table? Does it give you the warning dialogs (“You are about to update…”)? Any possibility of a permissions problem on the Programs table? Are you logged into Access with the default “Admin” account (if they still use that)?

16

I can have FileMaker create and export lines of commands like so (this is from a previous project, and was executed in Microsoft SQL, not Access):

update badupct.nalox_nonsep2018 set num_nalox=1, whynalox_work=0, nalox_given =1 where pk_id = 81762;
update badupct.nalox_nonsep2018 set num_nalox=2, whynalox_work=0, nalox_given =1 where pk_id = 81763;
update badupct.nalox_nonsep2018 set num_nalox=1, whynalox_work=1, nalox_given =1 where pk_id = 81764;
{etc}

Can I paste something like that in as an Access query and execute it, or will it barf on being asked to execute a bunch of consecutive commands? I know it isn’t the same as MS SQL.

17

Gave it a tryout.

UPDATE Programs  SET Latitude=40.708675, Longitude=-73.818500, XCoordinate='1034570', YCoordinate='0197518' WHERE Program_ID=2;

Compiles, runs, no errors…doesn’t update the freaking row in Programs where Program_ID=2 :frowning:

18

I doubt Access will let you do that (sequential queries).

Are you sure you have NULL values in the Latitude column of the Programs records you want to update?

19

If I understand correctly, you imported the CSV but as a linked table? Is there a reason you don’t want to load that data into Access. Typically a linked table is used where you have an active external table in something like SQL Server or MySQL. I haven’t used linked tables for text files, and I wonder if Access isn’t handling that well.

20

Oops, just saw your reply above after I submitted my last reply.

Well, the queries are correct (both mine and yours above), so perhaps there’s something about the data type of the Programs columns (Latitude, Longitude, etc.) that’s keeping Access from updating them with the values supplied by your query. Have you compared your two tables’ column definitions side-by-side?