Shortcut to update values from all Field names from one table to another

[chromachem]

With help, the Upsert code below now works.


The structure of both tables is exactly the same.
We plan on using the upsert code for some tables that will have over one hundred compounds (PFOS, PFOA, ...)
Rather than go line by line, is there a way to set the equivalencies for each individual field?

Current Code that would end up having lots of lines:

UPDATE tblTemp 
LEFT JOIN dbo_tbl537 ON tblTemp.LabID = dbo_tbl537.[LabID] 
SET 
dbo_tbl537.LabID = tblTemp.[LabID], 
dbo_tbl537.[Date] = tblTemp.Date, 
dbo_tbl537.PFOS = tblTemp.PFOS,
dbo_tbl537.PFOA = tblTemp.PFOA,
dbo_tbl537.PFHxS = tblTemp.PFHxS,
dbo_tbl537.PFNA = tblTemp.PFNA;

What I want to do that doesn't work:

UPDATE tblTemp 
LEFT JOIN dbo_tbl537 ON tblTemp.LabID = dbo_tbl537.[LabID] 
SET 
dbo_tbl537.* = tblTemp.* ;

Thanks for looking.

[June7]

Must do the first syntax.

Alternative is to redesign db structure. Or complex VBA manipulating recordset.

[CJ_London]

Since the names are the same use the query qbe to drag all the field down - takes a second or so

edit: I think��

[June7]

Unfortunately, cannot drag into UPDATE TO cell.

[chromachem]

I've been away a couple of weeks. Sorry for not getting back sooner.

I will write a VB macro in Excel that generates the text for the query. That way we don't have to type out the entire query by hand.

[NoellaG]

Why Excel? You can write a function in Access itself to generate the SQL. Just loop through all fieldnames to generate the SQL string.

[chromachem]

Will do. I gotta wean myself off excel . I would have used excel because I already know how to do it.

TY!