Weird table "corruption" occurring. Thoughts? (1 Viewer)

[DiverGuy]

I put a split database (created in 2000 format using Access 2003 SP2) on the network three weeks ago. Three times the entries in two of the columns have been changed for every record, essentially making the data useless. I have not been able to narrow it down to a specific event that is causing it in order to program around it. Any thoughts would be greatly appreciated. Here are the details:

The table involved is called tblTitleLog. It contains 13 columns, the first of which is an autonumbered ID field. Column 7 is called CompletedCSA. Column 9 is called CompletedDate. The data in those cells could be different, from record to record, or could be the same as several records on either side of it. The data in those two cells can be updated either of two ways:

• On a per-record basis using a form that contains controls tied directly to the cells for a given record, or
• On a form that incorporates a multiselect listbox and an Update button. This form is specifically designed to allow batch updating of those two columns.

On three separate occasions, the data in those two cells suddenly changed for all 6940+ records. For example:

This:
CompletedCSA CompletedDate
Timmy 2/2/2008
Bobby 2/5/2008
Joey 2/10/2008

Would suddenly change to:
CompletedCSA CompletedDate
Bobby 2/5/2008
Bobby 2/5/2008
Bobby 2/5/2008

No warning messages about multiple people trying to change the same record(s) is displayed. None of the users have been able to spot a trend in something they were doing when it occurred. The code for the Update button on the batch update form only changes the records with the ID of the SelectedItems.

This is the code from the Update click event on the BatchUpdate form:

    For Each i In Me.listboxTitles.ItemsSelected
        strWhere = strWhere & Me.listboxTitles.ItemData(i) & ", "
    Next
    
    If Len(strWhere) > 0 Then
        strWhere = "WHERE ID IN (" & Left$(strWhere, Len(strWhere) - 2) & ")"
    End If
    
    strSQL = "UPDATE tblTitleLog " & _
          "SET tblTitleLog.CompletedCSA = '" & Me.cboCompletedCSA.Value & "', tblTitleLog.CompletedDate = '" & Me.txtCompletedDate.Value & "' " & strWhere

    CurrentDb.Execute strSQL, dbFailOnError

The controls on this form are all unbound.

Duplicating the same actions the users reported just before it happened didn't seem to make a difference. So far, I haven't been able to duplicate the conditions. Management is not happy and I'm frustrated. Any thoughts or suggestions on how to prevent this from happening again?

Thanks,

DiverGuy

 

[KeithG]

You could use a transaction on the update query. This way you can see how many records will be affected. If it is not the correct number you could rollback the changes or if it is the correct number you could commit the the changes.

 

[gemma-the-husky]

when you say you have split the dbs, have you given each user a separate front end?they shouldnt all be sharing the same front end

 

[DiverGuy]

You could use a transaction on the update query. This way you can see how many records will be affected. If it is not the correct number you could rollback the changes or if it is the correct number you could commit the the changes.

That's an interesting workaround. Could work. Can you provide an example of how to wrap it in a transaction? Have only heard about that before and never tried it.

 

[DiverGuy]

when you say you have split the dbs, have you given each user a separate front end?they shouldnt all be sharing the same front end

Yes. They've all been given a separate front end. Worth double-checking, though. Would that be enough to cause such a corruption?

 

[DCrake]

Simple Software Solutions

Hi

Are you using a wireless connection to the back end? If so Access is nototious for corruption in this state. A simple nano second losss in connection can cause major problems.

David

 

[DiverGuy]

Are you using a wireless connection to the back end? If so Access is nototious for corruption in this state. A simple nano second losss in connection can cause major problems.
David

Interesting. No, these are all hard-wired connections.

 

[KeithG]

Look up the workspace object in vba help there should be an example.

 

[DCrake]

Simple Software Solutions

Ok, so you have a wired conection.

When you run the .execute I suggest that for auditing puproses or until you can find the source of the problem is to create additional fields in your table that hold the following:

ComputerName
UserName
Date/Time Stamp
BatchProcess

And when you run the query include the above to track when a record was last updated, who by and on what computer. The Batch Process field will be boolean and should default to Yes if updated via the routine you provided.

Should the error occur then examine the new fields to see if there is a trend. Also if the Batch Process is false this will give some indication that what is going on is not associated with your code, but is being carried out by some other process. Malicious or not.

You are now entering the realms of forensic auditing and cleansing.

David

 

[DiverGuy]

You are now entering the realms of forensic auditing and cleansing.

Oh goodie.

Okay, here's what I've done so far (and, in retrospect, probably should have done from the beginning):

The update form properties have been changed to lock the record(s) selected.

The code behind that form now counts the records selected and reports that number in a message box that asks for verification on the number of records to be updated. If the number is not correct... for example, 6000 instead of 15... the user clicks No to abort the update process. If the user clicks Yes, the fields in the SelectedItems are updated, as requested, and the user name, computer name, and date/time are passed to three new fields in the table.

I'm hoping those changes will be enough to keep it from happening again. But if it does, at least we'll have a clue as to who did it and when.

Thanks for everyone's thoughts and suggestions.

John

 

[gemma-the-husky]

as your data is just wrong, and not corrupt, look your code again

are you sure a user isnt inadvertently selecting every item in thel ist box - or does it work wrongly if no items are selected