Update existing Access tables from Excel using VBA in Access

[Cudaboy]

Hey All!

I have an Access 2016 database that I want to use VBA in the database so when it opens it updates the table data with data in an excel spreadsheet in a different network folder. this has to be automated for the user. let's just start with a table named "Summary" and I can go from there. I can append OK, but cannot update. No code has been written as yet so I am starting from scratch. Thanks in advance!

 

[alansidman]

Instead of using VBA, why not just link your Excel Table to Access. In this manner, everytime your table is updated, it automatically syncs with Access.

https://support.office.com/en-us/ar...workbook-a1952878-7c58-47b1-893d-e084913cc958

 

[Cudaboy]

Short answer – because I need to update the tables from aspreadsheet to keep the data synced between two databases.
I’ll try to condense this:
User has a stand-alone copy on a laptop. Data is updated while in a non-networkedarea. The same user has anotherstand-alone copy in a networked folder at their desk. Both copies will have data updated by the singleuser and must remain synced even after the user changes/adds data while in thenon-networked area. I can get bothcopies to write to Excel and overwrite the existing sheets with the latest data,but they must both also update their tables using the spreadsheets so they stayin sync with the data changes.
It is the updating of the tables via the spreadsheets that Ineed to code.
If anyone can think of a better way, I will absolutelyconsider it. Thanks again!

 

[Cudaboy]

Well, it's been over a week with no more replies. I'll work this on my own, but will still appreciate any help or experience that can be shared. Thanks!

 

[alansidman]

I'm thinking that you did not understand my solution for you. Here is a quick video that will further explain what I meant. You can link the table in Excel to two different databases if necessary. Look here---->https://www.youtube.com/watch?v=pezedEvncn0

 

[Cudaboy]

Thanks Alan, I'll give it a look.

Until then I'm going to try having the user click a button which will simply copy the back-end copy on the laptop to the network location. That is what will happen 90% of the time so I will start there and worry about syncing the other direction later.

Glenn

 

[Cudaboy]

OK, that was interesting and I will give it a try, but there may be an issue with the auto-numbered primary keys. I'll repost when I have some results.

 

[Cudaboy]

The Excel method still does not solve the problem of a user changing the data via the networked database and the data getting updated to the laptop's local copy of the database. Let me try to break this down again:

The laptop is off-line and records are updated/added twice a month.
Once done, the updates and additions need to be copied up to the networked back-end. This is done when the laptop is brough back on-line.
The user opens a networked copy of the databse and makes more changes/additions. The laptop is turned off at this time.
The laptop is used again offline in two weeks and needs to have the data be current. This is the problem - how can I ensure the data is current? The laptop was not turned on when the networked copy was used and may not be turned on until it is needed off-line again. Really starting to sound impossible, so I am going to make the networked copy read-only so any changes/additions must be made on the laptop, then have an "Update" button to copy the back-end to the network when the lap.

 

[Cudaboy]

This is the outline of my solution, code will follow when I have it tested! (hope it helps someone else)

1) On the laptop, add a button for the user to click to "Sync Data"
2) The code will first check to see if it can get to the network. Since it may throw an error, use an error handler to alert the user there is no connection. Otherwise the code may hang for a while, then throw a MS-style incomprehensible error (at least to the user!)
3) If there IS a connection, get the date/time stamps of the two back-end (_be) files. There is one back-end on the laptop in a folder on the C: drive, and one back-end file in a networked folder.
4) Determine which is the latest Date/Time and use an If-Then-Else to copy the most recent Date/Time file to the other folder, replacing the older file.
5) Alert the user the data sync completed successfully.

This requires the user to click the

"Sync Data" ​

button after they have made data changes on the laptop. I have also disabled all the data fields on the networked copy of the database, effectively making it Read-Only. The user can still use all the reporting on the networked copy, so they can work at their desk and connect to other systems that need the report outputs, etc. BUT they MUST use the laptop to enter/change any data and then click the "Sync Data" button when done.

Not the most elegant solution, but one I believe will work and still be relatively easy for the user.

 

[xenou]

This will work for a very small number of users (I guess - its awkward but do-able).

It sounds broken if there are multiple users who might work simultaneously. I.e.:
User1 gets the latest copy and starts doing work.
User 2 gets the latest copy and starts doing work.
User1 syncs to write back changes.
User2 syncs to write back changes.

The problem is that User1's changes are completely lost now, as they have all been overwritten by User2's changes.