I have various csv files that export daily with raw data. I link these files into an access database and then have select queries that connect spreadsheet dashboards to the database. Several users use these dashboards, however only one user can refresh their data at a given time. I am looking for a way for the users to call their queries from the database at the same time without getting an error.
I realize access and excel are not ideal, but it is all I have to work with in my environment. I know that access does not support the "with (NOLOCK)" that I would normally use in SQL, and I am looking for an alternative.
Users NEVER update data. They just pull it for reporting.
I use MS Query to call the individual select queries that contain the output from the database, and have tried using SELECT DISTINCT however I get errors when attempting this via this method. I am open to a new method. This process was originally set up for one or two users and it has grown to 15-20 users now trying to refresh at once.
Check your forms setting and the lock settings.
You have this for a form's property sheet:
So, while your import routine may well lock the external Excel file (it probably will), if you import the Excel into a staging table, then from that point on, you should not experience a locked table.
So, setting no locks is "similar" to the no locks in SQL Server.
Linked Excel sheets are read only, and they can't be updated. However, if your forms don't have table locks set, then multi-user operations should continue to work.
Of course you can't drop tables that are in use here.
However, you CAN drop "staging" tables if you running a split application.
So, if you have a split database, and each user has a copy of the "application front end" part, then again, most such locking operations will not matter.
So, is your application split? If not, it should be. And check the form's property sheets, as they should be set to no locks.
Apply both approaches (split database), and check the forms lock settings, and doing so should allow multiple users to run just fine.