Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old June 21st, 2005, 09:47 PM
Registered User
Join Date: Jun 2005
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL Server Database Locking with Access

I'm sorry if this is a repost, I ran a quick search and didn't really find what I needed. I recently upsized an Access database to an SQL Server. I can access the database fine on multiple computers but there seems to be no locking system in place. Whenever I access the same table from 2 different client computers, they can each edit the form simultaneously which of course leads to errors. I connect to the database through an Access project. I need to keep using Access and was wondering if anyone could tell me how to implement or activate (if that's the case) a locking system that will prevent simultaneous editing. Thanks in advance.
Old June 23rd, 2005, 06:25 PM
SQLScott's Avatar
Wrox Author
Join Date: Dec 2004
Posts: 338
Thanks: 0
Thanked 2 Times in 2 Posts

SQL Server locking is much different than Access locking. SQL Server can apply locks at many different levels; row, pages, keys, indexes, tables, and even databases.

You can configure these, and your best bet is to look in the SQL Server Book Online (BOL) and read up on locking. Otherwise, this would be a really long post. :-)


Old June 23rd, 2005, 06:52 PM
Registered User
Join Date: Jun 2005
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts

Check your app after INSERT,UPDATE,DELETE or using COMMIT;
IF not using,You will lock the DB or TABLE or COL until this db system timeout

Old September 7th, 2005, 08:05 PM
Friend of Wrox
Join Date: Aug 2004
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts

I think part of the problem is what the definition of "lock" is. It is different in SQL than for FoxPro/Access/Xbase. What the requester appears to want to do is what Oracle calls a "select for update", this feature does not exist in MS-SQL. But you can design it into the schema/application. What they appear to want is if user1 is viewing and editing a record #100, the developer wants to block some other user from retrieving for update the same #100 record. It is not related to field or row or table level locking in the SQL sence. It is to flag the row as someone is editing this row don't touch it till they save their changes (or do a sql update).

Similar Threads
Thread Thread Starter Forum Replies Last Post
Locking in Access DB linked to SQL Server backend gibbers Access 3 April 24th, 2007 03:20 PM
Access to SQL Server locking problem echovue Access VBA 2 December 22nd, 2006 08:10 AM
Migrating Access database to SQL Server lryckman SQL Server 2000 3 April 10th, 2005 02:19 PM
exporting access database to sql server Sheena SQL Server 2000 2 December 29th, 2004 04:06 AM
exporting sql server database to access pankaj_daga SQL Server 2000 4 October 31st, 2003 12:37 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.