Export queries in excel, update some columns and pull those back into access. (1 Viewer)

Alt

Member
Local time
Today, 13:09
Joined
Sep 6, 2021
Messages
35
I know how to export a query in excel but how can I update specific columns (those modified in excel file) back to access table after?

What's the best way to do it?
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 10:09
Joined
Oct 29, 2018
Messages
21,358
One way is to link to the Excel file and use an UPDATE query to edit the Access table.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:09
Joined
Feb 19, 2002
Messages
42,971
how can I update specific columns (those modified in excel file) back to access table after?
Either Access is the "master" or Excel is the "master". Having two sources for updates to data is the road to unreliable data.

If you want to import updates from Excel, you CANNOT do this by linking to Excel and joining to the Access table and running an update query.

WHY? Because linked Excel tables are not updateable and that renders the join to the Access table not updateable.

Therefore, you need to import the Excel file into a temp table and then run an update query using the temp table rather than the linked table.

Importing data like this makes Access bloat so you might want to use a tempBE and import into that so you don't bloat your FE or BE. We can advise if you want to do that.
 

AngelSpeaks

Active member
Local time
Today, 12:09
Joined
Oct 21, 2021
Messages
406
Just read this yesterday from DB Guy. I'm going to try it.

 

isladogs

MVP / VIP
Local time
Today, 17:09
Joined
Jan 14, 2017
Messages
18,186
There is a way of updating Excel files directly from Access.
To do so, you need to create a query to the external Excel file and set IMEX=0
By doing this you can circumvent the read only property of linked Excel files
 

Alt

Member
Local time
Today, 13:09
Joined
Sep 6, 2021
Messages
35
I never do it but is it possible to sent those specific columns back to access using SQL?

I'm already oulling infos using SQL (several excel files each with its own SQL string and using ADO). I need to be 200% sure not to change any other columns (we're several users using it).

perhaps consider using what is called an upsert query which will look something like this:
Code:
UPDATE SourceTable AS S LEFT JOIN DestTable AS D ON D.ID=S.ID
SET D.field1 = S.field1, D.field2= S.field2, D.field3 = S.field3, D.ID = S.ID
 
Last edited:

Alt

Member
Local time
Today, 13:09
Joined
Sep 6, 2021
Messages
35
Either Access is the "master" or Excel is the "master". Having two sources for updates to data is the road to unreliable data.

If you want to import updates from Excel, you CANNOT do this by linking to Excel and joining to the Access table and running an update query.

WHY? Because linked Excel tables are not updateable and that renders the join to the Access table not updateable.

Therefore, you need to import the Excel file into a temp table and then run an update query using the temp table rather than the linked table.

Importing data like this makes Access bloat so you might want to use a tempBE and import into that so you don't bloat your FE or BE. We can advise if you want to do that.
Hi Pat, can you advise on how to do it please?

DL link for "Temporary Table" (from AngelSpeaks) is no longer working but I was able to find another link (see other post).
 
Last edited:

Users who are viewing this thread

Top Bottom