Script to release locks automatically

[paqguy]

I have a client who is using SQL Server as the backend to an Access database ... for some reason everytime a user opens a form to make changes, Access puts a lock on the table and does not release it ... preventing anybody else from making modifications to ANY records. I have yet to find a solution. All users are using the same front end, so I suggested making multiple copies of the front end, but they are hesitant to do that.

My question is, is it possible to write a script that runs every so often to remove all open locks? I know that could be dangerous, but for now, it's a possible work around.

dbageek - Shaun Beane
Instructor/Developer

 

[AlexCuse]

It's putting a lock on SQL Server tables or on Access tables? You may need to enable record-level locking.

While this lock is in place, can you modify tables through query analyzer?

You also would probably want to publish the front end through batch files (or if you are very lucky Citrix server. This has worked like a charm for me).

 

[mrdenny]

No there is no script you can run to release locks (unless you kill the client connection to the server).

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[paqguy]

It's a sql server lock. i haven't tried to modify tables through query analyzer ... good thought. I DO have record level locking enabled in Access.

I gave them a vb script to distribute the front end to the users ... this is all being run from a Terminal Server 2003. So I have the script creating a folder by username and copying the front end into that.

I just can't seem to find out why the locks are being created in the first place.

dbageek - Shaun Beane
Instructor/Developer

 

[mrdenny]

When ever any change is made in SQL Server (or data is viewed for that matter) a database lock is made at both the table level and at the page or row level.

When you use access via it's native database connectivity on the forms it likes to takes much larger locks than it actually needs to take.

Do the locks get released when the user closes the data entry/edit page?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[paqguy]

no unfortunately they don't (locks don't get released when user closes form)...

i assume if we were to completely reengineer the front end with unbound forms, and populate with recordsets that might work?

just not sure of the best way of setting it up ... haven't had too much experience with Access as the front end to a sql database.

dbageek - Shaun Beane
Instructor/Developer

 

[AlexCuse]

Recordsets could work.

Did this application function properly in the past or is it new?

What I do in most of my applications is create parallel tables in access, then use these for any data entry/manipulation and get the data to SQL using update/append queries. You do need to have an automatically assigned identifier on your SQL table for this to work though, as well a means for users to identify their desired records and bring them to access for updates.

Be sure to post your solution, whatever it may be, as I am almost certain I will encounter this problem at some point.

Best of Luck,

Alex

 

[paqguy]

The database orginated completely in Access. They outgrew Access as the size of the mdb was approaching 2 gig very fast. So they imported all the tables to SQL Server using the DTS Wizard, then linked to the tables in Access. That was pretty much all they did. The access guru then changed how the forms were being populated (just found this out) ... the main form was based on a query which only opened one record (based on a search field on the switchboard). So, the form is not really bound to the entire table ... however, as soon as ANY user makes an edit to a record in this form, the whole table is locked, and remains locked until you KILL the sql connection ... closing access does not release the lock.

Just plain weird. I'm starting to think this has to have something to do with Terminal services, but I'm really not positive.

I will certainly post a solution when I find it.

dbageek - Shaun Beane
Instructor/Developer

 

[mrdenny]

i assume if we were to completely reengineer the front end with unbound forms, and populate with recordsets that might work?

Yep, that would be my recomendation.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com