Error 3188 - Could not update; currently locked by another session on this machine.
I have an update function on a bound form in Access 2003, which appears to have a record locking issue.
My database has one record per contact for reasons that I won't go into now, but I group related records together with an ID number. For example, if I have 20 contacts at IBM, they hall have the same GroupID number. The function that I am trying to develop is one that updates all of the related records - from information on the current record. For example, the address.
The way that I have the update function structured is to create a DAO recordset that includes all of the records with the same RecordID (but excludes the current record (as it may be being edited)). I run a refresh function just before the update starts (to ensure that everything is up to date on the source record) and then loop through the records in the recordset, updating as it goes.
Occasionally, I get a 3188 record locking error on the first record in the recordset, which is not the source record (I've double checked). This happens when I go into edit the recordset (recBuklUpdate.edit).
If anyone could tell me how to cleanly get around this - it would be really appreciated.
My database has one record per contact for reasons that I won't go into now, but I group related records together with an ID number. For example, if I have 20 contacts at IBM, they hall have the same GroupID number. The function that I am trying to develop is one that updates all of the related records - from information on the current record. For example, the address.
The way that I have the update function structured is to create a DAO recordset that includes all of the records with the same RecordID (but excludes the current record (as it may be being edited)). I run a refresh function just before the update starts (to ensure that everything is up to date on the source record) and then loop through the records in the recordset, updating as it goes.
Occasionally, I get a 3188 record locking error on the first record in the recordset, which is not the source record (I've double checked). This happens when I go into edit the recordset (recBuklUpdate.edit).
If anyone could tell me how to cleanly get around this - it would be really appreciated.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Create a table for the common data in a group - address, city, etc. Then link to that table via the id column. Now you have a single table to update with your address change.
New table: ENTITY
id (autonumber)
address (text)
etc.
etc.
Then change any other table to FK this new table INSTEAD OF MAINTAINING REDUNDANT VALUES!!!
New table: ENTITY
id (autonumber)
address (text)
etc.
etc.
Then change any other table to FK this new table INSTEAD OF MAINTAINING REDUNDANT VALUES!!!
ASKER
I'm sure it's me but I can't see the benefit of this route - since I would still need to update the central data at some point. That said, I have found a slightly different approach.
I think the problem might be something to do with the forms "Record Locks" property. It was set to Edited record, which to me meant it should only let you know about an issue if it was trying to upadte that record, which is isn't since it is excluded from the recordset. As soon as I changed it to No Locks - the issue doesn't arrise.
Is this a good/safe idea, as I ended up with some corruption when it gave me the locking issue earlier.
Thanks for all of your help on this.
I think the problem might be something to do with the forms "Record Locks" property. It was set to Edited record, which to me meant it should only let you know about an issue if it was trying to upadte that record, which is isn't since it is excluded from the recordset. As soon as I changed it to No Locks - the issue doesn't arrise.
Is this a good/safe idea, as I ended up with some corruption when it gave me the locking issue earlier.
Thanks for all of your help on this.
>>I'm sure it's me but I can't see the benefit of this route
OK, so you have a group of 10 accounts (or 100, or 10,000) that share the same address, city, state. If you store the exact same data for each account, the chance of making an error in updating - or "forgetting" a new account - exists.
Now imagine one row in a table that stores the address info. If you update it, then *all* of the accounts that share that address are "updated", too.
Your update time is minimal; your database will not bloat with redundant data (read up on database normalization); if something is wrong, you have a SINGLE PLACE to look for the error.
OK, so you have a group of 10 accounts (or 100, or 10,000) that share the same address, city, state. If you store the exact same data for each account, the chance of making an error in updating - or "forgetting" a new account - exists.
Now imagine one row in a table that stores the address info. If you update it, then *all* of the accounts that share that address are "updated", too.
Your update time is minimal; your database will not bloat with redundant data (read up on database normalization); if something is wrong, you have a SINGLE PLACE to look for the error.
ASKER
I now see what you mean - Unfortunately, in this case normalization is not applicable for this part of the database, but I understand your position (and would normally agree). Putting the discussion to one side, the issue is down to record locking (please see my previous comment).
All the best and thanks again.
All the best and thanks again.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks guys - that really helped.
No worries - glad to help.
ASKER
The recBulk.Edit function is only called once for each record (and it's the very first record in the recordset that causes the issue) - before anything else happens.