Data connection, refresh and upto 8 users

[JackDanIce]

Hi,

I have 7 input Excel xlsm files which use macro's to read and write data from an Access database.
Unsurprisingly, there are issues with data refresh and syn as 7 users read/write at similar times for month-end reporting purposes.

All triggers are from Excel, Access is never touched in normal use.
Data flow is:
Pull data from Access into Excel, based on specific parameters the User sends (i.e. each user pulls a different slice of data, however, some slices overlap each other
- i.e. manager owns a job but part of it is done by another manager's team, task is evaluating how much work is done, vs how much is owned by, across 7 managers

Two data sets are returned that are written to two separate data sheets
Each data sheet has a separate pivot table, which is refreshed and results printed out into 2 separate sheets for User
- 1 sheet is used for edits and writing data back to database (see below)
- Other sheet is for viewing only and changes not captured.

When User pushes data back to Access they are doing it from a defined interface (cross-tab view)
Excel, lifts the interface into a temp table in Access and triggers a macro int Access to loop over the table and transform it back into underlying Access tables.
It writes the whole data set back (i.e. including any stale data) which isn't fast.

One suggestion is to track ID value for each row changed in the edit view sheet and then only write those changes (i.e. smaller data set) back to Access
If data is refreshed, then using same list of tracked ID values, all data is overwritten except the Users locally made changes, until they update database with their own changes.

I've just started reading about Data Connection feature and it seems like I can use it to directly refresh pivot tables via Access with an auto-refresh feature. How does writing data back work, do I still use current macro code to push data out?

Has anyone dealt with a situation like this and can offer general comments or suggestions to avoid Users missing out on each other's data changes?
I am assuming using Data Connection will make some of my code unnecessary e.g. connecting to database.

TIA,
Jack

 

[Legacy 456155]

Does your front end in Excel allow users to delete or add records?

 

[JackDanIce]

Not entire records...

Each row (record in this case) contains forecast revenue figure by month and the user can edit or change these values.

When this interface is pushed to Access, I use a loop in Access to change monthly date and revenue figure to flatten it from months shown horizontally (Excel interface)