I am running an Access Database on Access for Office 365. After running it for 5 - 6 years, once a day the system crashes and I have to repair a table. This happens at various times of the day, happens when different users access it but it is always involved with the same table. How do I permanently fix something like this? Any help will be appreciated.
Database crashing
- Thread starter JabberUK
- Start date
I have about 15 users. The tables are in a separate database with the main database linked to the tables in the table database. I run a bit of code where each user clicks and the main database is copied onto their machine which they then use. (still linked to the seperate tables database). This allows me to see who is logged in at any given time. The code below is what copies the database:
Set WshNetwork = CreateObject("WScript.Network")
GetUser = WshNetwork.UserName
Set WshNetwork = Nothing
LUName = "y:\bblive.ACCdb"
'Note: or LUName = "UNC Name"
oldname = LUName
newName = Replace(LUName, ".ACCdb", "") & GetUser & ".ACCdb"
retval = 0
Dim objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")
retval = objFSO.CopyFile(oldname, newName, True)
Set objFSO = Nothing
Dim objShell
Set objShell = CreateObject("Wscript.Shell")
objShell.Run "MSAccess.exe " & newName
Set objShell = Nothing
Set WshNetwork = CreateObject("WScript.Network")
GetUser = WshNetwork.UserName
Set WshNetwork = Nothing
LUName = "y:\bblive.ACCdb"
'Note: or LUName = "UNC Name"
oldname = LUName
newName = Replace(LUName, ".ACCdb", "") & GetUser & ".ACCdb"
retval = 0
Dim objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")
retval = objFSO.CopyFile(oldname, newName, True)
Set objFSO = Nothing
Dim objShell
Set objShell = CreateObject("Wscript.Shell")
objShell.Run "MSAccess.exe " & newName
Set objShell = Nothing
isladogs
MVP / VIP
- Local time
- Today, 19:18
- Joined
- Jan 14, 2017
- Messages
- 19,012
Perhaps some users are instead using the FE from the network and as a result, more than one user is sharing that copy.
Why doesn't each user have their own copy of the FE permanently on their PC?
What's 'special' about that one table? have you checked record lock settings?
Is the BE with the tables an Access database or SQL Server
When it crashes do you get a message about Access being in an inconsistent state?
Why doesn't each user have their own copy of the FE permanently on their PC?
What's 'special' about that one table? have you checked record lock settings?
Is the BE with the tables an Access database or SQL Server
When it crashes do you get a message about Access being in an inconsistent state?
This way, I can see who is in the database at any time. If it crashes or I need to work on it, I can do so without walking around the factory checking which of the 15 users is still in the database. How do I check lock setting? The BE and FE are both access databases. When it crashes, I get the following message "unrecognised database format. Y:\bbtables.accdb
To start with, you could skip to code you've at start up.
Because as I read your post, the database crash several times a day, so it will quickly prove whether it is something in the code that is the reason, then if the database run a whole day without crashing, then it is the code.
Oh yeah I know, you want to see which of the users are on the database, but for a single day, then phone them instead.
If that doesn't stop the database from crashing, then create the problem table from scratch, not by coping it, but manually!
Because as I read your post, the database crash several times a day, so it will quickly prove whether it is something in the code that is the reason, then if the database run a whole day without crashing, then it is the code.
Oh yeah I know, you want to see which of the users are on the database, but for a single day, then phone them instead.
If that doesn't stop the database from crashing, then create the problem table from scratch, not by coping it, but manually!
isladogs
MVP / VIP
- Local time
- Today, 19:18
- Joined
- Jan 14, 2017
- Messages
- 19,012
You can just as easily log who is using the FE without them having to copy the FE each time.
When you repair that table, what exactly do you have to do?
You didn't answer my question: What's 'special' about that one table?
Also how are users accessing it? Direct from the nav pane? Using a query? Form? Report?
Record locks are set in the property sheet for queries and forms
Is anyone connecting to the network wirelessly (including yourself)? If so, corruption is inevitable
Finally - see this link which may be relevant: https://www.devhut.net/2018/06/13/access-bug-database-is-in-an-unrecognized-format/
Ok, please bear with me as some of your advice does fly over my head. I have now set everyone up differently. The main and tables databases are both on our server and each member now has a direct link to the main database over the server. I will see how this works. The table in question is the main table that holds all the information for customer orders. It is used in virtually every form or report we have. One person is out on the road with access via an ipad where he logs onto our server.
Microsoft Access has detected that this database is in an inconsistent state, and will attempt to recover the database. During this process, a backup copy of the database will be made and all recovered objects will be placed in a new database. Access will then open the new database. The names of the objects what were not successfully recovered will be logged in the "Recovery errors" table
Minty
AWF VIP
- Local time
- Today, 19:18
- Joined
- Jul 26, 2013
- Messages
- 10,670
You have actually taken a backwards step. Do not share a common front end database, you will definitely get corruption that way.
I suspect as JHB suggested your user logging is possibly causing the issue, and as Colin has asked, what table keeps corrupting?
Your remote user may be causing the issue unless they are using a remote desktop style connection into your server?
I suspect as JHB suggested your user logging is possibly causing the issue, and as Colin has asked, what table keeps corrupting?
Your remote user may be causing the issue unless they are using a remote desktop style connection into your server?
isladogs
MVP / VIP
- Local time
- Today, 19:18
- Joined
- Jan 14, 2017
- Messages
- 19,012
Agree with Minty
1. Users are now SHARING a common link to the frontend (main database) on the network.
This is worse than before
Each user needs their own COPY of the frontend on their own hartd drive
2. The person on the road is connecting wirelessly. Each time there is any interruption to the wireless connection - even for a split second - the record in use is likely to get corrupted
Its not a problem if you need to ask for explanations about the advice given ...but if you don't answer our questions its difficult to provide real help.
So I'll try once more - what exactly happens to the table - corrupted data / deleted records / something else ... and how exactly do you fix it?
One more thing - can you please upload a copy of the Recovery Errors table generated in your app
The user on the ipad connects to a pc that has remote desktop on it. Microsoft Remote Desktop app for ipad.
The database crashes and the user gets the message ""unrecognised database format. Y:\bbtables.accdb "
I then open the tables database and get this message:
Microsoft Access has detected that this database is in an inconsistent state, and will attempt to recover the database. During this process, a backup copy of the database will be made and all recovered objects will be placed in a new database. Access will then open the new database. The names of the objects what were not successfully recovered will be logged in the "Recovery errors" table
I click ok and it shows repairing...…
then I get the message: Microsoft has recovered this database. Examine the database to verify that there are no missing databases objects.
It then works again until the next crash.
I will now going onto all 15 machines and copy the FE from the server to their desktop and get them to open their own version but the tables database will still be on the server. Is this correct?
Do you need anything else?
The database crashes and the user gets the message ""unrecognised database format. Y:\bbtables.accdb "
I then open the tables database and get this message:
Microsoft Access has detected that this database is in an inconsistent state, and will attempt to recover the database. During this process, a backup copy of the database will be made and all recovered objects will be placed in a new database. Access will then open the new database. The names of the objects what were not successfully recovered will be logged in the "Recovery errors" table
I click ok and it shows repairing...…
then I get the message: Microsoft has recovered this database. Examine the database to verify that there are no missing databases objects.
It then works again until the next crash.
I will now going onto all 15 machines and copy the FE from the server to their desktop and get them to open their own version but the tables database will still be on the server. Is this correct?
Do you need anything else?
Minty
AWF VIP
- Local time
- Today, 19:18
- Joined
- Jul 26, 2013
- Messages
- 10,670
Does anyone (other than you when you are mending it) open the backend database directly on the file share?
How is access to the file share controlled. Are you on a domain, and everyone logs in?
Although not your part of your current problem, long term it might be better to connect to the backend using a UNC path, rather than a assigned drive path. \\YourServer\YourfileShare.... will always resolve, Y: on a new machine won't necessarily.
Sorry lots of questions!
How is access to the file share controlled. Are you on a domain, and everyone logs in?
Although not your part of your current problem, long term it might be better to connect to the backend using a UNC path, rather than a assigned drive path. \\YourServer\YourfileShare.... will always resolve, Y: on a new machine won't necessarily.
Sorry lots of questions!
I am the only one that ever opens the BE database directly.
We have a server (the Y drive) and everyone is connected to that.
UNC paths are way beyond my understanding.
Does anyone know of a company that specialises that could come in and deal with this? We have an IT company but they have no understanding of Access. We are based in South Yorkshire.
We have a server (the Y drive) and everyone is connected to that.
UNC paths are way beyond my understanding.
Does anyone know of a company that specialises that could come in and deal with this? We have an IT company but they have no understanding of Access. We are based in South Yorkshire.
Minty
AWF VIP
- Local time
- Today, 19:18
- Joined
- Jul 26, 2013
- Messages
- 10,670
Okay - since you have moved everyone onto a local copy of the front end, have you had another crash / corruption?
UNC is simply a more reliable method of pointing to a network location. Ignore that advice for the time being.
Can't easily offer help as you're about 400 miles away. Sorry.
UNC is simply a more reliable method of pointing to a network location. Ignore that advice for the time being.
Can't easily offer help as you're about 400 miles away. Sorry.
yes I am getting crashes every 30 mins now. I have tried using an older version of the main database. I have tried exporting the data to an excel spreadsheet and then re importing it and then the database does not work at all. I am out of my depth here as I have tried everything I can think of. Thanks for everyone's help.
isladogs
MVP / VIP
- Local time
- Today, 19:18
- Joined
- Jan 14, 2017
- Messages
- 19,012
Thank you. That was helpful.
MSysRecoveryErrors is a hidden, system table. You need to tick both of these in Navigation options to see the table. Next time it happens, before running the recovery, please do the following:
1. Open the corrupted table and take a screenshot of corrupted data...or make a copy of that table
2. Copy the recovery errors table to a new database so you can upload it
You now have the correct approach for the FE and BE but you need to prevent that user connecting via remote desktop.
If they had a Windows tablet, they could run Access on that with a local copy of the FE and BE. However iPads can't run Access. So the next best solution may be to have a local copy of the BE on their office PC such that when they remote in, the database used has no networked BE tables. That way, at worst, just their local copy will be affected
MSysRecoveryErrors is a hidden, system table. You need to tick both of these in Navigation options to see the table. Next time it happens, before running the recovery, please do the following:
1. Open the corrupted table and take a screenshot of corrupted data...or make a copy of that table
2. Copy the recovery errors table to a new database so you can upload it
You now have the correct approach for the FE and BE but you need to prevent that user connecting via remote desktop.
If they had a Windows tablet, they could run Access on that with a local copy of the FE and BE. However iPads can't run Access. So the next best solution may be to have a local copy of the BE on their office PC such that when they remote in, the database used has no networked BE tables. That way, at worst, just their local copy will be affected
Minty
AWF VIP
- Local time
- Today, 19:18
- Joined
- Jul 26, 2013
- Messages
- 10,670
Firstly, make sure you have a good intact copy of your data, make several backups of it. This possibly sounds like table level corruption.
Firstly try a decompile - instructions here http://www.fmsinc.com/microsoftaccess/performance/decompile.asp
Can you open all the backend tables directly, and see all the records. (Kick everyone out of it at this point) No weird #errror or #deleted records in the tables? That might indicate the table and record that has the problem.
If that doesn't work , try the following - Open a new blank database , and import all the tables into the new database. Compact and repair
Do the same for your front end database.
If not I'm sure someone on here can offer further direct assistance.
Firstly try a decompile - instructions here http://www.fmsinc.com/microsoftaccess/performance/decompile.asp
Can you open all the backend tables directly, and see all the records. (Kick everyone out of it at this point) No weird #errror or #deleted records in the tables? That might indicate the table and record that has the problem.
If that doesn't work , try the following - Open a new blank database , and import all the tables into the new database. Compact and repair
Do the same for your front end database.
If not I'm sure someone on here can offer further direct assistance.
- Local time
- Today, 13:18
- Joined
- Feb 28, 2001
- Messages
- 30,501
Your typical cause for crashing when you have network connectivity issues (and your iPad user might qualify for this one) is not too hard to explain.
Usually the front end opens the back end using SMB (Server Message Block) protocol. This is a TCP-family protocol that involves negotiated port numbers and sequence-numbered transactions. SMB is the standard Windows File-Sharing protocol because Access merely treats BE files as ordinary files, not as something more complex.
When you have one of these crashes, what has probably happened is that your unlucky user tried to update something. If the tables are indexed, any DELETE, INSERT, or an UPDATE that affects an indexed field must write one or the other of the two items first - either the index or the table itself. (They ARE separate.) This is because Access is not multi-threaded. It linearizes its work.
If you get unlucky, you find that one of the two updates worked but the other didn't due to a network failure that split the two operations. So "inconsistent state" MIGHT be something as simple as "index doesn't match table." (There are lots more bad things, but that one is easy to discuss in this context.)
IF that inconsistency is all that happened, you are lucky because rebuilding the index makes the table and index consistent again. Access "knows" if this happened because it leaves behind indicators of what it was doing (so that other user FE copies will know that the particular part of a table is in flux.) If the markers don't go away then Access knows that something is wrong and does the repair.
What I just described is ONE of several possible mechanisms for a recoverable crash due to inconsistent format. There are other issues, such as preparing to update a set of records only to have a crash prevent all of the records from being updated.
Because this is a TCP-family operation, if the network drops, that connection is essentially dead. Remember, it had a negotiated port number and all transactions used sequence numbers. After the network drop, you CANNOT reconnect using the same port and remembering the next sequence number. (Most modern TCP/IP packages will prevent reconnection as a security issue.) So the FE that lost connection cannot try to pick up where it left off. It can't finish the incomplete transaction. OK, so now you have an operation pending and no way to finish it. So Access instead forces you to do a Compact & Repair.
Here's the sneaky part. Access does this C&R by opening a new empty database and copying everything from the old database - but it won't copy inconsistent stuff. So what is left is structurally OK but might be incomplete. Since file locks are specific to the file, this means that the NEW copy (the output of the C&R operation), being a physically different file, has no pending locks. What will eventually happen is that Windows will time-out the broken connections and allow that old version to "go away."
What did that discussion do to help you? I hope it helps you understand one reason why crashes happen. In particular, if there is ANYTHING tenuous about your network, just remember that EVERY network drop-out potentially leaves something only partially updated and thus inconsistent.
Next question: How do you prove what happened? Open the Control Panel on your server machine and get to the Administrative Tools area. Look for Event Viewer. You will see a list of several possible sets of events to review. The System logs might be helpful. Don't be afraid of them, they are cryptic but the viewer will help you understand them.
You need to know the time to approximately the specific minute at which the error occurred. Then use event viewer to review logs at that time. This is easy because the logs are sorted by date/time of occurrence - so it should be trivial to narrow down to the correct time in the log. Usually, events occur no more than 1 minute to either side of the crash time. So if you can find the crash event, look to that which precedes it by no more than a minute.
You can have as many as four or five potential logs from Windows, so don't be afraid to review each log in turn. You might see a "network timeout" event in the log. If so, you will be able to see the IP address of the other side of the failing connection. And that will tell you whodunit.
You can also look at the same logs from the client side by visiting the logs on the machine experiencing the failure first. Again, look at the logs just before the crash, no more than a minute or so, to see if you can identify a causative event.
Usually the front end opens the back end using SMB (Server Message Block) protocol. This is a TCP-family protocol that involves negotiated port numbers and sequence-numbered transactions. SMB is the standard Windows File-Sharing protocol because Access merely treats BE files as ordinary files, not as something more complex.
When you have one of these crashes, what has probably happened is that your unlucky user tried to update something. If the tables are indexed, any DELETE, INSERT, or an UPDATE that affects an indexed field must write one or the other of the two items first - either the index or the table itself. (They ARE separate.) This is because Access is not multi-threaded. It linearizes its work.
If you get unlucky, you find that one of the two updates worked but the other didn't due to a network failure that split the two operations. So "inconsistent state" MIGHT be something as simple as "index doesn't match table." (There are lots more bad things, but that one is easy to discuss in this context.)
IF that inconsistency is all that happened, you are lucky because rebuilding the index makes the table and index consistent again. Access "knows" if this happened because it leaves behind indicators of what it was doing (so that other user FE copies will know that the particular part of a table is in flux.) If the markers don't go away then Access knows that something is wrong and does the repair.
What I just described is ONE of several possible mechanisms for a recoverable crash due to inconsistent format. There are other issues, such as preparing to update a set of records only to have a crash prevent all of the records from being updated.
Because this is a TCP-family operation, if the network drops, that connection is essentially dead. Remember, it had a negotiated port number and all transactions used sequence numbers. After the network drop, you CANNOT reconnect using the same port and remembering the next sequence number. (Most modern TCP/IP packages will prevent reconnection as a security issue.) So the FE that lost connection cannot try to pick up where it left off. It can't finish the incomplete transaction. OK, so now you have an operation pending and no way to finish it. So Access instead forces you to do a Compact & Repair.
Here's the sneaky part. Access does this C&R by opening a new empty database and copying everything from the old database - but it won't copy inconsistent stuff. So what is left is structurally OK but might be incomplete. Since file locks are specific to the file, this means that the NEW copy (the output of the C&R operation), being a physically different file, has no pending locks. What will eventually happen is that Windows will time-out the broken connections and allow that old version to "go away."
What did that discussion do to help you? I hope it helps you understand one reason why crashes happen. In particular, if there is ANYTHING tenuous about your network, just remember that EVERY network drop-out potentially leaves something only partially updated and thus inconsistent.
Next question: How do you prove what happened? Open the Control Panel on your server machine and get to the Administrative Tools area. Look for Event Viewer. You will see a list of several possible sets of events to review. The System logs might be helpful. Don't be afraid of them, they are cryptic but the viewer will help you understand them.
You need to know the time to approximately the specific minute at which the error occurred. Then use event viewer to review logs at that time. This is easy because the logs are sorted by date/time of occurrence - so it should be trivial to narrow down to the correct time in the log. Usually, events occur no more than 1 minute to either side of the crash time. So if you can find the crash event, look to that which precedes it by no more than a minute.
You can have as many as four or five potential logs from Windows, so don't be afraid to review each log in turn. You might see a "network timeout" event in the log. If so, you will be able to see the IP address of the other side of the failing connection. And that will tell you whodunit.
You can also look at the same logs from the client side by visiting the logs on the machine experiencing the failure first. Again, look at the logs just before the crash, no more than a minute or so, to see if you can identify a causative event.
