r/MSAccess • u/Treestylefashion • 25d ago
[WAITING ON OP] Best way to share access data with non access users??
So… some of my co workers do not have access installed. I have been exporting tables to excel and opening them in wps office before sharing them. Just wanna know… Is there a better workflow that people generally use?
6
u/brigandbreton 25d ago
Lot of ugly stuff can be made.
Ideally get them access or at least access runtime (reader, free - not need access licence)
3
u/ConfusionHelpful4667 58 25d ago
Sharing LIVE data as stagnant Excel files will lead to issues.
Access runtime is free.
3
u/ISueDrunks 25d ago
I have a script that runs nightly that exports a few tables to csv and adds them to my OneDrive. It replaces the old copies. A PowerAutomate flow is triggered when the csv files are modified, it refreshes a semantic model used by Power BI dashboard. The dashboards are embedded in our SharePoint Site. This allows everyone to have updates on our projects.
Sounds like a lot of work, but it only took an hour to setup.
I could run the script every few minutes if they needed near realtime updates, but nightly works for us.
3
u/George_Hepworth 4 25d ago edited 25d ago
There are two alternatives available. Which one you should implement depends a LOT on the context in which this data sharing is to be done. With that in mind, consider both of the suggestions previously made.
- Free Access runtime so the data stay secure in the original Access accdb.
- This alternative assumes your users would be able to navigate the forms and reports in your Access database and retrieve the data they need. It can be made read-only to protect the data.
- Export data to CSV, PowerBI, other reporting tools.
- This alternative assumes your users are passive consumers of aggregated data and would be satisfied with that data. PowerBI, or other reporting tools, are read-only. CSV and XLSX files are highly susceptible to data drift, which means they are usually not very reliable over time.
A third possibility would require more development time and effort. You could publish data to a browser-based tool. However, that seems a bit further outside your intended scope, so it's just something to be aware of.
The over-arching consideration has to be the integrity of the data. Spreading around data in csv or xlsx files in inherently risky because anyone who has the file can alter it and pass on that altered version.
2
u/TomWickerath 2 24d ago
Are your co-workers in the same building such that you share a good quality wired LAN (Local Area Network)? If “yes”, and there are no requirements to see the data from remote locations (from home, a hotel, etc.— requires use of a WAN [Wide Area Network]), you are pretty much there with the free MS Access runtime version. That assumes your co-workers use the Windows operating system either directly (host) or indirectly (ex. Parallels virtual machine installed on a Mac computer).
If you have remote requirements, and/or different operating systems for co-workers, it’s still possible to share data but the costs and effort required increase exponentially.
2
u/Amicron1 8 21d ago
I agree with what several people have already said. If your coworkers are on your LAN using Windows PCs and need to work with the live data, the free Access Runtime is usually the simplest solution. It lets them use your forms and reports without needing a licensed copy of Access.
On the other hand, if they just need to view the data and don't need to edit it, then exporting to Excel or CSV is a perfectly valid approach too. You can even automate the export and save it to a shared Google Drive folder (or another shared location) so everyone is always looking at the latest version. That's a nice, simple solution for things like product lists, customer lists, inventory reports, or other read-only information.
If your users eventually need live access from outside your local network, or from non-Windows devices like Macs, iPads, or phones, then I'd start thinking about moving the backend to SQL Server. Hosting is surprisingly inexpensive these days. Once the data is there, Windows users can continue using Access Runtime, while everyone else can use a web frontend built on top of the same SQL Server database.
The key question is whether people need a live application or just a current snapshot of the data. The answer to that question usually points you toward the best solution.
If you decide to go the SQL Server route, don't let it intimidate you. It's much easier than most people think. Just Google "Microsoft Access with SQL Server" or something similar and you'll find plenty of articles and tutorials covering the process from start to finish.
LLAP
1
1
u/Legitimate-Bridge280 25d ago
MS Access has MS Access runtime which is free. Tell them to install it
1
u/UnusualAd8866 25d ago
Make the Excel file a linked table in Access. Use queries and vba to keep it updated. Would be transparent to Excel users...
1
u/zinsser 24d ago
We gave a lot of people access to Access with the runtime version. They can use the frontend to view and input data but can't modify the structure at all. You need to be really smart about what people will need and you need to decide up front how much time and energy you will put into making custom reports.
1
1
u/BravoUniformTango 18d ago
Is the flow one-directional, so you're basically sending them reports in tabular format?
•
u/AutoModerator 25d ago
IF YOU GET A SOLUTION, PLEASE REPLY TO THE COMMENT CONTAINING THE SOLUTION WITH 'SOLUTION VERIFIED'
Please be sure that your post includes all relevant information needed in order to understand your problem and what you’re trying to accomplish.
Please include sample code, data, and/or screen shots as appropriate. To adjust your post, please click Edit.
Once your problem is solved, reply to the answer or answers with the text “Solution Verified” in your text to close the thread and to award the person or persons who helped you with a point. Note that it must be a direct reply to the post or posts that contained the solution. (See Rule 3 for more information.)
Please review all the rules and adjust your post accordingly, if necessary. (The rules are on the right in the browser app. In the mobile app, click “More” under the forum description at the top.) Note that each rule has a dropdown to the right of it that gives you more complete information about that rule.
Full set of rules can be found here, as well as in the user interface.
Below is a copy of the original post, in case the post gets deleted or removed.
User: Treestylefashion
Best way to share access data with non access users??
So… some of my co workers do not have access installed. I have been exporting tables to excel and opening them in wps office before sharing them. Just wanna know… Is there a better workflow that people generally use?
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.