Access 2000 Database corruption 1

[sunnywink]

Hi,

We are currently using Access 2000 database with 5 users over network. It has been working fine for many years until recently where corruption occurs 2 or 3 times a day.

The error messages ranges from "Database is in an unrecognized format. The database does not exist" to jet database engine stopped because you and another user are tring to change the same data at the same time" and "Disk Error" etc.

Often the database can be repaired and we would import all the objects to a new database after repairing. We have constructed a new form too. The database would work fine for some time before error starts occuring again.

I am really puzzled and would like to know the real cause to the corruption. We have now switch to single user and is rather in convenient. Would appreciate some advice.

Thanks all in advance.

 

[JoanneM]

Afternoon Sunny wink


Could I ask a few questions

Did you do any modifications to the database or have you recently changed/ upgraded/ added new patches to OS or the application itself

Or Have you moved/changed server/drives or locations recently.

Jo

 

[natatbh]

Hi sunnywink,

Would also like to know if the amount of data being entered
into your database has grown to larger amounts lately.

Also do you constantly compact your database?

natatbh

 

[psemianonymous]

I recognize the "you and another user" error message. If you are using memo fields to store a lot of text, one of them has corrupted. Just one record. That is what has happened to me twice now. To test what specifically is broken, try importing all your tables into a new database file, and see where it breaks. Once you have the table, you can either manually search for it, do a COPY-PASTE into Excel and see what line it ends on...there's a lot of things you can do. Then when you find the line, copy the information and delete the line itself. Re-enter the data into your database without the memo field's information (because it's long gone already anyway).


For a bunch of specific help on database corruption, see the FAQ below.

 

[psemianonymous]

Blah, the FAQ is in my signature, which I turned off. Here it is:




--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[sunnywink]

Hi all,

Thanks for the advice. We did switch server recently, it is changed to another server with larger disk space by our administrator. Could it be the reason?

Our file size is maintained at about 4MB and did not grow by large amount recently.

We are using a number of Memo fields. In one case, one record in Memo field become ####### after repair. It works alright after we copy and paste from a previous Access backup. Because of the errors we encountered, we suspected too memo field may be corrupt and we did not use the Memo field in later applications. The errors still presist.

In one case, a record duplicate by itself after we receive the error message, even when we have specified in the properties of the fiels for no duplication.

 

[JoanneM]

Do you have security on the database because the problem I had was the database that did not exist was the .MDW that was corrupting
if that is the case I may be able to help

The database is in an unrecognized format again could be referring to the .MDW if it was originally in an earlier version

You haven't by any chance move to an XP OS using 2000 have you, because that was the cause of my problems similar to yours.

regards

Jo

 

[sunnywink]

Hi

Is there a way to determine if it is an unstable network that is causing the corruption?

Movements of the server is controlled by the administrator. We have no access or idea to the process of switching. Do you have ideas on any don'ts or do's in doing a switch of server to ensure the database can run smoothly, or is there any material to read up on this? We can perhaps check with the administrator.

We did convert the whole database from Version 97 to 2000.

Best regards
sunnywink

 

[assets]

Sunnywink, I had similar problem when I had access 2000 on windows95 xp and converting to access 2002 has solved the problem i was having. By the way my database is 17meg, which does not help.

 

[DaemonSaDiablo]

I also had this problem recently, a time billing and costing utility I wrote running off an Access 2000 database.

The solution should not have worked, but did (I love MS, really).

With 15 users and a real time connection, I had to change my frontend connection string to ForwardOnly connection, rather than Dynamic that worked for 3 years.

It not only lets everyone on at the same time with no corruption, but actually works faster.

Hope it helps.

 

[fong]

Sunnywink:

If the corruption happens often after you move your database to a Win2000 server, the opportunistic locking in the Win2000 server is to be blamed.

I had a similar case as you have after I moved my database from a Novell server to a Win2000 server. The solution is to disable the opportunistic locking in the Win2000 file server. MS knew this bug and posted it on Q300216.

You may read MS article Q296264 regarding the opportunistic locking.

Fong

 

[Titan5]

SunnyWink:
After database corruption sometimes I saw duplicate records too even if it specifies as no dupicate. In fact, the primary key mark was gone after the corruption. I had to delete the duplicate records first before setting the primary key again. I wonder if this is from the network instability such as malfunctioning NIC or connection. BTW my data file is about 30 MB. Sometimes I also saw 'Disk or Network Error' message if the backend computer has turned off while the client frontend computer is still running MS Access. That's because the client computer can no longer get the data from the backend. The above is my experience and hope it may be of help to you.

 

[sunnywink]

Thanks for your advices.

Hi Titan,

The syptoms which you mention are exactly what we see too. Is there a way we can ascertain if the error is coming from network instability?

We do not have administration rights to server allocation and it would be better if we are able to pinpoint to the exact problem before going to our adminstrator for help.

 

[Titan5]

Hi,Sunnywink,
The network instability is just my hunch based on the information I read somewhere before and the experience I had with one customer. This customer had 3-computer network. The database has information as to which computer (based on MS Access log-on name) was the last one using data before the corruption. It always pointed to the same computer and it happened about once every 2 to 3 months until after the customer changed to a new computer. No more problem afterwards. The other 80 some customers have had no such a problem.