November 12, 2014 at 11:17 am
I have now moved all our order system from an Access backend to SQL server
I use the front end from Access using forms to modify the data.
It is a multi user set up.
My problem is record locking
If two users have the same record / form open and one makes a change the other gets an error message "Write Conflict" save - copy- drop.
How can I lock the second instance of the form being opened so the second user has to wait until the first user closes the form before the second user can edit it.
With the Access backend this worked by using Record Locks and setting it to "Edited Records"
But this does not work with the SQL backend.
Any help ?
November 19, 2014 at 9:37 am
The problem may be elsewhere.
When you upsize an access db to sql server the boolean column is converted to a bit column. This bit column on sql server cannot have any null values if access is to able to lock and edit the record. Make any bit columns TRUE or FALSE, not NULL.
Also, open the table in design view in the Management studio, click your bit field, in the column properties, set the Allow Nulls property to NO, set Default Value or Binding property to 0.
Regards
Melt
November 19, 2014 at 9:55 am
Hi I do have 5 Bit Fields none have Null values all are either 0 or -1
Nulls are allowed but when I try to change to not allowed I get an error
like :The Save (Not Permitted) dialog box warns you that saving changes is not permitted because the changes you have made require the listed tables to be dropped and re-created
Thanks
November 19, 2014 at 9:59 am
I have a solution though
I have a table with each user stored password and security i.e. what options are available.
To this I have added a "record open field".
As each record has a unique Id
As any user opens any record then the "open fields" record is filled with the ID no.
So if when opening any record the ID is already listed then the record is locked by the form i.e. no edits and shows a message telling the person trying to edit the form/record who has the record open.
When closing all records it set the saved data to 0
Works a treat !!
Steve
November 19, 2014 at 3:09 pm
I think there may well be a more general solution that doesn't involve tracking who has what open. I presume you are using ODBC linked tables in your Access front-end, though you don't indicate that. If you add a "timestamp" field at the end of each of your SQL Server tables, and then relink the tables, you should no longer get the write conflict errors. This isn't documented well, but it's something we adopted over 10 years ago and has worked well for us.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
November 24, 2014 at 7:41 am
When I imported the Access tables
A Colum was added to each table called "SSMA_TimeStamp" it is a "timestamp" type with Allow nulls as un ticked.
but the problem was still there
Steve
November 24, 2014 at 8:58 am
You don't indicate what version of SQL Server you are using, but it appears it is a recent one - 2008R2, 2102 and 2014 have an option that you have to set to allow you to make table changes when there is existing data in the table. Open SQL Server Management Studio (SSMS) and choose the Tools / Options from the menu bar. That brings up a dialog box that has a tree control on the left side. Choose "Designers" and then remove the check from the "Prevent saving changes that require table re-creation" option. Since you have the timestamp field, the allowing nulls on the bit fields is almost certainly the issue with record locking.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
November 24, 2014 at 9:07 am
I am using SQL 2012 I will try the suggestion thank you.
I will try it tonight when no one is logged in
Steve
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply