I have an Microsoft Access application that has a "continous forms" subform. When accessing that form in my application I have discovered that SQL Server has "a lot" of locks (over 1000). [Found by doing a select 1000 records from master database view sys.dm_trans_locks].
The data source for the form is a Pass Through Query called qryProspect summary. It in turn just has the SQL "EXEC qryProspectSummary" to call a stored procedure which returns the required data.
the Stored Procedure "qryProspectSummary" is just a complex select statement - with some nested selects and unions and joins over several tables. Running it from SQL Server Management studio produces the correct results and doesn't lock any parts of the database.
I have set the datasource recordset type to "snapshot".
How can I prevent Access taking out all these locks - it effectively destroys any multi-user working for what is supposed to be just a data snapshot.
You may want to use a linked view to return the results for this snapshot. I have found in some cases that Access uses a view much better when dealing with large data sets from SQL Server. Is there any way you can use qryProspectSummary as a view? This is not guaranteed to work but is one option that I would try.