Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 21st, 2005, 09:47 PM
Registered User
Join Date: Jun 2005
Location: , , .
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.
Reply With Quote
  #2 (permalink)  
Old June 23rd, 2005, 06:25 PM
SQLScott's Avatar
Wrox Author
Join Date: Dec 2004
Location: Wellington, FL , USA.
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. :-)


Reply With Quote
  #3 (permalink)  
Old June 23rd, 2005, 06:52 PM
Registered User
Join Date: Jun 2005
Location: GuangZhou, , .
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

Reply With Quote
  #4 (permalink)  
Old September 7th, 2005, 08:05 PM
Friend of Wrox
Join Date: Aug 2004
Location: Orange County, CA, USA.
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).

Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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

All times are GMT -4. The time now is 02:26 AM.

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