Trying to come up with a way to limit access to data in an Access file?
Posted on 2016-08-30
This feels like a very slippery slope, but am wondering if it's possible. I have an Access file that is linked to a live database. The live database is part of a web app that I own and manage. There are a couple thousand users of the web app that can only see their data. I was wondering if it's possible for me to create a duplicate Access file that includes all linked tables but only one query and limit access to only view the query. The query would have the criteria set to only show this one customers data. My preference would be that they not even be able to open the Access file. Then I would create an Excel file that is linked to the query. They could simply refresh the Excel file to get current data. If I required a password to open the Access file, I know I can save the password in Excel, but believe the password would not be secure there.
I know this situation is perfect for an API, but we are not ready to go down that road just yet. I'm looking for a quick, but secure, fix.