Access Corruption from Multi-User Front End

[Phil5673]

I have an Access 2003 database that I have split into front/back ends. The front end is copied down to the users' hard drive, so everyone has their own copy. In the front end, I have a VBA function that updates a table on the backend. The backend table that I am updating has year-to-date hours for employees, and each copy of the front end deals with different Regions, so everyone is updating a different set of employees. The problem is that when two front ends are trying to update the back end at the same time, I get the dreaded 'Unrecognized Database Format' error. After getting the error in one front end, I can halt processing on it, and the other front end will keep updating just fine. I am using ADO with pessimistic locking. Any ideas on how to prevent this?

Here is the code:

Private Sub UpdateYTD(ByRef rs As Recordset, firstDay As Date)
Dim rsYTD As New Recordset
Dim strSQL As String

On Error GoTo ErrorHandler

strSQL = "Select * from tblYTDHours where social = '" & rs.Fields("social").Value & "'"

rsYTD.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockPessimistic

If Not rsYTD.EOF Then
'update YTDHours in tblMaster
rs.Fields("YTDHours") = rsYTD.Fields("YTDHours")
rs.Update
'don't increment the YTDHours in tblYTDHours if the processing date isn't larger
'than the lastIncrementMonth in tblYTDHours.
If DatePart("m", firstDay) > rsYTD.Fields("LastIncrementMonth") Then
rsYTD.Fields("YTDHours") = rsYTD.Fields("YTDHours") + rs.Fields("TotalPenHours")
rsYTD.Fields("LastIncrementMonth") = DatePart("m", firstDay)
rsYTD.Update
'update the YT in tblMaster
rs.Fields("YTDHours") = rsYTD.Fields("YTDHours")
rs.Update
End If
Else
'if a record doesn't exist for the social in tblYTDHours,add it
rsYTD.AddNew
rsYTD.Fields("Social") = rs.Fields("Social").Value
rsYTD.Fields("YTDHours") = rs.Fields("TotalPenHours").Value
rsYTD.Fields("LastIncrementMonth") = DatePart("m", firstDay)
rsYTD.Update

'since no record existed in YTDHours, just use the total hours for the month
'as YTD hours
rs.Fields("YTDHours") = rs.Fields("TotalPenHours")
rs.Update
End If

ExitHere:
On Error Resume Next
rsYTD.Close
Set rsYTD = Nothing

Exit Sub
ErrorHandler:
MsgBox Err.description, vbOKOnly, "UPdateYTD error # " & Err.Number
Resume ExitHere


End Sub

Phil

 

[KenReay]

Hi

Not really a solution, more a question and observation

Are you using record (or row) level locking?

If the possibility exists that two (or more users) will attempt to update same record at same time you need some code to trap the possible "record is locked" errors and to handle gracefully

Also I note that your code allows the possibility of a .AddNew / .Update followed by another .Update without an intervening .AddNew or .Edit

Also I note from you code it is the recordset pointed to by RsYTD which pessimistic locking, but does not appear to be updated, it is recordset pointe dto by Rs which is updated in your code



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Phil5673]

Ken,
Thanks for the response.

I am using record-level locking. Since every copy of the front-end is limited to a different region, and no employee can be in two regions at the same time, no two front ends should be attempting to edit the same record.

On the .addnew/.edit issue, are you referring to the fact that I don't use .edit prior to .update? I have never used .edit before when I have a recordset open and need to update something. I have always just assinged a new value and used .update - has never caused any problems...should I be doing that? I'm not updating the same recordset twice in a row without changing anything - it always follows the pattern:
rs.fields([field]) = whatever
rs.update
rsytd.fields([field]) = whatever
rsytd.update

Again, I'm explicitly not using .edit before assigning values, but have never had a problem before.

Addressing the last point, rsYTD is updated in the following code:

rsYTD.AddNew
rsYTD.Fields("Social") = rs.Fields("Social").Value
rsYTD.Fields("YTDHours") = rs.Fields("TotalPenHours").Value
rsYTD.Fields("LastIncrementMonth") = DatePart("m", firstDay)
rsYTD.Update


I am curious to know about the .edit thing, thanks.

Phil

 

[Zion7]

Phil, .Edit is not legal in ADO, it has no Edit action, but DAO does.
So, no, you don't need it because I see rsYTD, is an ADO recordset, and I'm assuming rs also.

 

[MichaelRed]

another possability exists. If hte tabls uses an autonumber field, two users (from anywhere) entering records "at the same time' cam cause conflicts, and resultant errors. there is a faq here in these fora re this.



MichaelRed

 

[Phil5673]

MichaelRed,
Thanks for the response. No autonumber field on the table that I am updating. The primary key for the table is a SSN field.