Data connection, refresh and upto 8 users

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,691
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
L

Legacy 456155

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

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,691
Office Version
  1. 365
Platform
  1. Windows
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,113,744
Messages
5,543,955
Members
410,586
Latest member
acadavid86
Top