excel to update an access table

**scottiex** · 10-02-2023, 07:30 PM

Struggling a bit with getting some vba to update an access table.
I keep getting close but not close enough based on code designed to add records or and doing it in an inefficient way so probably better to get fresh advice.

the scenario is that I have an access table with some entries and a excel spread sheet with some entries. and I want to update the lines on the spread sheet to the data base if the ID exists or add them to a new line if they do not exist (leaving any other lines in the data base unchanged).

If I have a template (or even general instructions) for doing this I can probably work it out.

**Haluk** · 10-03-2023, 10:13 AM

If you can supply a sample Access file, we can try to see what we can do.

**ranman256** · 10-03-2023, 10:15 AM

Usually the access db pulls data from the XL sheets. Simple.

It's harder to do it backwards, xl to Access.

**Haluk** · 10-03-2023, 10:22 AM

No, its not.... you can use ADO to connect to DB from Excel and write, read, delete, update data.

**alansidman** · 10-03-2023, 10:38 AM

You can also link your Excel Table to Access. Then any updates to the Excel Table is automatically reflected in the Access Table.

**scottiex** · 10-04-2023, 11:35 PM

OK,

1) I zipped the data base so I could upload it.

2) I have linked the power query to the data base using cell B2 in the index sheet. So that path is wherever the access database is saved.

----

In reality there are two scenarios

i) The green table in the inwards sheet reads from the database. In this table there should only be lines if they exist in the database - if they are changed they should be updated.

ii) the main issue I was asking about - The blue one in the update sheet is for writing to the data base from a different source (a query from another database via an ODBC connection).
I didn't have the unique database ID (from access) so I used a xlookup based on so_order_no & so_bo_suffix & stock_code being unique.

So now the lines in this blue table may or may not exist in the database, if they don't I want to add them, if they do they should be updated (never deleting from the database).

----

The process

in practice it would run the blue table then update it to the access data base (so they have the latest info in the data base)
then run the green table (now they can see it)
then let them edit it and either a button or automatic update back to the access database. (now it is updated)

I tried myself but I think my code was a confusing mess. Happy to have any part of the solution here.