Hey everyone! I'm managing over 120 Excel workbooks that pull data from some central sources, currently stored as .xlsx files. We've been experiencing issues that seem to stem from these files, so I'm transitioning to Microsoft Access for our data needs. However, I'm not sure if it will solve the problem entirely.
Here's the issue:
- Users access the master data files through Excel's "Get Data > From SharePoint" feature on workbooks hosted in SharePoint.
- Unfortunately, when they refresh the data, it's often pulling from a locally cached path (like: C:UsersusernameAppDataLocalMicrosoftWindowsINetCacheContent.MSO...).
- This leads to Excel sometimes pulling outdated data, even though the actual database has been updated, which is frustrating since each user has different temporary file paths.
Is there a more effective way to manage this situation without moving to SharePoint lists (due to large data volumes of 500k+ rows)? I'd prefer to keep the data connection settings for easier script modifications. However, I have a lot of pivot tables that complicate handling data sources.
We're also considering:
- Mapping a SharePoint library as a network drive (WebDAV)
- Hosting the Access database on a shared network path, but I'm uncertain how Excel would react there.
Any insights on how teams have tackled multi-user refresh issues with SharePoint, Excel, and Access would be greatly appreciated!
3 Answers
Honestly, you should think about moving to a more robust database solution like SQL Server or Dataverse instead of Access. You'll find it provides better refresh consistency and keeps you away from those annoying cached versions that cause data mismatches. Access just doesn't cut it for serious data management!
Honestly, if you're still using Excel for this, it's time to upgrade your approach. Excel is great for quick tasks but not for heavy data. Access isn't a true database either. Shake things up and get a real database system in place!
If you do go the SharePoint route, be cautious. Even with a proper database, if you place it on SharePoint, caching issues could still creep up. Make sure to explore options to disable caching in OneDrive and check if your Office settings allow that too. Consider a web database or an on-prem solution for more reliable access.
Amen! Too many users try to make do with Excel when there are far better options out there for handling large data sets effectively.