CODEWITHSUNDEEP
sql-serverms-access
akc42
akc42

Reputation: 5001

How do I prevent Access locking database

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.

Upvotes: 1

Views: 3528

Answers (2)

akc42
akc42

Reputation: 5001

I found the answer to my problem yesterday, but for some reason couldn't access this site. Instead of creating a passthrough query with the access to the stored procedure, I instead added the following code to the ON LOAD event of the form.

Dim cmd As ADODB.Command, rst As ADODB.Recordset
Set cmd = New ADODB.Command
cmd.ActiveConnection = getStrConn
cmd.CommandText = "qryProspectSummary"
cmd.CommandType = adCmdStoredProc
Set rst = cmd.Execute()
Set Me.Form.Recordset = rst
Set cmd = Nothing

getStrConn is a function which retries the database connection string (I have an admin feature to change databases on startup, so I can't just hardcode it). This solves the problem and I find that nothing out of the ordinary is now getting locked when I open the form.

It does have one downside, I was using Form.Requery, but that doesn't rerun the query - I have to re-execute the code above instead. But its a small price to pay

Upvotes: 1

HelloW
HelloW

Reputation: 1617

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.

Upvotes: 1

Related Questions