?
Solved

[urgent] Access Frontend/SQL backend, record locking system idea

Posted on 2005-04-07
14
Medium Priority
?
348 Views
Last Modified: 2012-08-13
I moved from Access to SQL server, and of course, the first thing that went sour was my record locking.

I have to anticipate human error, and there already have been multiple instances where multiple people would both have the (rather complex) form open for the same person, and one would lose their work.

I have a solid idea of how to accomplish a solution for this, but would like the opinion of folks here, as well as some code help, if at all possible.

When someone opens the form in question, frmminirec, connected to tblVisit, every field and subform on the form should be disabled and read-only. if someone goes to a record and wants to edit it, they must click the "secure ownersip" button. When this button is clicked, it will run through the following IF/THEN scenario (not code, just the logic)

Open Table LockLog
Filter LockLog.VisitID for Current tblVisit.VisitID
IF rowcount > 0
     IF winlogin = LockLog.User
           enable all fields for edit.
           goto end
     endIF
THEN display popup stating [LockLog].user locked this record on [LockLog].DateTime
goto end
ELSE make new record in [LockLog], writing tblVisit.VisitID to LockLog.VisitID
enable all fields for edit.
end

Also, when they are finished editing the record, they must click on the "Release Ownership" button. Doing so should lock all the fields and subforms from data entry (form-side), and delete the lock record from LockLog.

Does this sound like a feasible approach? Have you done similar things before? If it is, I need help with the code, as I am very unfamiliar with concepts such as writing new records to tables via VBA, and performing filters.

Suggestions are appreciated. I wish I could offer more points, but if someone can help me make this work, I will award an A, so please help!
0
Comment
Question by:kkamm
  • 8
  • 2
  • 2
  • +1
14 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 13733248
SQL Servers locking mechanisms are a lot more more sophisticated than MS Access.

I would be hesitant to implement locking through code algorithms.

I believe you can open a recordset with 'Pessimistic' locking. That will lock everyone except the current user out  of the record. It won't give them as nice a message though.

That way SQL Server takes care of the locking.


0
 
LVL 1

Author Comment

by:kkamm
ID: 13733661
well, in reality, I am avoiding changing how SQL locks and manages data AT ALL in my (suggested) method, and am putting full responsibility in the hands of the Client App. I know that for some people, this may not be the best solution, but it seems the best way to keep SQL and access happy that has been presented thus far.

I was told in no uncertain terms that there was no way to emulate how access locks a record in an mdb through SQL, so I am seeking a solution to counter this. The one ive propsed would be wonderful, if I could get it to work.

So I guess it comes down to two questions:

1) Can you get access to lock records in SQL in the same fashion that it does with MDBs?

and if not, then

2) How do I go about making my thought tree come to life? This seems to be a VERY common problem with people upsizing, and there isnt a solution on here that matches the need.... please help :(.
0
 
LVL 11

Expert Comment

by:Jokra_the_Barbarian
ID: 13733938
A couple of questions for you:
1. Are your forms bound or unbound?
2. Access mdb or adp?
3. If bound, are they bound to stored procedures?
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 1

Author Comment

by:kkamm
ID: 13734284
Jokra:

1) Bound to queries, stored on the access app, which utilize the ODBC linked SQL data
2) mdb.
3) See 1.

thanks for your help.
0
 
LVL 11

Expert Comment

by:Jokra_the_Barbarian
ID: 13734872
I would use pessimistic locking as recommended by nmcdermaid. Here's a good article on how to create a bound found based on an ado recordset:
   http://support.microsoft.com/default.aspx?scid=kb;en-us;281998
0
 
LVL 1

Author Comment

by:kkamm
ID: 13735297
Well, having looked over what that will offer, I have to say that I think I will prefer the method I described up on the top.

(call me a heathen if you will, but having EVERYTHING read-only, then claiming permission to edit a record seems to be the ideal solution for my practice).

Now, as to the concept listed above... how would one go about writing that?


What it needs to do is the following:

1) when a button is pressed, check to see if the tblvisit IDENTITY field has been written to to a row in the tblLock

2) if not, then create a record which has the windows login name, the visit ID, and a date/time, then enable all the fields on the report, and disable the navigational buttons

3) if so, check the windows login name to see if the record was previously opened by the same person. if so, simply enable the fields on the report, and disable the navigational buttons.

4) if the windows login is for someone else, bring up a pop-up which says that (winlogin) Locked this record at (Date/Time) with an OK box to close. Make no changes to enable/disable fields, leave things as is.

5) when the commit button is clicked, delete the record from tblLock which is effectively locking the DB.


I just don't know how to code this.

If this goes beyond what can be offered here, I would not be opposed to making it a contracted job, as long as this can move through VERY quickly. We need this ASAP.
0
 
LVL 85
ID: 13735872
The way something like this is normally handled would be to add a "blnLocked' column to your table, which you would then toggle on/off for the various states. For example, when a user initially opens your read-only record, you'd do nothing ... if the user begins to edit, you would then toggle this column to On IF no other user has begun editing:

Sub YourOwnershipButton_Click()

If Me.blnLocked Then
  Msgbox "Another user is currently editing this record"
Else
  Me.AllowEdits = True
  Me.blnLocked = True
  Me.Dirty = False '/this saves blnLocked immediately  
End IF

End Sub

You would also have to toggle this back Off when the user updates:

Sub Form_AfterUpdate()
  Me.blnLocked = False
  Me.Dirty = False
  Me.AllowEdits = False
End Sub

You could use the same methods to write to your tables, if you must ... I see little reason to log who is editing, but you may need this.
0
 
LVL 1

Author Comment

by:kkamm
ID: 13738318
Tried your method, and I get a runtime error 3197 (stopping process because another user is making changes). There are no users editing that record at the time. I am using a test record from six months ago, just to make sure of that.

The hangup occurs on the Me.Dirty=False statement (debugger highlights this line yellow). Are you sure thats the right code for this situation?

PS: I plan on using your idea to do my recordlocking, but instead of one field (blnLocked), there are three (blnLocked [= True], lockusername[= currentuser()], locktime [= now()]) which give me all the data I would need. I have tried it with just one field, however, just to check.
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 13738624
Well, Me.Dirty = False forces the Jet engine to write any changes to tables ... you could try DoCmd.RunCommand acCmdSaveRecord instead. There have also been some issues reported with SQL Server linked tables with Memo fields that do NOT have a TimeStamp field ... seems Access can't quite figure out who is updating what record (even with only one user) and can trigger this error. You could try adding a TimeStamp field if the runCommand doesnt' work.
0
 
LVL 1

Author Comment

by:kkamm
ID: 13738917
I think this error is my fault... and I think I know how to fix it. Me.Dirty should work...

I'll get back to you later today with a response.

by the way, do you think its okay for me to store those three fields in the table as opposed to one? Will my value calls from my previous post work?
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 13741472
What happens when your impatient user end-tasks the application or turns their computer off and the locking record never gets cleared?

This is just one reason to use the locking provided by SQL Server as opposed to trying to code up a locking mechanism. All of this has already been thought about and implemented. I really think you'll get yourself into trouble trying to code in locking.

If it is urgent, then the quickest method is to use SQL Server locking anyway.
0
 
LVL 1

Author Comment

by:kkamm
ID: 13741594
SQL server locking, alongside my mdb, does not allow for row-level locking where the fields cannot be data-ented into, but viewed.
0
 
LVL 1

Author Comment

by:kkamm
ID: 13743340
okay, time to explain where I am at right now...

I used LSMConsulting's method, and this is the error I am getting, specifically:

Runtime error '2115'

The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing WHO EMR Reloaded from saving the data in the field.

And when I debug, it highlights the Me.Dirty = False field in the afterupdate section of the form.

any ideas?

Once again, I cannot thank you folks enough for your help.
0
 
LVL 1

Author Comment

by:kkamm
ID: 13893997
LSMConsulting,

took a bit of gruntwork on my side, but your answer was just the help I needed.

Thanks!
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question