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.

 

[Najemikon]

I think the key to all this is going to be on your network/ server, not your application. It really sounds like you have some sort of share going on. This can cause problems as it will clash with the database settings. Something has changed on the server, highlighting a basic flaw in the database.

As I understand it, the record locking depends on multiple connections or terminals for Access to distinguish between. If a single MDB file is being shared, it would defeat this as Access can see only one unique terminal, being used multiple times.

Generally speaking, splitting a database is intended to have a backend on a server share, while each PC has a frontend. This is basically for speed. If yours is shared, it is still important to split it, even if the files are all in one directory.

As I said before, however you split it, I would keep an MDB for myself and share/roll out an MDE file for the users. The speed increase should be quite large.

I'm not sure any of this would cause the "No current record" error though. I suppose it could, but double-check the form settings I described in the other thread.

Now, I've been assuming something that needs clearing up before I post that code! Are you using workgroup level security for your users? Do they all have independent usernames?

Hopefully I'm not talking out of somewhere other than my mouth and sending you down the wrong path! But I definitely think you need to know exactly how your server and Access work together.


Jon

 

[mlrmlr]

Hi Jon,

You have been a wealth of information and I thank you for it.

I can not understand why this is suddenly happening. I also believe that the issues are related to the server or network but my server/network knowledge is not strong enough to challenge the SA.

The issue my users experienced today was missing data. A couple of users were missing data that they recorded (when it rains it pours). I am so frustrated that I just removed the database from the server and asked the users to revert to a backup tool that I created.

I plan on splitting the database, making a MDE file and loading the FEs tomorrow. Let’s see how that works. I will post my encounters tomorrow.

No, I am not using workgroup level security for the users. I tried using the workgroup a couple of times and flopped. Maybe I should try it again.

Mary

 

[Najemikon]

I would highly recommend running the workgroup security wizard. To be honest, I've never looked into it in detail; just ran the wizard, kept to defaults, crossed my fingers and it worked! I think it should be ran after you split it, though I'm really guessing there.

 

[mlrmlr]

Jon,

I'll try the workgroup on a test database after I split it.

Thanks.

 

[Craig0201]

Re: I can not understand why this is suddenly happening.

I asked this right at the top and was ignored so I will try again.

What has changed? You didn't experience a problem for a year, you are now. Therefore, the likely culprit is something having recently changed. So what is it?

 

[mlrmlr]

Craig0201,

I am so sorry, I definitely don't mean to ignore anyone.

Not much as changed, maybe the workload and now everyone is trying to input data at the same time. I added one more field (couple of checkboxes).

I recently split the database as suggested, but users are still receiving the message "You can't go to the specified record".

I don't know how to get around this issue. I do not have advance experience in code which may help.

Any suggestions?

 

[debot54]

I see that you have received a ton of advice on your problem but the first thing I would do is spli the DB. Having all users share the same front-end is simply asking for trouble. As this is probably the easiest of all solutions you have been given, I think you should definately try this first.

 

[Najemikon]

How many users get this "you cannot go to the specified record" message? Does it still happen with only one person logged in? If not, how many have to be logged in before it does happen?

If it does happen with only one user, copy the database to a single PC, or log directly onto the server console. Basically, remove the network share and multiple users from the equation. Something fundamental is wrong, so running the database from a share is going to muddy the issue.

 

[mlrmlr]

Debot54, I finally split the database but there are two or three users still receiving the message "You can't go to the specified record" message.

Najemikon, I believe about three individuals have received the message, but one indiviual recieves the message after being able to enter 3 or 4 records.

I'm not sure how many users are logged in when this message occurs. we usually have no more than 10 users in the database per shift (three shfts).
Do you think the number of userslogged in at a given time may be the problem?

 

[Najemikon]

It certainly could be. You need to test it by only having one person logged in and adding records. If you still have a problem, consider setting it up temporarily on a single PC for one user just to test.

 

[Craig0201]

Seen this?

http://www.mcse.ms/archive164-2004-9-1095272.html

Are people trying to move out of a new record with invalid data on the existing record?

 

[mlrmlr]

Craig0201,

I read the post provided, thank you.

I removed ALL of the required fields which I originally thought was causing the issue, but apparently not. :-(

 

[Najemikon]

Is the form somehow filtered? Would saving the record cause that filter to change? For instance, if the form is linked to a table of addresses, but only shows records where "postcode" (or something) is blank, and then you edit a record and fill in the postcode, that record is no longer valid for the filter. I think I'm talking rubbish because I'm sure it wouldn't cause an error! But maybe it's worth mentioning if it sets you on the right track... At least check the form properties for filters.

Otherwise, is there anything particulary special about the form? To test problems like this I sometimes create a new form using the wizard and all default settings. It should literally take a few seconds; click new form wizard, choose your table, finish and use! Fill in the fields as you'd normally expect to. If there is still a problem, it's likely with the table design.

And did you check the thread I linked to before about forms that change settings? I know from experience that if the form is set to "Data Entry = No", you will get that error if you try to add a new record.

 

[mlrmlr]

Najemikon,

The form is pretty basic. I don't have any filters or complicated functions on the form. The only thing I noticed that changed was the "Default Record" locking changed from "No Locks" to "Edited Locks". I changed it back to "No Locks".

There are no filters on the form. The allow record filtering property is set to no filters.

The Data Entry property is set to "Yes".

Here is the code attached to the form.

<BEGIN CODE>
Option Compare Database
Option Explicit


[BLUE] 'ATTACHED TO THE TECH LAST NAME COMBO BOX [/BLUE]
Private Sub cmbChooseLastName_LostFocus()

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

End Sub

[BLUE] 'ATTACHED TO THE CLOSE BUTTON [/BLUE]

Private Sub Close_Click()
On Error GoTo Err_Close_Click

DoCmd.Close
DoCmd.SelectObject acForm, "WO_Menu", False
DoCmd.Restore

Exit_Close_Click:
Exit Sub

Err_Close_Click:
MsgBox Err.Description
Resume Exit_Close_Click

End Sub


[BLUE] 'ATTACHED TO THE ADD RECORDS BUTTON [/BLUE]

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

[BLUE] ‘ATTACHED TO THE FORM OPEN EVENT [/BLUE]
Private Sub Form_Open(Cancel As Integer)

Call_Type_ComboBox.SetFocus

End Sub
<END CODE>

 

[Najemikon]

I can understand picking which control to focus on as the form opens, but what's the thinking behind the lost focus event? It looks like a save command. Is it really needed at that point?

 

[mlrmlr]

Najemikon,

This event is attached to a combobox that is attached to a query of user last names. When a user selects his or her name, another combobox is populated with their first name.

I actually inherited this form from someone else so, I'm not quite sure what the purpose of this function is or if it is really needed. What do you think?

Please, if you have another suggestion or better solution to perform a lookup on the user names, I would love to know.

 

[Najemikon]

Have you noticed if that function of auto-populating the first name always works? I'm wondering if it's something there that trips the "cannot go to..." message. If the users are able to populate the first name themselves, consider commenting out the lost focus code and seeing how it behaves.

So:

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Becomes:

'DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

 

[mlrmlr]

Najemikon,

I will try your suggestion and hope this is the cause.

Stay tune...

 

[mlrmlr]

Jon,

I followed your suggestions of splitting the database and removing the "Lost Focus" event. So far, this seems to do the trick and the database has been working pretty smoothly.

I appreciate the time you spent troubleshooting this issue with me. You deserve more than the starts assigned but unfortunately this is the best I can do via the forum.

Thanks again. :->