Can't open DB! Super Emergency Pls Help Quick!

[tdw]

This just happened this morning! When anyone, from any computer, tries to open our Orders Database, we get the following error message:

The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time. (Error 3197)


But that's not true! Nobody is on it!
Help! How can I get this to open? We rely heavily on this database, all day long!

[prn]

II can have our IT guy come in and restore Monday's backup copy, then redo all of this weeks changes. It will be challenging to gather all of the info to do this, but it's not impossible. However, if old copies on the server are also not working, I fear that the same may be true after restoring Monday's backup of the db too. Fear unfounded? I hope so.

That's going to be nasty no matter how it goes.It looks like nobody is holding out a lot of hope for a quicker, easier and/or more reliable (than tracking down and re-entering all your data) fix.

The surprising (to me) result that some but not all of the older backups work on either the server or your desktop PC and that the same backups work or don't work regardless of which system they are running on leaves me completely mystified. I have no clue to offer you about whether Monday's backup will run on either your server or your desktop. This sounds completely wacko from where I sit. Restore Monday's backup and if it works, that's great. If it doesn't work then you have some serious work to do. Just don't panic. (I know, that's easy for me to say, but take it slow and easy and make sure you save as much information as possible all the way through.)

If the restored DB does not work, you may have to restore the entire server. I don't have a clue about what else is on it, so that may or may not make any sense at all. What I would prefer to do in that situation, if you can do it, would be to restore your server to a separate system that has been wiped clean. As a small business, you probably don't have a separate spare system to restore to. Hardly anyone does. I don't know about the configuration of your server, but the second best thing to do would be to pull the current disk(s) out of that server (carefully saving them) and put in completely clean disks and restore to them. There is an expense to that, but you can buy 300-500 GB for around $100 these days and if your boss balks at it, just remind him/her what it could cost to lose that data completely. (Hint: you bet your business!)

Whatever you do, be careful!

Good Luck!
Paul

[tdw]

We tried an import earlier. I gather it didn't work

Right. I tried that from the experiment version that is working, when I tried to select the db file as the one I wanted to import from it just gave me the same error message.

[tdw]

That's going to be nasty no matter how it goes.It looks like nobody is holding out a lot of hope for a quicker, easier and/or more reliable (than tracking down and re-entering all your data) fix.

The surprising (to me) result that some but not all of the older backups work on either the server or your desktop PC and that the same backups work or don't work regardless of which system they are running on leaves me completely mystified. I have no clue to offer you about whether Monday's backup will run on either your server or your desktop. This sounds completely wacko from where I sit. Restore Monday's backup and if it works, that's great. If it doesn't work then you have some serious work to do. Just don't panic. (I know, that's easy for me to say, but take it slow and easy and make sure you save as much information as possible all the way through.)

If the restored DB does not work, you may have to restore the entire server. I don't have a clue about what else is on it, so that may or may not make any sense at all. What I would prefer to do in that situation, if you can do it, would be to restore your server to a separate system that has been wiped clean. As a small business, you probably don't have a separate spare system to restore to. Hardly anyone does. I don't know about the configuration of your server, but the second best thing to do would be to pull the current disk(s) out of that server (carefully saving them) and put in completely clean disks and restore to them. There is an expense to that, but you can buy 300-500 GB for around $100 these days and if your boss balks at it, just remind him/her what it could cost to lose that data completely. (Hint: you bet your business!)

Whatever you do, be careful!

Good Luck!
Paul

A thought: maybe the corruption in the backup copies (which are over a year old) were already corrupted on their own, and since we never needed them for anything we just didn't know it; and the fact that all of the versions on the server are corrupt is just a coincidence. Still leaves the question of how the one that counts got corrupted in the first place. I have place a call to the IT guy, and he will come restore Monday's tape backup of the file, and I guess I'll spend the weekend updating it. Thankfully, we keep a rich text doc version of each order on the server, so I can find where the backup ends, and proceed from there. I'll also have to track down which orders are still open and which should be closed. FUN!

[tdw]

Another thought: When you go into the properties for any file, there is an option in there to compress it. If the file thinks it's in the middle of being edited, even when it's not, could I kind of reset that by compressing the mdb file from it's property window?

[prn]

A thought: maybe the corruption in the backup copies (which are over a year old) were already corrupted on their own, and since we never needed them for anything we just didn't know it; and the fact that all of the versions on the server are corrupt is just a coincidence. Still leaves the question of how the one that counts got corrupted in the first place. I have place a call to the IT guy, and he will come restore Monday's tape backup of the file, and I guess I'll spend the weekend updating it. Thankfully, we keep a rich text doc version of each order on the server, so I can find where the backup ends, and proceed from there. I'll also have to track down which orders are still open and which should be closed. FUN!

Yes, the backups may have gotten corrupted on their own, but would they have gotten corrupted independently? That's the really weird part to me. You have different copies of your database from different times and some of the older backups don't run while some of the newer ones do run. I find that really difficult to explain.

It's definitely worth checking the quality of your backups periodically. It's a royal pain, but one of those things like periodicaly cleaning out your desk :) that just needs to get done as normal maintenance. You don't need to check everything on a constant basis, but checking something (especially something critical) every now and then could prove important.

Personally, I suspect that Monday's backup should have started from a file that was not corrupted and copied that to whatever your backup medium is. We don't have any particular reason to believe that your system as a whole was corrupted on Monday. What the quality of the restored file may turn out to be like, though, is a whole different question. And like I said, if the file does not restore to a working state, you may have to restore the system as a whole (or, if your IT guy was clever, just the data:) ). Using a new disk could at least ensure that it is not the disk itself that is to blame. A disk controller or the like could still be the culprit, though. There are enough different plausible single points of failure that eliminating them all one-by-one could be a long weekend.

I'm about to take off, myself now, but I'll try to look in later.

Best Wishes,
Paul

[nico5038]

I would start with reinstalling (or repairing) your office installation.

Next I would check these steps for a corrupted .mdb:
1) Create a backup of the corrupt database. (Just in case of)

2) Create a new database and use File/Get external data/Import to get all objects of the damaged database.

3) Try these Microsoft solutions:
Repair A97/A2000:
http://support.microsoft.com/support.../Q109/9/53.asp
Jetcomp:
http://support.microsoft.com/default...;en-us;Q273956
and/or read the article:
ACC2000: How to Troubleshoot Corruption in a Microsoft Access Database
http://support.microsoft.com/default...b;en-us;306204

4) Bit "heavier":
Access decompile:
http://www.granite.ab.ca/access/decompile.htm

5) Try a recovery tool / Table rescue
Table datarecovery:
www.mvps.org/access/tables/tbl0018.htm
Access recovery:
http://www.officerecovery.com/access/index.htm

6) Ask a company (will cost $$'s ! )
http://www.pksolutions.com/services.htm

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


Nic;o)

[NeoPa]

Another thought: When you go into the properties for any file, there is an option in there to compress it. If the file thinks it's in the middle of being edited, even when it's not, could I kind of reset that by compressing the mdb file from it's property window?

Compression of a file via the file system and Compaction of an Access database are almost completely unrelated. The only thing I'm aware of that they share is that they both cause less of the hard disks space to be used up.
I would say that's a blind-alley.

[tdw]

Compression of a file via the file system and Compaction of an Access database are almost completely unrelated. The only thing I'm aware of that they share is that they both cause less of the hard disks space to be used up.
I would say that's a blind-alley.

Yeah, but lost panicky people often turn down blind alleys and get mugged! I figured that was the case, and was just grabbing at everything I could think of. :-)

[tdw]

I would start with reinstalling (or repairing) your office installation.

Next I would check these steps for a corrupted .mdb:
1) Create a backup of the corrupt database. (Just in case of)

2) Create a new database and use File/Get external data/Import to get all objects of the damaged database.

3) Try these Microsoft solutions:
Repair A97/A2000:
http://support.microsoft.com/support.../Q109/9/53.asp
Jetcomp:
http://support.microsoft.com/default...;en-us;Q273956
and/or read the article:
ACC2000: How to Troubleshoot Corruption in a Microsoft Access Database
http://support.microsoft.com/default...b;en-us;306204

4) Bit "heavier":
Access decompile:
http://www.granite.ab.ca/access/decompile.htm

5) Try a recovery tool / Table rescue
Table datarecovery:
www.mvps.org/access/tables/tbl0018.htm
Access recovery:
http://www.officerecovery.com/access/index.htm

6) Ask a company (will cost $$'s ! )
http://www.pksolutions.com/services.htm

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


Nic;o)

I went through each one of those, and my problem seemed to have been described pretty well, but the solutions were a dead end. The downloadable utility for compacting a mdb that won't open said it encountered an error.
One thing appeared to work though! And that was:
Access recovery:
http://www.officerecovery.com/access/index.htm
the demo brought back all my forms, tables, reports, everything.
Of course, it was not usable because it was a demo. But I know iit would work if I had the $400 to buy the full version! Oh well.

[NeoPa]

Yeah, but lost panicky people often turn down blind alleys and get mugged! I figured that was the case, and was just grabbing at everything I could think of. :-)

Yes indeed. I was just hoping to turn you back before you went too far (into muggers territory) ;)

[Denburt]

I think most of the solutions I can think of have been mentioned although I do have a few ideas that might help.

1. I may have overlooked it but I did not see you post your version of MS Access. What version do you have... That would help us help you.

2: Found a couple of Microsoft articles that mention this error:
http://support.microsoft.com/kb/182867
http://www.microsoft.com/technet/pro...art3/ch16.mspx

3. Still having a problem try opening the database while holding your shift key this will bypass any startup options/forms, Macros etc.
http://support.microsoft.com/kb/826765

4.Backup your database and try using the decompile command line option.
http://support.microsoft.com/kb/819780

5. Create a new MBD file and import everything into the new DB.

This was suggested earlier but I can expand on it since I have had MANY corrupted DB's
Can you start the import process at all? If so what happens does it go part way then hang or crash? If the latter then make a note of the item that it hangs on and try again without trying to import that 1 item. I had one db that had about 4 forms corrupted and the only thing I could do was to skip them then rebuild them.

Hope this helps, let us know.

[Denburt]

I just thought of something else that may help.

Use a command line to compact/repair the database.

Go to the start menu hit run in the run line you should be able to attempt a compact/repair.

Before MS access 2000 the repair and compact are separate commands. 2000 and later the compact/repair are combined into the compact option (use the second option).

msaccess.exe /repair "c:\My Folder\My Database.accdb"

For 2000 and later

msaccess.exe /compact "c:\My Folder\My Database.accdb" 

Article:
http://office.microsoft.com/en-us/ac...666051033.aspx

Here is a nice tip that I use quite frequently it allows you to right click on any MS Access database and compact from there before you even open the DB. It is basically the same as doing the above but simplifies it for later use:
http://www.emsc.nysed.gov//irts/STEP...th_a_click.htm

[MMcCarthy]

I just thought of something else that may help.

Use a command line to compact/repair the database.

Go to the start menu hit run in the run line you should be able to attempt a compact/repair.

Before MS access 2000 the repair and compact are separate commands. 2000 and later the compact/repair are combined into the compact option (use the second option).

msaccess.exe /repair "c:\My Folder\My Database.accdb"

For 2000 and later

msaccess.exe /compact "c:\My Folder\My Database.accdb" 

Article:
http://office.microsoft.com/en-us/ac...666051033.aspx

Here is a nice tip that I use quite frequently it allows you to right click on any MS Access database and compact from there before you even open the DB. It is basically the same as doing the above but simplifies it for later use:
http://www.emsc.nysed.gov//irts/STEP...th_a_click.htm

I didn't know you could do it from the command line. Thats a very handy tip.

Mary

[tdw]

I just thought of something else that may help.

Use a command line to compact/repair the database.

Go to the start menu hit run in the run line you should be able to attempt a compact/repair.

Before MS access 2000 the repair and compact are separate commands. 2000 and later the compact/repair are combined into the compact option (use the second option).

msaccess.exe /repair "c:\My Folder\My Database.accdb"

For 2000 and later

msaccess.exe /compact "c:\My Folder\My Database.accdb" 

Article:
http://office.microsoft.com/en-us/ac...666051033.aspx

Here is a nice tip that I use quite frequently it allows you to right click on any MS Access database and compact from there before you even open the DB. It is basically the same as doing the above but simplifies it for later use:
http://www.emsc.nysed.gov//irts/STEP...th_a_click.htm

My version is Access 2003, but the DB was written for 2002.
I tried the command line, it just very briefly opened access, then closed it and gave me no error message, nor any indication that it was doing or had done anything. Just to make sure, there should be a space before /compact and before "c:\... right?

[Denburt]

My version is Access 2003, but the DB was written for 2002.
I tried the command line, it just very briefly opened access, then closed it and gave me no error message, nor any indication that it was doing or had done anything. Just to make sure, there should be a space before /compact and before "c:\... right?

Good Morning, O.K. 2003, Yes on the space for both. Did you change the command line to locate your database?

msaccess.exe /compact "c:\Your Path\Your Database.mdb" 

It should do more than a brief flash but if that is all you get... How about any of the other options I suggested?

P.S. Glad you liked that Mary hope you find it useful.

[tdw]

Good Morning, O.K. 2003, Yes on the space for both. Did you change the command line to locate your database?

msaccess.exe /compact "c:\Your Path\Your Database.mdb" 

It should do more than a brief flash but if that is all you get... How about any of the other options I suggested?

P.S. Glad you liked that Mary hope you find it useful.

Good morning to you too

Yes I did change the path to locate my database. As far as the other options, I found several articles that mention the error, and they describe the problem well, but most of the solutions presume that the file can be opened in the first place. The suggestions for files that can't be opened aren't working. I have already tried opening another database file to import from the corrupted one, but when I select that file as the one to import from, I just get the same exact error message.

Current status on this:
Our IT has been in today, and he said that he actually has a backup from Wednesday night, so that would leave a day's worth of data that has to be re-entered (plus Friday and today's new orders that came in while the DB was down). Supposedly he got Wed. night's backup restored, but he left without telling me where he restored it TO (I guess he put it in a different location on the server, I've tried to locate it but no luck). I also found a website (www.everythingaccess.com) that I sent a copy of the corrupted file to and they sent a report back saying that they recovered it and it would cost $61.00 to have the recovered copy sent back. So, I'm waiting to hear from the IT guy again to see whether I should use Wed. night's backup, or pay the $61.00 (I would prefer to pay to get the recovered file, but it's not my money).

[NeoPa]

Whatever you choose, always keep all options open until you're finally happy with the results.

[tdw]

Whatever you choose, always keep all options open until you're finally happy with the results.

Ok, nightmare over. We went with paying for the recovered version. All is well, except the autonumber field reset to 1, so I had to make some calcuation adjustments to compensate! :-) Now to get the catching up done!

[MMcCarthy]

Ok, nightmare over. We went with paying for the recovered version. All is well, except the autonumber field reset to 1, so I had to make some calcuation adjustments to compensate! :-) Now to get the catching up done!

Happy to hear it.

I would definitely change the backups to every day rather than twice a week.

Mary

[Denburt]

Glad you are back up and running.

[NeoPa]

Ok, nightmare over. We went with paying for the recovered version. All is well, except the autonumber field reset to 1, so I had to make some calcuation adjustments to compensate! :-) Now to get the catching up done!

Nice one.
Don't delete other versions until ALL issues fully resolved though (just in case).

[tdw]

Nice one.
Don't delete other versions until ALL issues fully resolved though (just in case).

The problem our IT guy ran into was that our Tapeware backup system doesn't work the way it should. We are becoming more and more displeased with that system. It gives lots of problems trying to restore individual files. Our preventative solution for now is going to be that I will copy and paste the database file to another location (my desktop or a memory card or something) every evening before I go home.

Our IT guy believes that the way we use this db file is risky because we have multi-users (not often, but it happens) on it at the same time, 2 or 3 people. He says it's not set up to handle that. Would making it a back-end/front-end system be a solution, or would we have to use an SQL Server (which I don't know much about) to resolve this?

[MMcCarthy]

• Make it a backend / frontend system
• Set default record locking on Access applications to Edited Record
• Remove as many memo fields as possible
• Have a hidden form to force proper close down
• create mde of frontend

These are the obvious security measures although I'm sure others will have further suggestions.

Mary