Well, it's not locking per se - but locking them from being able to view. I thought that this might be able to be done via some method I was unaware of. Apparently not.
out of interest, why would you want to prevent someone viewing a record while others are using it? - i would think you could just show an indicator to point out that it is in use (maybe not so easy) - but a user couldnt change it even with no locking (ie optimistic locking ) because then you get the "another user changed the record error"
Because this information is used to call customers. We don't want to call a customer more than one time. Thus, if two people look at the same record, they may call the same person. This is bad. Doesn't matter that they can't change it - there is no contact information in this database. They must look that up in a different system. So the first thing they do is look at it, then pull up another program with the phone number, then make a call. No changes are made to the form until after the call is completed.
Also, users are not very bright. I don't want to rely on them to take the proper course of action, which has proven too difficult for them many times. I want to prevent them from being able to take the wrong course of action in the first place.
your last quote I don't get how they are locking so many records at once.
As stated, not an actual lock. Just terminology. I am in effect locking a user out of a record using a Yes/No field to set a lock of my own. You would have to read the post that shows how everything is done, start to finish, to understand what the program is doing.
been trying to post this for a while - but very slow
thats just it. I think I read enough to understand what you were doing - its just that i dont think you need anything other than normal optimistic record locking
one thing is - are you using continuous, or single forms? do your users have to click the "ive dealt with this flag" before they call the customer? Tjhere is obviously no way you can enforce this - its just a good habit they need to get into
the problem is that it seems to me that each user will effectively have a "snapshot" of the data in their recordset - and you need to refresh/requery the data set in some way, to ensure that changes to the "ive dealt with this flags" are available
Now, its easy to do this with a single forms - as the data isnt visible until they navigate to a given record, and therefore you can refresh it in the forms current event.
Although you can also do this in the current event a datasheet/continuous view, the user can "see" the potentially incorrect data at all times.
So if the current user has to set the flag BEFORE calling the customer, and in the meantime another user has set the flag - then its easy to refresh the row, and report any changes to the current user
alternatively you could refresh/requery the data every few minutes on a timer setting
you could also allocate each caller a section of the contacts - maybe based on alphabet - that way they dont see each other's potential calls
been trying to post this for a while - but very slow
thats just it. I think I read enough to understand what you were doing - its just that i dont think you need anything other than normal optimistic record locking
one thing is - are you using continuous, or single forms? do your users have to click the "ive dealt with this flag" before they call the customer? Tjhere is obviously no way you can enforce this - its just a good habit they need to get into
the problem is that it seems to me that each user will effectively have a "snapshot" of the data in their recordset - and you need to refresh/requery the data set in some way, to ensure that changes to the "ive dealt with this flags" are available
Now, its easy to do this with a single forms - as the data isnt visible until they navigate to a given record, and therefore you can refresh it in the forms current event.
Although you can also do this in the current event a datasheet/continuous view, the user can "see" the potentially incorrect data at all times.
So if the current user has to set the flag BEFORE calling the customer, and in the meantime another user has set the flag - then its easy to refresh the row, and report any changes to the current user
alternatively you could refresh/requery the data every few minutes on a timer setting
you could also allocate each caller a section of the contacts - maybe based on alphabet - that way they dont see each other's potential calls
I have decided to go a completely different route. Here is a function I made which will get and assign new records to an individual.
Code:
Public Function getNewRecords()
Dim db As Database
Dim rs As DAO.Recordset
Dim strSQLFunc As String
Set db = CurrentDb()
strSQLFunc = "SELECT * FROM tblTemp WHERE tblTemp.INITIALS='" & strInitials & "' AND tblTemp.COMPLETED=False;"
Set rs = db.OpenRecordset(strSQLFunc)
If rs.RecordCount <> 0 Then
'Continue work with existing records.
Exit Function
Else
'Update and add more records to work with.
rs.Close
Set rs = Nothing
strSQLFunc = "SELECT TOP 10 * FROM (SELECT TOP 500 * FROM tblTemp WHERE tblTemp.COMPLETED = False AND tblTemp.LOCKED = False) ORDER BY Rnd(tblTemp.ACCTNUM) DESC;"
Set rs = db.OpenRecordset(strSQLFunc)
If rs.RecordCount <> 0 Then
rs.MoveFirst
Do Until rs.EOF
rs.Edit
rs![LOCKED] = True
rs![INITIALS] = strInitials
rs.Update
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set db = Nothing
Else
MsgBox "ERROR: No records found. Plesae notify IT before continuing."
Exit Function
End If
End If
End Function
This is just for testing purposes at the moment. I will probably have it assign 50 records at a time to a user. Then while they are working, it will just draw from that group.
On the Form_Load event I have this code:
Code:
getNewRecords
strSQL = "SELECT TOP 1 * FROM tblTemp WHERE tblTemp.INITIALS='" & strInitials & "' AND tblTemp.COMPLETED=False;"
Form.RecordSource = strSQL
So if there are existing records, the getNewRecords function will not do anything, and the form will go on to work with the existing records. If none exist, it will grab more records to work with and assign them a user ID. I have it using initials at the moment, just for testing, but that will change.
When they get a new record, I just have this code:
This will be sure there are more records before doing a requery on the form. This is after necessary error checking to be sure the current record was actually completed, etc.
So far this works great, but I'm working with a temp table and am the only one. Once multiple people start using it, I may have issues.
Basically, when it is grabbing new records and updating them with the users ID and setting locked=true, it may run into one that someone else has edited and has locked. I need to know how to handle that error so it will ignore that record and then continue with the others. Is there a way to see if a particular record is locked by another user editing it before updating it?
Also, since this happens before the form has a recordsource, how do I enable record locks in that function so that it will lock a record (or the whole recordset of 50 records that it opens) while it works with them - and then unlocks them after it is done?
The problem is that you're looping in a recordset and "locking" them one by one. It still is open to the possibiility of two users selecting same records before it's actually locked.
As I explained earlier, you shouldn't do a SELECT first, but rather do an UPDATE first then SELECT after.
Basically, in the getNewRecords, you would do this:
Code:
UPDATE tblMains SET
LOCKED = Yes
Initial = <whatever>
WHERE <some criteria>
then for the form itself, it would do:
Code:
SELECT *
FROM tblMains
WHERE Initials = <same data>
AND LOCKED = True;
I don't want to update based on any criteria. (Rather, the criteria I want to update based on will return all unassigned and uncompleted records, but I don't want to update that many.)
I just want to update say 50 records. The only criteria being that both the Locked and Completed fields are set to False. The Locked field will become the UserID field, so it's criteria should be Is Null, but same basic thing.
My problem is that the TOP function doesn't seem to work with UPDATE. If I put UPDATE TOP 50, etc. it would update all the records that match the criteria, which I don't want.
Well, update query is a set operations, so of course if the only criteria is locked & completed is false, then it'll update all matching rows. If you truly just want random batch or don't particularly care _what_ the 50 records are, then this should work:
Code:
UPDATE tblMains SET
Initial = <whatever>,
LOCKED = True
WHERE <primary key> IN (
SELECT TOP 50 <primary key>
FROM tblMains
WHERE LOCKED = False
AND Completed = False
ORDER BY <whatever>
);
Note that UPDATE query itself uses primary keys to determine which rows to update rather than the random selection.
Does that address what you want to do?
BTW, because this is technically two queries, I would feel better if this was wrapped in a transaction:
Code:
With CurrentDb
DBEngine.BeginTrans
.Execute "<the SQL above>", dbFailOnError
DBEngine.CommitTrans
End With
to ensure the whole operation is executed as a single atomic operation and thus minimize contention/conflicts.
Perhaps. I tried the IN part before, but it wasn't working right.
My main table has 2 primary keys. Basically the two fields contain a whole account number, but they are split in two pieces. The first is a branch, the second is the rest of the account number. So in order to insure no duplicates were imported, both of those fields are set as PK's.
Ah, didn't realize you had a composite key in the table. Yes, you would select both columns, but perhaps like this:
Code:
PK1 & PK2 IN (SELECT Pk1 & Pk2 ...
e.g. concatenate them together so you don't have to do two separate IN()s for each column. Be aware, though, this could kill performance because this negates ACE's ability to use an index here. If this is too slow, consider using a surrogate key and use it as primary key then defining the PK1 & Pk2 as UNIQUE.
I'm glad that worked out. Remember if you have performance problem, you know what to do with the composite keys. This is one reason why I don't particularly like using composite keys as primary key. Defining them as UNIQUE, sure. As primary keys, ehh, one long integer, please.
Also, to be 100% sure, did you see the part about wrapping it in transactions?
I have had problems with it not incrementing correctly, and I have to run some code on the back end to reset it. This has happened with a few databases that are heavily used. I don't know what causes it.
I have heard that setting it to random fixes this particular issue. I just haven't done one like that yet.
To be clear, are you saying the code runs fine on both tables, but run too slowly on the main table? If that is the case, then it's as I said - the "fix":
Code:
PK1 & PK2 IN (SELECT PK1 & PK2...)
forces the engine to do a full table scan, and it can't use the index to speed up the operation. You would need to alter the table so there's a single primary key column. You can keep the original PK1 & PK2 and define them as UNIQUE to preserve the effect you had when they were defined as a composite primary key but for lookups, use a single-column PK.
FWIW, autonumber shouldn't cause problem and if they do, it's usually to do with the database design or code doing stuff that really doesn't need be done. Using random is an alternative, though.
Probably because they aren't. If they are one field, they are unique, but as two fields, it is possible to have duplicates. But I don't want any where both fields are identical, which would indicate a duplicate account.
