Editing Same data at same time?

Trystan

New member
Local time
Today, 17:33
Joined
Jun 10, 2003
Messages
6
Hi all,

i don't know if this is something that's common or not. my company has a HUGE access database (we're up to 7000+ records) and most of the work in access is done in a form with a main form, and then multiple subforms for different parts of the project (writing, billing, etc.), and they are filled in as completed.

for some time now, i've been adding assignments to this database, and just recently, when i enter new information into one of the subforms (if there are existing records already in the subform this doesn't happen), i get the following message:

"The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time." ....and i get this 5 or 6 times in a row.

here's the problem: i'm the only one changing this data at the time!! (and i'm not *changing*, i'm adding information.

if i go back into the fields in question, they say "#Error" however, if i go to the next or previous record in the main form (using the arrows at the bottom of the main form), then return to the record i was just editing, the correct information is there already.

(1) did i explain that clearly?
(2) has anyone else encountered this?
(3) does anyone have an idea what the problem might be and how it can be fixed? (using regular English, i'm not that well-versed in Access language)

thanks!
Trys
 
Last edited:
7000 records is not huge. It is in fact, small. However, the problem is that there is some code running in some form event that is updating the bound recordset. It is not necessary to use DAO/ADO or an update query to update bound records. You should leave that to Access.

To resolve the problem, you'll need to find the code and determine when it runs and why before we can offer an alternative solution.
 
Pat Hartman said:
To resolve the problem, you'll need to find the code and determine when it runs and why before we can offer an alternative solution.

how would i go about finding the code?

i don't use an update query....it's one main form with several subforms. when i add the data in say, the Invoice section, if it's the *first* invoice in the main record, then i get the message. if the main record has already had an invoice, when i add the second record, i *don't* get the message.

the question then is, how do i find the code, how do i determine when it runs, and why? :confused:

as i said, i'm not that well-versed in Access Language.

cheers,
Trys
 
Open the code module for the form and copy and paste it here.
 
the Code for the Form

thanks Pat.
with the name of the form highlighted, i clicked on the menu bar button labeled "Code" and this is what i got.......

also, to note, this is a shared database, shared by about 9 people.....

i'm not sure if this what you're looking for (or if it's right):

Option Compare Database

Private Sub Command73_Click()
On Error GoTo Err_Command73_Click


DoCmd.GoToRecord , , acNewRec

Exit_Command73_Click:
Exit Sub

Err_Command73_Click:
MsgBox Err.DESCRIPTION
Resume Exit_Command73_Click

End Sub
Private Sub Command75_Click()
On Error GoTo Err_Command75_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.PrintOut acSelection

Exit_Command75_Click:
Exit Sub

Err_Command75_Click:
MsgBox Err.DESCRIPTION
Resume Exit_Command75_Click

End Sub
Private Sub Command76_Click()
On Error GoTo Err_Command76_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Command76_Click:
Exit Sub

Err_Command76_Click:
MsgBox Err.DESCRIPTION
Resume Exit_Command76_Click

End Sub
Private Sub Command77_Click()
On Error GoTo Err_Command77_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "CLIENTS"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command77_Click:
Exit Sub

Err_Command77_Click:
MsgBox Err.DESCRIPTION
Resume Exit_Command77_Click

End Sub
Private Sub ActivityLog_Click()
On Error GoTo Err_ActivityLog_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "ActivityLog"

stLinkCriteria = "[CaseID]=" & Me![ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_ActivityLog_Click:
Exit Sub

Err_ActivityLog_Click:
MsgBox Err.DESCRIPTION
Resume Exit_ActivityLog_Click

End Sub

Private Sub Form_Activate()
If Me.ID <> Null Then
g_currentID = Me.ID
End If
End Sub

Private Sub Form_Current()
If IsNumeric(Me.ID) Then
g_currentID = Me.ID
End If
End Sub
Private Sub PrintActivityLog_Click()
On Error GoTo Err_PrintActivityLog_Click

Dim stDocName As String

stDocName = "caseActivityLog"
DoCmd.OpenReport stDocName, acNormal

Exit_PrintActivityLog_Click:
Exit Sub

Err_PrintActivityLog_Click:
MsgBox Err.DESCRIPTION
Resume Exit_PrintActivityLog_Click

End Sub
Private Sub Print_NewCase_Click()
On Error GoTo Err_Print_NewCase_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.PrintOut acSelection

Exit_Print_NewCase_Click:
Exit Sub

Err_Print_NewCase_Click:
MsgBox Err.DESCRIPTION
Resume Exit_Print_NewCase_Click

End Sub


thanks,
Trys :)
 
One other thing.....

i did not design this database; i have no idea who did, or their level of expertise, and one of the managers is very paranoid about anyone editing the database...... so if this is fixable, it may take some time.

if it's not fixable, i can deal with the error message.

thanx again,
cheers,
Trys :cool:
 
wondering if what i posted is the information needed?

Trys
:confused:
 
The following If will not work. This is NOT how to test for nulls:
Private Sub Form_Activate()
If Me.ID <> Null Then
g_currentID = Me.ID
End If
End Sub
Change it to:
Private Sub Form_Activate()
If Not IsNull(Me.ID) Then
g_currentID = Me.ID
End If
End Sub

I don't know what the following menu item is doing.
Private Sub Command76_Click()
On Error GoTo Err_Command76_Click

Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Command76_Click:
Exit Sub

This code was probably converted from a macro. It references the menu structure of A95 - that's what Ver70 is and without having that version of Access loaded I have no idea what the 10th menu option does. Does the button label offer a clue?

In the following sub, the criteria is not specified. This may or may not be causing a problem:
Private Sub Command77_Click()
On Error GoTo Err_Command77_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "CLIENTS"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command77_Click:
Exit Sub

Err_Command77_Click:
MsgBox Err.DESCRIPTION
Resume Exit_Command77_Click

End Sub
 
The following fix worked everytime for me

The Fix:
1. Check the residing folder of the database for a db1.mdb. If it has one, to avoid conflicts, put it in a temp folder while you work.
After all the steps are completed, you may want to determine what it is. I would not recommend using it, because it will most likely contain old data.
2. Make sure everyone else has closed their database connection
3. Go to tools in the menu
4. Go to Database Utilities
5. Select Compact Database (Office 2000 and XP Compact and Repair)
6. You will receive an error message, click Ok and you will find a new db1.mdb in the residing folder, this is repaired copy of the database.
7. Rename the original database to nameBak.mdb
8. Rename the db1.mdb to the original name
9. Go into the database with the bad record and delete the ##### garbage in the bad field, then update it with what should be in the field.
10. Confirm the database is okay, then delete the nameBak.mdb

Why this happens:
If I am right...the problem occurs because 2 people at the same time are trying to edit the same record. Then they try to move to another record and a message box comes up with 3 options.
1. Drop the changes
2. Save the changes
3. Copy to the clip board

If the user receiving the message selects 2, it trashes the data causing the error.

I would always select drop the changes, move back a record to refresh, move forward and start over.

I have instructed all users to select drop the changes and go back. this has eliminated the problem.

Good Luck Martin
 
Last edited:

Users who are viewing this thread

Back
Top Bottom