Front end corrupting back end..?

[April15Hater]

Hey guys-

Having quite the issue with corruption lately and I don't quite know how to even begin to troubleshoot this problem, yet I've found a way to fix it, so I guess that's a start. Here's the dilemma:

We're running Access 2003 with Windows 2000 on all of the machines. When my users log in to their front-end they get the following message: "Unrecognized database format 'J:\Data\Services Database Data.mdb.'" When I login to the back-end, it prompts me to compact and repair and the problem goes away. Once the users get back in the database after 10 minutes or so the back-end re-corrupts itself and the same message comes right back.

Some database background: Each user's front end is an mde stored locally on their PC which is updated from a server location by their windows login script. So a 'new' copy of the front-end is loaded every time the user logs in. Likewise, a shortcut is copied to the desktop with the following command line: "C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" "C:\DB\CCE Services Inc - Contractor Commander.mde" /user %USERNAME% /wrkgrp "J:\Security\ShareWorkgroup.mdw" which is also updated via login script from a server location. I also have three different front-ends that contain different access objects depending on the user's job function.

My fix for it is to copy all of the table links, queries, forms, and reports to a new mdb file, reconfigure the security and other database attributes, then make it an MDE and replace it on the server and tell everyone to restart.

I did have something unusual happen yesterday that may or may not be a clue to this problem. One of my users, 'APEAVY', was logged in and an error message came up that said "The database has been placed in a state by user 'MKARAPETIAN' on machine 'CAD32' that prevents it from being opened or locked." Both of these users use different front ends and either of them had done anything to write to the database for at least 30 minutes prior.

Any suggestions are appreciated as I am at a loss and don't even know where to start troubleshooting this.

Thanks,

Joe

 

[SOS]

Just some things to look at as I had a problem with one of mine similarly and found out that:

1. The users were entering single quotes in a memo field and that was causing some problems.

2. My users were accessing the same records as others (at the same time frequently) and so I implemented record locking for the selected record so that one could make changes but not others until the original editor left the record. Not my favorite thing as you can have someone lock the record and leave and then everyone else is left hanging. But we managed to deal with that via training.

3. We kept getting a record corrupt about weekly and it had to do with a memo field so we took that field out of the table and put it into its own table. Seems to have helped.

So, I don't know if any of that will help you, but I thought I'd mention it.

 

[April15Hater]

Regarding #2, how did you enable record locking? Was it through ado?

 

[SOS]

No, it was going into the form and selecting the RECORD LOCKS property and setting it to EDITED RECORD.

 

[April15Hater]

Hmmm, none of my forms make use of recordlocking, however, I also don't have anything bound to the forms. Everything is run in VBA with ADO. Would the recordlocking still apply to the ADO queries?

 

[SOS]

I know none of your forms make use of record locking. I meant that I hadn't used it either until I had a problem and then I had to implement it using what I said. And I don't know about the ADO part.

 

[April15Hater]

Thank you for our time and I certainly appreciate your input. At least I have some avenue to possibly diagnose the problem. After I posted it, I realized that ADO actually has a property to set the record locking, so I'm going to try to look at that. Thanks again!

 

[gemma-the-husky]

Just some things to look at as I had a problem with one of mine similarly and found out that:

1. The users were entering single quotes in a memo field and that was causing some problems.

2. My users were accessing the same records as others (at the same time frequently) and so I implemented record locking for the selected record so that one could make changes but not others until the original editor left the record. Not my favorite thing as you can have someone lock the record and leave and then everyone else is left hanging. But we managed to deal with that via training.

3. We kept getting a record corrupt about weekly and it had to do with a memo field so we took that field out of the table and put it into its own table. Seems to have helped.

So, I don't know if any of that will help you, but I thought I'd mention it.

That's an interesting comment. I have just been noticed in a clients database that certain memo fields are showing "deleted". (They reported an update error) The bad records cannot be edited/copied. The database cannot be compacted. The tables cannot be exported.

If you try to manually enter those fields in a table, you get "another user has locked the record for editing." (or somethnig similar - i forget the exact message)

I have been able to add a yes/no field to the table

I can copy all the data on the unaffected rows into a new table
I can copy all the data except the funny memos into the new table on the affected rows.



I wonder if its caused by a similar thing.

 

[SOS]

That was what I experienced. I managed to get it to compact and then went into the table and removed the record (which then didn't have a primary key so it was easy to find).

 

[HiTechCoach]

since you are running Win2K as the worstation OS, my fisrt thoght is that is is an issue woth one of the workstation. I used to have this issue a lot win Win2K.

I would monitor the .ldb to see which pc is causing issues.

also make sure that all pcl's have the same version of jet dll's and MDAC/DAO.

also make sure they are all running the same SP for Office 2003.

uninstall and reinstall office on the suspect pc

 

[April15Hater]

You guys have definitely got me a lot closer to finding the culprit than I was yesterday. I've been monitoring the ldb file, and I found that one user that has been in the DB every time it's crashed. So I looked at his ldb from his mde file and low and behold it is showing corruption. It should say MRIVERA MRIVERA and it says: 前噉剅A††††††††††††牭癩牥a††††††††††††.

I installed the Jet Service Pack, no good. I've updated his mde file, no good. I made a new security login, no good. I'm working on uninstalling office and reinstalling, an maybe that will work. Short of that, I don't really know where to turn. If you guys have any other ideas or have heard of such a situation, I'm all ears. Thank you for all ofyour help.

Joe

 

[SOS]

Have the network guys check his Network Interface Card to make sure it is working properly. That can cause issues and can be a bear to find sometimes. So, if it isn't an integrated card, perhaps they can replace it to see if that works.

 

[April15Hater]

I think I just got to the bottom of it. The reinstall did no good either. What I had to do is give the user a new username/password and voila problem solved. Pretty bizarre issue if you ask me. I'm gonna take a look at the security file and possibly rebuild it. I can't thank you guys enough for helping me out.

 

[SOS]

And thank you for posting back with a solution to the problem. We'll have to try to remember that one for someone to try first so they don't have to go through what you went through .

 

[April15Hater]

Oh it's my pleasure. I can't tell you how many times I've searched to find that someone else posted a solution and solved my issue. I figure it's the least I could do to give back to the AWF community.

Unfortunately however, I'm still in the dark as to why it is happening. I have found a few other users that also have the garbled ldb file yet it is always tied to a single user on a single computer. In fact, there are two shifts; the first shift user may have the garbled file while the second shift is just fine. I'm stumped

 

[SOS]

Perhaps someone is trying to open the ldb file with another program and that is corrupting it.

 

[vbaInet]

My guess is the memo field just like SOS mentioned earlier. If you're interested, here's the translation of that message (after removing the crosses):

Former DAN Dousilaifang

 

[HiTechCoach]

I think I just got to the bottom of it. The reinstall did no good either. What I had to do is give the user a new username/password and voila problem solved. Pretty bizarre issue if you ask me. I'm gonna take a look at the security file and possibly rebuild it. I can't thank you guys enough for helping me out.

I had re-installed the OS, which did the same the as creating a new profile. I did it to clean up a lot to prevent any future issues. It never has another problem.

 

[April15Hater]

I had re-installed the OS, which did the same the as creating a new profile. I did it to clean up a lot to prevent any future issues. It never has another problem.

The OS reinstall does fix it. Our quick little band-aid is to just switch the machine with somebody else's. But I'm hesitant to think it’s something on the profile level because I logged in as a different windows user (which I would assume would use a different profile) but the same db user and the ldf was still corrupt. That makes me wonder if it’s something in the All Users profile, the windows system directory, or the registry that's causing the problem.

 

[HiTechCoach]

I have experienced lots of trouble with Win2K and Access 2000 and later.

When Windows 2000 went bad, it caused lots of data corruption in text and memo fields.

As soon as I switch all machines to Win XP pro, the problems went away.

The best I could determine is this:

Windows 2000 uses JET internally. YES it is true, Windows 2000 actually uses JET (Access) databases!

I discovered this when I tried to run the JET Comp utils on a server without Access installed and it ran. WOW. That means that the JET DLL's were already installed. It was true since the OS uses them!

TIP: Do not compact a database across the network. That is why I was using JetCOmp on the server. JetComp will run on Win2K, Xp Pro, Windows Server 2k and Server 2003. I have done it on all of those OS's without Access installed.

Windows XP Pro (never used the Home version) seams to handle using JET internally with Access also running a lot better than Win2k. For whatever reason, upgrading win Windows XP Pro fixed a lot of issues.

NOTE: I have stopped supporting any Access database running on Windows 2000. Just not worth the expense to my clients.