Engine stopping processes due to phantom data accesses!

**ADezii** · Jul 11 '07, 10:48 AM

Originally posted by DaveSlade72

Whilst trying to update my Access 97 database I'm getting the error 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".
There are no other users accessing the database when the error occurs, and the table being updated is only available through the database itself so no one could possible have access to it.
So my question is simply - can this error be generated by any other means than that which it is claiming!
I did have a similar file-locking problem back in January that was solved by re-booting the server, but before I do that and disrupt my colleagues work, I wanted to explore other options.

The update query worked fine a week ago, and I'm not aware of having made any changes to the queries involved.

Regards,
Dave Slade
Cardiff

Close the Database.
See if there is an *.ldb file present with the same Base Name and in the same Directory as your Access Database *.mdb. If this file is present, then Delete it.
Open the Database again and see if the same problem still exists.

**DaveSlade72** · Jul 11 '07, 11:02 AM

Originally posted by ADezii

Close the Database.
See if there is an *.ldb file present with the same Base Name and in the same Directory as your Access Database *.mdb. If this file is present, then Delete it.
Open the Database again and see if the same problem still exists.

There is no ldb file to delete I'm afraid.

**puppydogbuddy** · Jul 11 '07, 12:58 PM

Originally posted by DaveSlade72

There is no ldb file to delete I'm afraid.

That message often occurs because a user attempts to edit a record that is also being updated by a background process via VBA code. Check and see if you have any VBA code updating the record when you get that message. The message also occurs if the same record is being accessed via 2 open forms and an attempt is made to commit changes on the second form before the changes on the first opened form are commited.

The remedies?
In the case of the 2 opened forms, insert the following code for form1 before opening form2:
If Me.Dirty Then
Me.Dirty = False
End If

In the case of the vba background process, you can trap the error and then issue an "On Error Resume Next"

Here is method for dealing with lock conflicts as a result of 2 users simultaneously accessing the same record. This method is provided by www.aadconsulti ng.com on their tips page:

For every Access database opened for shared use, an .ldb file is created to store computer and login names, and to place extended byte range locks. The .ldb file always has the same name as the opened .mdb and is located in the same folder.

The Jet database engine uses .ldb file information to prevent users from writing data to pages that other users have locked, and to determine who has other pages locked. If Jet detects a lock conflict with another user, it reads the .ldb file to get the computer and login name of the user who has the file or record locked.

In most lock conflict situations, Access raise a generic Write conflict message that allows you to save the record, copy it to the Clipboard, or drop the changes you made. In some circumstances, you may receive the following error message:

Couldn't lock table <table name>; currently in use by user <security name> on computer <computer name>.
1. In the form create a TextBox that sits exactly over the ComboBox

2. Set the ComboBox's Visible property to False

3. The TextBox is Visible and holds a value according to needs of the application: it may be a bound control or, as in the sample code below, unbound with the value assigned on the Form_Load event.

4. Clicking on the TextBox hides it and displays the ComboBox. After the user has updated the ComboBox, the value is assigned to the TextBox and the ComboBox hidden again.

Private Sub Form_Load()
Me!txtHideShow. SetFocus
Me!txtHideShow. Text = "Test"
End Sub

Private Sub txtHideShow_Cli ck()
Me!cmbHideShow. Value = Me.txtHideShow. Value
Me!cmbHideShow. Visible = True
Me!cmbHideShow. SetFocus
Me!txtHideShow. Visible = False
End Sub

Private Sub cmbHideShow_Aft erUpdate()
Me!txtHideShow. Value = Me.cmbHideShow
Me!txtHideShow. Visible = True
Me!txtHideShow. SetFocus
Me!cmbHideShow. Visible = False
End Sub

**DaveSlade72** · Jul 11 '07, 01:52 PM

Originally posted by puppydogbuddy

That message often occurs because a user attempts to edit a record that is also being updated by a background process via VBA code. Check and see if you have any VBA code updating the record when you get that message. The message also occurs if the same record is being accessed via 2 open forms and an attempt is made to commit changes on the second form before the changes on the first opened form are commited.

The error occurs when trying to run an update Query. The original table was deleted the first time the query failed and because the query failed it was not re-created, so there is no table for anyone else to be accessing/updating or generally meddling with. There are no forms at all in the database and there should not be any code working behind the scenes.

I have copied the database from an old back-up and tried to run the query on a local drive rather than the server but this has also not helped.

Could some kind of feedback in the query cause this error? I didn't think that I'd made any changes to it in the past two weeks, but I may be wrong.

**DaveSlade72** · Jul 11 '07, 05:34 PM

Originally posted by DaveSlade72

The error occurs when trying to run an update Query. The original table was deleted the first time the query failed and because the query failed it was not re-created, so there is no table for anyone else to be accessing/updating or generally meddling with. There are no forms at all in the database and there should not be any code working behind the scenes.

I have copied the database from an old back-up and tried to run the query on a local drive rather than the server but this has also not helped.

Could some kind of feedback in the query cause this error? I didn't think that I'd made any changes to it in the past two weeks, but I may be wrong.

It does appear to be a VB issue after all - one of the fields in the table being created was formed by combining three memo fields whilst stripping out some Rich Text Formating. This function has worked perfectly for three years, but today has caused this error. I'll have to look at a work around tomorrow but I'm none the wiser as to where this problem has suddenly appeared from.

Thanks for the suggestions.
Dave

**puppydogbuddy** · Jul 11 '07, 07:37 PM

Originally posted by DaveSlade72

It does appear to be a VB issue after all - one of the fields in the table being created was formed by combining three memo fields whilst stripping out some Rich Text Formating. This function has worked perfectly for three years, but today has caused this error. I'll have to look at a work around tomorrow but I'm none the wiser as to where this problem has suddenly appeared from.

Thanks for the suggestions.
Dave

Dave,
Maybe you are getting message because Access can't open the table and thinks the table is locked? Just in case, you should check to see if the table you are updating exists before you execute the update. If it doesn't exist, abort the update.

Here is code to check if a table exists:

Code:

Private Function CheckIfTableExists() As Boolean
' Returns True if table exists.
   
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
   
    Set db = CurrentDb

   ' Try to open table to see if it exists.
    On Error Resume Next
    Set rst = db.OpenRecordset("uztblErrorLog")   'enter name of your table

    ' If there's no error, ie. table exists, returns True.
    If Err = 0 Then
        CheckIfTableExists = True
    Else
        CheckIfTableExists = False
    End If
   
End Function

This approach can be extended to other Access objects.

**DaveSlade72** · Jul 12 '07, 07:55 AM

Originally posted by puppydogbuddy

Dave,
Maybe you are getting message because Access can't open the table and thinks the table is locked? Just in case, you should check to see if the table you are updating exists before you execute the update. If it doesn't exist, abort the update.

Here is code to check if a table exists:

Code:

Private Function CheckIfTableExists() As Boolean
' Returns True if table exists.
   
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
   
    Set db = CurrentDb

   ' Try to open table to see if it exists.
    On Error Resume Next
    Set rst = db.OpenRecordset("uztblErrorLog")   'enter name of your table

    ' If there's no error, ie. table exists, returns True.
    If Err = 0 Then
        CheckIfTableExists = True
    Else
        CheckIfTableExists = False
    End If
   
End Function

This approach can be extended to other Access objects.

Sorry - this just shows how long ago I set the queries up - it's actually a Make Table query, not an Update query. So if the table is already there it is deleted first and then created. At least that is what used to happen before yesterday!

**DaveSlade72** · Jul 12 '07, 08:43 AM

Originally posted by DaveSlade72

Sorry - this just shows how long ago I set the queries up - it's actually a Make Table query, not an Update query. So if the table is already there it is deleted first and then created. At least that is what used to happen before yesterday!

The field that is causing the problem in concatenating comments fields from four other linked tables. Three of those tables are fine, but one is not.

Code:

IIf(SURVEY_EVENT!COMMENT>"",STRIPRTF(SURVEY_EVENT!COMMENT),"") 
& IIf(SAMPLE!COMMENT>"",STRIPRTF(SAMPLE!COMMENT),"") 
& IIf(TAXON_DETERMINATION!COMMENT>"",STRIPRTF(TAXON_DETERMINATION!COMMENT),"") 
& IIf(TAXON_OCCURRENCE!COMMENT>"",STRIPRTF(TAXON_OCCURRENCE!COMMENT),"")

If I take out the fourth argument, the Make Table Query runs perfectly. Put this one back in and I get the error message about two users trying to alter the same data at the same time. In fact the STRIPRTF function is not at fault, as this can be removed from the final argument and it will still fail because of the other user (that does not appear to exist).

Having determined where the error is coming from, I now presume there must be a problem in the linked table, but I'm a little concerned. The whole point of the query was to extract data from the linked table without compromising the integrity of the linked database. This error seems to imply that this Make Table query is having an effect on the linked table... or am I missing something?

**ADezii** · Jul 12 '07, 10:47 AM

Originally posted by DaveSlade72

The field that is causing the problem in concatenating comments fields from four other linked tables. Three of those tables are fine, but one is not.

Code:

IIf(SURVEY_EVENT!COMMENT>"",STRIPRTF(SURVEY_EVENT!COMMENT),"") 
& IIf(SAMPLE!COMMENT>"",STRIPRTF(SAMPLE!COMMENT),"") 
& IIf(TAXON_DETERMINATION!COMMENT>"",STRIPRTF(TAXON_DETERMINATION!COMMENT),"") 
& IIf(TAXON_OCCURRENCE!COMMENT>"",STRIPRTF(TAXON_OCCURRENCE!COMMENT),"")

If I take out the fourth argument, the Make Table Query runs perfectly. Put this one back in and I get the error message about two users trying to alter the same data at the same time. In fact the STRIPRTF function is not at fault, as this can be removed from the final argument and it will still fail because of the other user (that does not appear to exist).

Having determined where the error is coming from, I now presume there must be a problem in the linked table, but I'm a little concerned. The whole point of the query was to extract data from the linked table without compromising the integrity of the linked database. This error seems to imply that this Make Table query is having an effect on the linked table... or am I missing something?

Delete the Link to this Table.
Import the Table (make in Internal).
Run the code again.
This should further isolate the cause of the problem as to whether it is a connectivity, multi-access, locked, etc. problem.

**DaveSlade72** · Jul 12 '07, 11:25 AM

Originally posted by ADezii

Delete the Link to this Table.
Import the Table (make in Internal).
Run the code again.
This should further isolate the cause of the problem as to whether it is a connectivity, multi-access, locked, etc. problem.

Ah! It won't let me import the table, and gives me the same message... getting closer!

**DaveSlade72** · Jul 12 '07, 12:31 PM

Ok. The problem lies with the linked database (which is also Access97).

I've tried Repairing it, and that runs and says it's worked. Compacting it however throws up an error, initially that I don't have read permissions on the database, and the 'MSysCompactErr or' -

ErrorCode ErrorDescriptio n ErrorRecid ErrorTable
-1611 The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time. à8 TAXON_OCCURRENC E

To be honest the databse looks like it's been compacted ok, but I'm not 100% certain.

**puppydogbuddy** · Jul 12 '07, 01:19 PM

Originally posted by DaveSlade72

Ok. The problem lies with the linked database (which is also Access97).

I've tried Repairing it, and that runs and says it's worked. Compacting it however throws up an error, initially that I don't have read permissions on the database, and the 'MSysCompactErr or' -

ErrorCode ErrorDescriptio n ErrorRecid ErrorTable
-1611 The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time. à8 TAXON_OCCURRENC E

To be honest the databse looks like it's been compacted ok, but I'm not 100% certain.

Oh...if it is a make table query, then I think I know what your problem is. You should not have deleted the prior temp table because the delete of the prior table is built into the make table query, itself. If you erase it, then Access will be unable to execute the make table query. If you have copies of the temp table, just copy it over to your application and you should be ok. If you don't have copies you may have to create a new make table query.

**DaveSlade72** · Jul 12 '07, 01:34 PM

Originally posted by puppydogbuddy

Oh...if it is a make table query, then I think I know what your problem is. You should not have deleted the prior temp table because the delete of the prior table is built into the make table query, itself. If you erase it, then Access will be unable to execute the make table query. If you have copies of the temp table, just copy it over to your application and you should be ok. If you don't have copies you may have to create a new make table query.

Sorry, I don't think that's it. When the table is present, the Make table query deletes it as you say it should, and then fails with the same error message. I've tried Make Table queries without the offending Comment field, and if there was no table it does simply create it.

**DaveSlade72** · Jul 12 '07, 03:04 PM

After speaking with someone that is familiar with the source database, it would appear that it is corrupted, and this is what has led to the series of errors.
Running it through JetComp v4.0 has done the trick.

Many thanks for your assistance.

Dave
Cardiff