| Author |
Topic |
|
Gearcam
Starting Member
6 Posts |
 Posted - 2014-11-12 : 11:45:39
|
| I have now moved all our order system from an Access backend to SQL serverI use the front end from Access using forms to modify the data.It is a multi user set up.My problem is record lockingIf 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 ? |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-11-12 : 11:49:54
|
| SQL Server does it with transactions.So your app has to work like this:1. get the data, fill in the form, wait for the user to submit changes2. begin a transaction, confirm that the old data is unchanged since step 1, update the data, commit the transaction2a. if the data HAS changed, issue the "Write Conflict" message. |
 |
|
|
Gearcam
Starting Member
6 Posts |
Posted - 2014-11-12 : 12:10:31
|
| thank you for the infoSo on two pc's I open the same form the data populates automaticallyon PC A I make a change move to another field then move to a different recordPC B no change same old data displayedOn PC B I move away from the record then the error is thrown "Write Conflict" if I select "Drop" the data from PC A is updated How can I implement what you suggest ? |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-11-12 : 12:34:13
|
| What part of the implementation do you need help with? We can show you how to use transactions (or you can easily learn it -- it's not rocket science!). However, this is not the forum for designing MS Access applications, if that's what you're looking for. |
|
|
|
Gearcam
Starting Member
6 Posts |
Posted - 2014-11-12 : 13:12:07
|
| Hi its just the transactions I need to learn to use I have never used them as Access has always worked fine.Is there a example or can you help me with that.I have a full blown management system which runs great except for this locking issue now since the SQL changethanks |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-11-12 : 13:32:11
|
For filling in the form, a simple SELECT will do. For updating the form, something like (to update an existing row): -- pass columns to check as parametersbegin transelect ... -- same as before with added WHERE clauseWHERE @parm1 = col1, -- first column to check AND @parm2 = col2, -- second column to check ... etc ...IF @@ROWCOUNT = 0 -- means no matching rows found ==> something changed since first select THROW 50000,'Write Conflict', 1;-- update the rowCOMMIT |
|
|
|
Gearcam
Starting Member
6 Posts |
Posted - 2014-11-12 : 14:05:02
|
| hi when the form opens as a sub form the source is likeSELECT TOP 10000 * FROM Orders Query WHERE [Job No] =something Order By ID DESCThe on the event On Current their is a lot of update code and checks etcI do not have any button etc to save the form data just close the form |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-11-12 : 14:22:32
|
| OK -- I'd change the * in your query to a specific column list. It's a best practice. Also I guess your form is not handling one row at a time. Is that true? In that case you'll need a different strategy. You might want to write new/updated rows to a separate table then merge those changes back into the main table when the form closes. If you set a unique id per form, you can insert that into the temp table along with the data they use that to match rows for the current form during update along with matching column values (to see if anything changed in the meantime).It sounds like your design needs a thorough thinking-through! |
|
|
|
Gearcam
Starting Member
6 Posts |
Posted - 2014-11-12 : 14:38:29
|
| The main form has a load of search fields so you can search for records that meet a number of parameters so the * is changed anywaythe form shows all the fields in one record at a time that meat the search parameters then you can step through the records on the form hence the current event.this was so easy with access backend on the form just had to set record locks set to edited records and it worksIn the form as it loads I just try to set a fields with the users name if it sets then the record is not already open so set allow edits to true if it can not set user name then set allow edits to false to stop editing |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-11-12 : 14:41:49
|
| OK -- so you only update one row at a time. Then the approach I posted at 13:32 should do it for you. |
|
|
|
Gearcam
Starting Member
6 Posts |
Posted - 2014-11-12 : 14:59:42
|
| Ok ill see how it goesSo this code goes in the "Current" Area ?Then reload the data direct into param from the source data base using the where clause to only look at the exact record?So if nothing found the form and the database are different ?Not as before with just locking the recordIs there no setting or something in SQL that can do this ie only have each record open once for editing and any others for read only ? |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-11-12 : 15:11:03
|
| SQL does lock rows (and lots of other things!) but only keeps the locks for the duration of a transaction. Believe me, you don't want to begin a transaction at the start of form and commit it when the form closes. If the user goes out to lunch or leaves it open at end of day...well you get the picture! Anyway, you've got the basic idea down. Populate the form with a simple select. At update time, begin a transaction and first compare the rows with the form data before changes were made. If no match, then someone else updated that row. If they match, do the update. Finally issue the COMMIT command to end the transaction and free any locked resources. |
|
|
|
|
Access Front End SQL2012 record lock