0

I've been using SQL Server for 2+ years but I am new to MS Access. I have just started to look at MS Access as a potential front-end/portal to access SQL Server and I was talking with a colleague who said I should be wary of the potential for MS Access to lock an SQL table if my Forms aren't coded correctly. I've experienced table locks before and can appreciate how frustrating they can be. My colleague mentioned using something called a 'pass-through' to get over the problem. Can anyone please help me by explaining what is happening and how a 'pass-through' solves the problem?

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
PingPing
  • 899
  • 6
  • 15
  • 28
  • 3
    Any app using SQL Server will use locks to ensure proper operation. It only depends on how it does that... your colleague should be a bit more specific on what he means - and how he think an improper Access form could lock an entire table - SQL Server by default uses **row-level** locks - not table locks – marc_s Apr 23 '12 at 07:52
  • 1
    I never HAD the problem in years. PT queries are mostly usefull for performance & complexity issues, IMO. – iDevlop Apr 23 '12 at 07:52
  • http://discuss.joelonsoftware.com/default.asp?design.4.515190.14 – David Brabant Apr 23 '12 at 08:50
  • Thanks for the responses. I understand it now. My colleague was referring to the problem that arises with opening a large Linked Table in Access, eg. through a Form, with the resulting table scan locking others out. – PingPing Apr 23 '12 at 13:22

1 Answers1

0

Regarding SQL pass-through queries in MS Access please see the following link - it explains how to create such a query...although this is related to Access database rather MS-SQL... How to create SQL pass-through query in Ms Access

Apparently you can control Access locking mechanism (in 2013 version at least) by opening the Property sheet of the Form in Design mode, move to Data section and there will be one entry called "Record Locks" with 3 options as "no lock", "all records" and "edited record" (this sounds like a row-level lock). I have never tried them in action, but that option is probably dedicated to manage Access locks on linked tables.

Tamas
  • 39
  • 4