More than one person writing to a record. Need Serious HELP!!! 1

[mlrmlr]

Hi,

I am having a serious issue with a database that has been working great for over a year.

This is a multi-user database with users between 5 and 10. I believe there are multiple users attempting to write or update to the same record causing major errors and the database to lockup.

I am now repairing the database multiple times a day (NOT GOOD). :-(

Does anyone have any suggestions or code I can use to alert a user that a record is currently locked by another user?

I am not a power Access person and I know I am missing some triggers upfront that may help in avoiding or alleviate multiple users writing to the same record.

I am in desperate need of some help and would very much appreciate some assistance.

Thanks so much.

 

[alvechurchdata]

Which version of Access are you using? Later versions gave us better control over record locks.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Craig0201]

Your belief may well be wrong.

It's worked well for a year and suddenly problems are occuring. What's changed?

That would be my first port of call.

Secondly, i'd look at rather more simple file corruption. Create the db and import all the data, queries, forms, etc. Delete the old db and rename the new one. See if that helps.

Then start looking for more complex issues.

 

[mlrmlr]

The Access version is 2003.

I did create a new database and imported over the tables, queries, forms, etc.) and that did not help. I also compiled the code, compacted the databse and ran the Performance Analyzer on all objects.

The only thing I did not do is split the database which may be the next step.

 

[mlrmlr]

Can a slow server cause some of the issues we are experiencing?

 

[ItIsHardToProgram]

No, I don't think the server speed would have anything to do with this...

What kind of errors are you experiencing, only database getting locked up?

I think you might be right about multiple users trying to update the record at the same time...

I wouldn't know how to fix this though, I havent had to come through any of those problems since only admins can add or modify records.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[mlrmlr]

The most current error is when the user attempts to add a new via a button, they receive the error message "you can’t go to the specified record”.

I have seen pound signs (#) in numerous records after repairing the database and viewing the table. I know this indicates lost records and just assume that more than one user were attempting to write to the same new record.

I am desperately trying to pin-point the problem so I can try to resolve the problem.

 

[ItIsHardToProgram]

add a new via a button

Would you provide the code in that button?

maybe the control source also

thanks

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[Remou]

I guess you have tested a scratch database with just you as the user, or you and one other person?

If you have a multi-user database, it is strongly recommended that you split it.
Best Practices When Using Microsoft Office Access 2003 in a Multi-user Environment

http://msdn.microsoft.com/library/d.../en-us/odc_ac2003_ta/html/odc_acmultibest.asp

 

[mlrmlr]

Here is the code for the Add New button:

<BEGIN CODE>
Private Sub Add_New_Record_Input_Form_Click()
On Error GoTo Err_Add_New_Record_Input_Form_Click

DoCmd.GoToRecord , , acNewRec

Exit_Add_New_Record_Input_Form_Click:
Exit Sub

Err_Add_New_Record_Input_Form_Click:
MsgBox Err.Description
Resume Exit_Add_New_Record_Input_Form_Click

End Sub
<END CODE>

The button is on an input form on the "On Click" event.

Remou,

I have thoroughly tested the database myself, it is somewhat difficult to test with others at this time because I am working VPN and it is very slow.

Also, I was just informated by a user that she is unable to get into the database because it is already opened exclusively by User ‘Admin’ on machine M907034290GJ2. Try again when the database is available.

I am the the Admin and I am not in the database. That is not my machine name, it is one of the users' machine.
:-(

 

[Cloonalt]

Do all your users have r/w privileges on the network drive where the data is stored? If any one of them has read only, for starters, when they log on they can't write to the .ldb file and I've seen that cause the problem you've described - everybody gets locked out.

Fran

 

[mlrmlr]

Interesting. Are you referring to the network or the server?

I believe all of the users had r/w privileges because they all have been using the database for over a year. These problems are recent, within the last two weeks.

 

[NvrSaDi]

The code that you used should work if there are no conflicts.
You will get that message, however, if you have code such as "me.allowadditions = false" in a form open function

 

[ItIsHardToProgram]

Sorry I did not get to follow I am having some problems myself....

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[NvrSaDi]

To ItIsHardToProgram.

If you take a simple form with a table as a record source.
Add the button with the code "DoCmd.GoToRecord , , acNewRec" and if there are no conflicts, it works fine. Add code such as "me.allowadditions = false" in a procedure such as form_open and you will get the same error message. This is one possoible cause of the problem. Just guessing with the info available.

 

[mlrmlr]

No, I have absolutely nothing on the form open or load.

I have tried it myself (both ways), when I clicked on the button, I received a dialog form with the message "You can't go to the specified record". When I tried going to the next record by using the record arrows (bottom left site of form) I received this message "No current record".

Any suggestions?

 

[Najemikon]

I used to have similar problems, but it was because I'm running my database on a Terminal Server. The Access record locking doesn't work because it's done at a computer user level and on Terminal Server, they all have the same computer name.

In the end I did it manually. I created a table with a record for each user, and a blank text field. When the first user opens a record on a particular form, it opens the user table in a recordset and puts the current record number in the text field.

When a second user opens the same form, the code will check to see if the record number already exists in another user entry. If it does, it sets 'allowedits = false' and produces an error forcing the user to exit the record, and telling them who the conflicting user is.

It's clunky, but it works. I haven't had any corruption since. And I've expanded the user table with more details so I can see exactly who's logged in and what they are doing (another drawback of Terminal Server is Access shows current users as just so many 'Admin' computers!).

 

[mlrmlr]

Najemikon,

This record locking issue just started occurring. The database was working pretty well for over a year.

Your technique sounds good, but it may be a little overwhelming for me. I understand the table methods, but I am not advance in creating code. :-(

 

[Najemikon]

Ah, sorry, I didn't read your post properly. If it's been working fine, that's strange. But typically IT!

You mention a server though. What sort of server is it? And is Access installed on the same server? We have a Terminal Server and an Exchange server. I've tried splitting the DB across the two and it ran very badly. It's still split but both the frontend and backend are on the TS and run perfect.

Do users run an individual copy of the MDB file on their own PCs, or a share? A VPN user seeing "Admin" smacks of a share; essentially the Terminal Server I described would act the same way.

If it's a share, running on a single machine (be it server or client), and that machine has been upgraded or reinstalled or just had a service pack applied, maybe your Access defaults have switched to, erm, default! Check 'Tools', 'Options' and see what Record Locking is set to under the Advanced tab.

I would recommend splitting the DB, but roll out an MDE frontend version to the users. That will run faster anyway and you can keep an MDB for editing (plus backups!). That way, forms can't be in "exclusive use" by admin.

Regarding the "No current record" error, double-check your form settings and see this thread (

http://www.tek-tips.com/viewthread.cfm?qid=1234420&page=1)

which I posted some time ago. In brief, I frequently have forms change settings without being edited which used to frustrate the heck out of me. Still no explanation, but I roll with it now. It's worth checking.

BTW, thanks for the implication I'm advanced in coding, but trust me, the above manual record locking is very easy. And I developed it from similar recordset code that I simply borrowed from someone here! If you're still stuck, I'll post it and you may as well try it. It's very short, honest!


Jon

 

[mlrmlr]

Jon,

Thanks so much for your time.

I really don’t know what type of server it is or if a physical Access application is installed on the server. I will ask our SA, now I’m curious.

I did not split the database. All users are accessing the database from a share drive on the server.

The record “Edited record” is selected in the “Default record locking”. The default open mode is “Shared”. I have the following set:

OLE/DDE timeout (sec): 30
Refresh interval (sec): 10
Number of update retires: 2
ODBC refresh interval (sec): 1500
Update retry interval (msec): 250

I am definitely going to split the database. I really need to resolve this issue to improve the performance of this tool.

If you don’t mind, I would very much appreciate if you could post the code you used.