I am using Access 2013 and Sql Server 2012. I have upsized the access application to Sql Server using linked tables. I have an Access query (not on SQL server) that provides a result from about 4 tables. I then have this displayed in a bound layout/table view where each row corresponds to a row from the result query.
In the layout view, the user can edit the data in any row. Once the user does an edit, apparently Access opens a transaction and keeps it open. As long as the user is in the editable layout view, the tables that were part of the query are locked. If another user on a different computer is using Access, then they are unable to edit any of the tables (through any method, not just the same layout view). The 2nd user will get a 30 second pause in their application, and then finally will get error...
ODBC-update on a linked table 'TableName' failed.
[Microsoft][ODBC Sql Server DRiver]Query timeout expired (#0)
Once the first user exits the layout view, then all is opened up again for other users to edit.
Is there any way to control the transaction? Maybe just have it update the one row, and then release the transaction.
May have to change the data source for the layout view to be a SQL Server Sproc or View and not allow edits in the table. Instead, if the user wants to change something in a row, then click to bring up an edit form. Looking for other options.
You have a few possible solutions.
One solution is to open the form in question with a where clause to ONLY thus open the one row, not many rows.
The above is only practical suggestion if the form in question is NOT a continues form.
So you go:
strInvoice = inputbox("What invoice to work on")
docmd.Openform "frmInvoice",,,"[invoiceNum] = " & strInvoice
So limiting the form to the one row will fix this assuming there is an index on the invoice column. Also such a design tends to be more user friendly. I explain this important search concept here:
http://www.kallal.ca/Search/index.html
Another way to fix this issue is to FORCE fill the form with all records (this is really a bad idea from a performance point of view, and even without SQL server launching a form WITHOUT ANY kind of where clause as I show above is a bad idea from a user point of view, and also from a performance point of view)..
The reason why the table lock often occurs is the form starts pulling data from SQL server but then Access says HEY WAIT I have enough data – but the queries that started on SQL server already fired off some table locks and ASSUMED that all rows would be returned to the client (so the client halting the flow of records is what really causes your locks). What you can do to prevent this issue is thus execute a move last to pull all records, and thus that will eliminate (release) the table locks.
So in the forms on-load event, you can go:
me.recordset.MoveLast
me.recordset.MoveFirst
As noted the problem here is that pulling all records into a form is a VERY bad design in the first place.
Last by not least:
Another way to eliminate the table lock is to build the query as a view SQL server side and include the NOLOCK hint. You then setup a link to the view, and based the form NOT on a local query, but that of the view. Since the view has a NOLOCK hint, then you don’t need the movelast/movefirst suggestion as per above.
So out of the several solutions here, opening the form to ONE record is really the recommended solution. I mean when you walk up to an instant teller machine, it does not download EVERY account and THEN ask you what account to work on. When you use a search engine such as google, then it does not download the WHOLE internet and THEN ask you what to search for. Even an old lady at the bus stop can figure this out, let alone someone writing software!
So when you design + build a form in Access, it makes VERY little sense to download all records from the table into the form and THEN let the user search. So the user needs to be prompted for what to work on BEFORE the form is loaded, and if you use a where clause when opening the form EVEN if the form is bound to a large linked table to SQL server ONLY the record(s) that match the where clause will be pulled down into that form.
If you don’t use the where clause, then as noted, the locking issue will rear its ugly head.
As a temp fix, try the movelast/movefirst in the forms on-load, as that should fix the locking issue. However for longer term, I would suggest using a where clause, or consider the view idea.
Note that if you have ANY combo box based on any of the 4 tables, then AGAIN will you find locking issues since the combo box will request the data (and SQL server places a table lock during that request). And AGAIN Access will THEN tell sql server to please stop loading up the combo box since all rows are NOT yet required (but too late, as the table lock has occurred).
So if you have ANY combo box in that form based on any of the tables used, then again you find table locks occur. In these cases, I would again suggest basing the combo boxes on either pass-through quires with the NO LOCK hint, or on views with again the no-lock hint.
So check the form for any combo box based on any of those tables – they will cause table locks also.