Record Locking Issue

[carlmack]

I cooked up an Access db some years ago for a small department of 4 users to display summarised stock and sales data through Excel front ends. The size and no. of users have increased over time and it now has 20+ read users and 3 write users (that poke data in from Excel using ADO).

The problem is that I have an intermittent issue on writing (one DELETE and INSERT). It occurs about 1 time in every few hundred transactions although it may happen frequently then not reappear for weeks. The error says it can not get to the table but it does not seem to try for very long. I can not reproduce the error in testing.

99% of all use is through Excel but the db has a few forms that are occasionally (not daily) used for processing and when a user enters the db the frequency of the write errors from the other Excel users appears to increase.

I am confident that the records are not actually locked by any other user ie. the users that write to the db are not trying to write to the same records.

One write user who works at the weekend seems to operate trouble free so user volume is a factor.

The db is compacted every night, sits at about 300mb when compacted and remains very responsive for all the users. It would be a big job to move to SQL because it contains complex forecasting routines with a mixture of Access specific SQL and VBA.

Any ideas would be appreciated.

PS. I am not an Access expert so may have overlooked something simple - more comfortable in SQL.

 

[carlmack]

Hi Pat thanks for the reply.

Yes the users are using a shared (in the sense that it sits on a server) but read only Excel client. Can you explain how this could be an issue. Excel sharing functionality is not switched on. It would be easy to deploy the Excel on the local PCs but it would make future versions more difficult to release.

I am using Excel because they needed a number of charts (often several on a single screen), pivot tables and somewhat complex calculations .... and I found Excel a more flexible platform than using the OWC style parts in Access forms. To be honest I am more familiar with Excel so that maybe a factor too.

Thanks
Carl

 

[carlmack]

Pat,

Thanks for the advice. Like the idea to automate a save locally.

Google found somebody with the same problem who had written a routine to loop round and try again with a short delay when it failed. I am trying that for now but will take the spreadsheets local if that doesn't solve it.

Thanks

Carl