Corrupt Database Problems........W2K The Culprit???

[Rohdem]

We use several Access 97 databases with up to 30-40 users where I work. They are all split into back and front ends, both located on our server. We have a problem with the back ends of two of our 'high traffic' databases becoming corrupt sometimes 2-3 times a day. Our network is a mix of Windows 95, 98, NT, and two Windows 2000 machines. I downloaded a program that looks at the .ldb header of a database and can tell which user has corrupted the database. 9 times out of 10, the machine that corrupts the database is one of our Windows 2000 machines. Our users on the Win2k machines say that when the database becomes corrupt, the front end usually freezes or doesn't work properly. However, the users on the 95, 98, and NT workstations can usually use the front end without any problems until they close it and try to get back in later. Does anyone know of any compatibility issues between Win2k and Access 97.

I'd appreciate any help/suggestions anyone can give me.

Thanks

Mike Rohde
rohdem@marshallengines.com

 

[calian]

Can you advise the location of the program you use to examine the ldb file? Thanx.

 

[Rohdem]

http://www.microsoft.com/ACCESSDEV/Articles/jetlund.htm

Download the program called LDBVIEW.

There is also a lot of interesting info on jet locking and the LDB header.

Mike Rohde
rohdem@marshallengines.com

 

[jgoodman00]

This is a very interesting thread. Has anybody experienced a similar phenomenon in Access XP?

I have tried using the LDB Viewer, but the db header is always blank? I also could not find the help file attached with this program? James Goodman

http://www.james-goodman.co.uk

 

[pattyrelos]

I got a problem on my database i use the office xp, i try to repair it but in the end it commit an error message "microsoft database engene stop the process because you and another user are attempting to change the same data at the same time" i remove the the user that connects to the database and try to open it again. the same error message appear and now i can't open my database even i make another db and import all the object to the new db still doesnt work....anybody encounter the same please... need help please. i need to fix my db immediately

 

[jgoodman00]

Try deleting the ldb file. It may be that this file has become corrupt & deleting it will resolve the problem.

Please beware that any changes this user made might be lost though... James Goodman

http://www.james-goodman.co.uk

 

[Rohdem]

This microsoft knowledge base article was the solution to our problem. Read the section on opportunistic locking. (oplocks) We disabled opportunistic locking on our file server and the problem went away.

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q303519

pattyrelos, I don't think your problem is related to this. You probably have a corrupt record in one of your tables. The corruption is usually in the memo field, and if you view the record in the table there will be a #Error in the field. You will get the error each time you try and access it. You can tell which table has the corruption by importing your tables to a new database, and watching which table you get the error on. Then you need to cycle through the records (using code or doing it manually) until you find the corrupt record, delete it, then recreate it.

Good Luck Mike Rohde
"I don't have a god complex, god has a me complex!"

 

[Guest_imported]

Hi.

I have had the same problem in the past.

Firstly, let me attack the first problem :

'------------------------------
We use several Access 97 databases with up to 30-40 users where I work. They are all split into back and front ends, both located on our server. We have a problem with the back ends of two of our 'high traffic' databases becoming corrupt sometimes 2-3 times a day. Our network is a mix of Windows 95, 98, NT, and two Windows 2000 machines. I downloaded a program that looks at the .ldb header of a database and can tell which user has corrupted the database. 9 times out of 10, the machine that corrupts the database is one of our Windows 2000 machines. Our users on the Win2k machines say that when the database becomes corrupt, the front end usually freezes or doesn't work properly. However, the users on the 95, 98, and NT workstations can usually use the front end without any problems until they close it and try to get back in later. Does anyone know of any compatibility issues between Win2k and Access 97.

I'd appreciate any help/suggestions anyone can give me.

Thanks

'------------------------------------

OK. What happens in a company is that people always find other ways of doing things other than the way you might. There may be some users whos machines have a "FileType" (see Tools-Options-FileTypes in Windows Explorer) set to Access 97. When an access database is opened, the system looks down the list of filetypes to try and locate the program related to a .mdb file. This is reset by Access every time you run Access.

So, imagine a Access97 database used by 20 users (10 have Access97 & Access XP installed on their PCs & the other 10 have Access 2000/XP only). Lets say that Fred is the former (has 97) and Joan is in the latter (does not have 97):

Fred goes into windows explorer and doubleclicks the database. His system runs through the filetypes and realises that it (due to the last Access database that was run on his system) refers to a .mdb as being opened by an Access 97 database. This is fine. The database opens.

Joan goes to her machine and clicks on the shortcut to the program (make sure that all users have shortcuts to Access Databases - wise move). On her system an mdb opens as a XP/2000 database. The system opens AccessXP/2000 and then tries to open the database. A window pops up saying that it is a "database created with an earlier version of access". It then continues to ask Joan if she would like to convert it to a 2k/XP version. Joan is unsure. Does she ring the IT department to find out why? Are the IT department available, or is it a tuesday? Whatever, she clicks OK. The system recompiles the database to a v2k/XP database.

You will now not be able to open the database in Access 97. Tough!

This has caused me no end of problems. Anyway, the moral of the story is :

1. Find out what versions of access people are using. Do they all use 97? Do they use XP?
2. Check the filetypes of each machine. What version of access is the MDB pointing to?
3. Do people use IT-department-designated-shortcuts (With well defined Targets), and not mess around with them?

Hope that helps with that problem.

The next problem :

'-----------------------------------
I got a problem on my database i use the office xp, i try to repair it but in the end it commit an error message "microsoft database engene stop the process because you and another user are attempting to change the same data at the same time" i remove the the user that connects to the database and try to open it again. the same error message appear and now i can't open my database even i make another db and import all the object to the new db still doesnt work....anybody encounter the same please... need help please. i need to fix my db immediately
'--------------------------------------

Oh yes, I got this problem myself. I actually went to microsoft and asked them what to do. They weren't much help actually but I did end up finding the solution. Yes, it is a "lock" related problem. Here are a few points to check first :

1. Go into the database as administrator and check that all linked tables point to the correct back-end.
2. Check the locks, both on the forms and the tables.
3. What service pack are you using? Check microsoft.com for any that may be available. Look for a file called VB5Cli.exe on microsoft.com. This does help in most cases. Good old microsoft, eh.
4. You may have to change the code to incorporate the following, if this problem still occurs:

Create a table of users with two fields :

UserName text ???
RecordNo Number ??? (depends on the keys in the table that the errors occur on.)

When a user requests an edit (by clicking a button), make an entry into this table :

Username ME
RecordNo 1901 (The current Record Being Added)

No actually, make sure that the entry doesn't already exist in that table (select Count(*) from table where RecordNo = 1901). If it doesn't exist, then you are free to lock it (remember to use RefreshDatabaseWindow() beforehand, though). You could also make RecordNo a Unique Index so that it is impossible for 2 users to enter the same record number in twice.

Shame that these problems do happen. I would also make a point of backing-up the table that has the error, and recreating it (should only have to do this once as the error should not occur again).

Hope this info helps.
Mr Big.

 

[Guest_imported]

Oh Yes, and I would like to point out that the "microsoft database engene stop the process because you and another user are attempting to change the same data at the same time" problem only ever occurs with MEMO fields - try (if possible) using a different type of field.

Mr Big