HELP! Database suddenly gone unstable

[teambond]

Hi,

I have an Access database that runs our Jobs Management. It has a front end and 4 back end database split into customers, suppliers, orders and partslist. It runs in a multi-user environment on a local network.

It has been operating since 1 July 2013 without a single hitch. The database was hosted on one computer in the network and all other computers looked at that via the network.

On 5 Feb 2014 we moved the database onto a Network Access Storage (NAS) device as we have increased the number of computers and the network limitations prevented all computers from seeing each other. It has been an unmitigated disaster for the database!!!

Originally it ran very slowly on the NAS which we ended up discovering was a faulty drive in the NAS. Having fixed that we are now getting daily corruption issues. It had had no corruption issues prior to moving it.

The biggest one appears to be that the multi-user element with the orders. We have previously had several people doing orders at the same time, however it seems to be corrupting now. Today we found the problem below:-

User 1 was writing order 1920. He had finished adding order details for 1 part number.
User 2 was writing order 1921. She finalised her order details for 1 part number.
When User 1 went back looking for order 1920 it did not exist and the order detail he had added had been put in job 1921 and it no had 2 part numbers.

When I looked at the back end tables. In the Order Table there were 2 x job 1921s and no job 1920. Job number is an auto number field in the Order table.
When I went to the order detail table. The part for job 1920 was showing job 1920, however when you query the form it is showing under job 1921.

I compacted and repaired the database and created a new job 1922 and changed the job number in the order detail for 1920 to 1922. That worked fine but now it has made the query that builds the form not allow additions, so I can't wirte any new jobs via the form.

I have had to compact and repair similar issues in the database at least 5 times since we moved the database.

Please help. I am going insane!!

Thanks

 

[Cronk]

Is this autonumber field a unique field? The problem of duplicates should never arise.

Try importing your tables into a new blank database. You might need to import all but the last two problematic records and then use an append query to add the last two records without the order number, letting the autonumber field assign the next number.

Incidentally, I never use an autonumber field for anything which is exposed to the user. In this case the Autonumber field would be OrderID and another unique field to store the Order number.

This of course, does not address the basic issue of the cause of the corruption, which could be anything including a faulty network card on the NAS box.

 

[teambond]

Thanks Cronk,

Do you recommend I rebuild all the tables to try and rectify the corruption? It is not only these 2 records. The Order db has had to be repaired at least 5 times in the last week.

I am intending to rebuild many aspects including the Auto-number in the next iteration of the database due for release in May, but it is a working db that I need to stabilize urgently.

So frustrating... We went 8 months with NO corruption.

 

[bob fitz]

It has a front end....

I think that may be the cause of your problems. With a split db each user should have a copy of the front end on their own machine which is linked to the back end tables.

 

[teambond]

Hi Bob,

There is a front end on everyone's PC. It has worked fine like this for 8 months. It was only once the back end databases were moved to the NAS that the corruption occurred.

 

[Cronk]

Re post #3, importing tables into a blank database will rebuild tables. I recall in the past I've made problems by appending records in the middle of an autonumber sequence.

As to the cause of the corruption, I would look at the NAS box as a likely cause. What else changed from when everything was running OK. Could be the card, maybe the RAID in the box.