Microsoft ACCESS database repair frequent repair

1.  is your database application split, where all the users have their own copy of the front-end on their PCs?  If not, this would be your first step.

2.  Are any of your users running your application from a laptop, connecting to the database via a Wi-Fi connection?  If so, they must stop doing so immediately.  Access is very tempermental on connections and any lost connection between the FE and the BE during a read (or especially a write) will corrupt the BE.

@Dale,
(1) Split? Yes Each user has their own copy
(2) Each user runs FE.mdb which has everything but tables from C:\. BE.mdb on the server has only tables. All 4 computers on the network are hardwired on Gigabit network.

I'm not a networking expert, but those are the two primary problems which cause corruption with an Access BE.

Another issue which I have encountered before, although infrequently, is with a network card which is bad, going bad, or with a switch which has gone bad.  But in order to test out that hypothesis you would need to get your sysadmin involved.

Dale

@Dale
Definitely faulty NIC can create problems. I will check Event Viewer for that. They don’t seem to be having problems in running other apps on their computers.

Funny ... I know it comes up, but I have never ever seen an NIC card go bad.

**

Select allOpen in new window

And I assume all users are experiencing this issue from their Front End ?

If so ... that would mean all NICs were going bad ...
Also, I've never seen 150MB or anywhere near it be a problem per se.

I would suggest creating a brand new blank MDB (ACCDB) and then Import all of your tables into that DB ... and see what happens.
If the problem persists, you would have to look elsewhere ...

@Joe
Once the database becomes inaccessible, yes no one can access it until I repair it. Yes and I agree that 150mb size is not necessarily the cause.
I have repaired access database because of corruption but I have not seen the corruption occurring on a daily basis and this message is different too.
I think it’s a good idea to try to re-create a brand new BE database and import the tables.
I will do that tonight and see how it goes tomorrow.

"Once the database becomes inaccessible, yes no one can access it until I repair it. Yes and I agree that "
Well .... what I was wondering is ... let's say it's working ... and someone other than yourself attempts to 'access' the DB.  Do they sometimes encounter the corruption ?

Is there an IT dept that controls your Server?
If so ... has there been ANY changes whatsoever to the overall Server/Network ?

I developed that Access database and also network admin. There are 4 users on the network and they all connect to the database when in the office.
There has not been any change on the network.

What I have not looked into yet is  integrity of the tables with respect to relationships or compacting the FE.mdb on workstation computers.

This kind of corruption seems to related to the design of your Application...if compact/repair and decompiling  doesn't resolve the issue then you have to take a look at the work flow of the application...in the past i had a nasty case of corruption due to the fact that users used the same record for test entry..so you need to "monitor" along with the users when does this happen and if there is a commοn point .

@John
I did two things:
(1) Renamed current database, Created a new database, and imported all tables from old database.
(2) Performed "Compact Database" on FE.mdb on a particular workstation PC.
We will see how this new database survives a day or two.

Hello Sglee,

I will suggest you to make a copy of your database and repair it by using professional Access database repair software.

<< What are the areas that I can look into to find out the root cause of this issue?>>

 The problem is that there are a wide range of issues that can cause a corruption of a MDB file.  For example:

1. Hardware problems (NIC's, network, disk, etc)
2. Users hitting Ctrl/Alt/Del and killing Access.
3. Anti-virus trying to "repair" the file.
4. Bugs in Access itself.
5. Running a wireless connection between the server and client station (wireless connections have high latency at times).

 Why can all that impact a DB file?    

 With the JET/ACE database engine, an instance of the database engine runs on each client station, and they all manipulate the DB file.  The DB file on the server is simply shared and there is no one central process taking care of the DB.    It's like having all of the cooks working in the same pot all at the same time.

 Contrast that to SQL server where you have one instance of the database engine running on the server and clients only request operations on the DB.   It's then the database engine that performs those operations and it's the only one working with the DB file.  If one of the clients just disappears, there's no problem as the DB engine can roll back changes and put things back to the way they were.

  So any type of abnormal disconnect or action by a client can cause a problem with a MDB file or through outright bugs in Access.

<<We will see how this new database survives a day or two.>>

   It is possible that the Compact and Repair did not fully repair the DB.   So it's good you went the route of importing everything into a fresh DB container.

   And if your not already keeping daily backups on a regular backup cycle, make sure you do so.    At the very least, I would be doing  a son, father, grand father cycle (daily, weekly, and monthly).

 Keep us updated.  

Jim.

@ John re "This kind of corruption seems to related to the design of your Application.."

Note the OPs original statement:

" I have an ACCESS database which has been used for years without a problem"

So I doubt it's a design issue per se.

Well @DatabaseMX simple little things can cause great havoc...
I remember the day i just accidentally used a .dll with a minor revision change ....something like version xxx.1655 to version xxx.1662...
Suddenly all hell broke loose...some users could work..some other don't...the application was crashing all the time and my coworkers just couldn't opened the application ....it hit me really hard to find out that an almost identical .dll could cause such damage...so to me anything is possible...
Also another day i was designing a nice cool application...the code seemed right...the application worked just fine...but it would crash after 2-3 runs....i refactored a bit the code...moved some code here and there...voila problem solved...the application was rock solid...

<<just fine...but it would crash after 2-3 runs....i refactored a bit the code...moved some code here and there...voila problem solved...the application was rock solid...>>

 That's an Access bug or a corrupt VBA project file.

 There's really nothing you should be able to do in your app that would cause Access to crash outside of API calls to external libs.

Jim.

@ sglee re "We will see how this new database survives a day or two."
Please do report back.
thx

It crashed again. The survey result ( as to what each user was doing at the time of crash) revealed that Invoice Duplicate button seems to be triggering the crash.
It is an Invoicing system (invoice main  and invoice transaction detail tables are two main tables) and I created a Duplicate button so that existing invoice information (invoice main & transaction detail records) is automatically duplicated so that the user simply edit newly duplicated invoice. This reduces data entry.
Three users are, I learned, using Duplicate button heavily throughout the day.
Now this has been in practice all these years and I have not changed the Duplicate related VB code for years.
Today, I am going to compact everyone's local copy of FE.mdb file.
The posted screen shot shows up when I open BE.mdb on the server.

This is what pops up when I open BE.mdb from the server right after database crash.
928A9A40-EC03-4BF8-83D6-3A883028FCC9.png

I would also Decompile each users Front End as well.
Are you familiar with Decompile ?

Jim has a great article on Decompile ... he can point you to it.
Meanwhile ... I've attached How To.
Be SURE ... you make a backup copy of db first .... simple.
Windows Explorer .... select Db ... Control C, Control V ... instant backup!
aaQicPostDecompile.txt

@Joe
I am not familiar with Decompile, but will read upon.

Given the information so far, what do you think it is happening?

I forget to mention that users click Duplicate button from 30 to 100 times a day according to the survey.

Since it was working before, hard to tell.
So ... kind of one thing at a time.
Decompile solves a lot of strange issues, especially with VBA code.  Decompile is quick and simple. The procedure I posted is what I have found to be the most reliable over the years.

If you have not recently done a Compact & Repair on your FEs ... then they could be suspect.
Do that first and see if resolved.  If not, try Decompile ...

"Do that first and see if resolved.  If not, try Decompile" --> Agree. That way I can see what method solved the problem.

exactly.  Read up on Jim's Decompile article ... because sooner or later ... you will need it :-)
I decompile (and recompile of course) all db FEs before deployment to be sure they are in pristine condition.

Got it.

IF ... it turns out the FE(s) are the problem ... there is a solution to prevent (or greatly reduce) that from happening again.  Keep us posted.

I will, but I have a feeling that compacting FE.mdb alone on each workstation PC won't cut it.

TBD

As Joe mentioned, unfortunately it is the process of elimination.

One of the first steps is figuring out which station(s) are causing the MDB to corrupt or at least have the repair flag set.  

You can write your own code to do that:

https://support.microsoft.com/en-us/help/198755/how-to-determine-who-is-logged-on-to-a-database-by-using-microsoft-jet

or use the utility here:

http://access.mvps.org/access/modules/mdl0055.htm

Note that I haven't tried that utility in a long time, but it should work and if not this one, then we can find another (there are tons floating around out there).   What you want to look at is the suspect flag.  This will be the station that caused the MDB to be flagged as needing repair.

 After this happens a couple of times, you will find that either it's more than one or it's a single station every time.   That tells you were to look and generally for what.

 Since this has been running fine for years, something has changed and it's just a matter of figuring out what.  

 So in the meantime, ask lots of questions and start compiling a list.  Any changes on the server?   in the network?   Any software updated?
 Did anyone get a new station?    Do you have new users?   Talk with the users and make sure no one is having any type of issue.

  In the past I've gotten "well my station reboots sometimes, but I wasn't using Access at the time".   Yet they had an Access app open and it was processing in the background.   Sometimes they just don't realize that one thing ties to another.   Of course the big thing when working with users is not one of placing blame, but rather "just trying to solve the problem".   You don't want them becoming defensive.    I always make it clear that in the process of using an app, there is nothing they should be able to do to cause a problem, so it's not their fault.  Of course that's not entirely true (Ctrl/Alt/Del does work well<g>), but for the most part it is.

 The other thing I'd check right off is that anti-virus and/or behavior monitoring software is not messing with the DB file.  

 and I believe you said no one is on wireless, but double check that.    Sometimes it gets switched on for a station without someone realizing it.

Jim.

You also could check network health by either running a continuous ping with the built-in ping command or a logger (lots of ping logging utilities out there), or setting up monitoring on the server checking for dropped and fragmented packets.

Jim.

Update:
Crashed twice today so far. First at 7AM when there was no one in the office except ONE user and Second time  about 1 hour ago when there were 4 regular users.
I checked the server and I see//' No Internet Connection" in task bar even though I am connected to the server using Remote Desktop from outside of the network.
I started PING test from the server to this particular workstation and running another PING test from that workstation to the server.
I have seen "No Internet Access" on other computers in the past, but there was no problem accessing Internet.
But I don't remember if I saw this message on this particular server and wonder if this may be the problem???

Update on PING TEST:
I performed PING tests from a few workstation PCs to the server and other workstation PCs.
As I suspected, MIKE's PC had a lot of "Request timed out." no matter what computer it was pinging. Furthermore, "Request timed out." occurred twice within 10 consecutive Pings in more than one occasion.
Would this be enough to corrupt the ACCESS database?
Ping-Stat.doc
ft1-1.txt

Is your server virtualized ? if you host many servers on one physical host probably there is some load on the other VMs causing network drop outs...

File Server is virtualized along with 3 other VMs. However workstation computers are physical PCs.

If your physical server has more than 1 NIC assign 1 NIC only to the file server

Dell T320 came with two NICs. However Only 1 NIC is being used.

Based on the ping results, I think there is a problem with  Mike’s workstation PC. So I rebooted the PC and started a ping test again.

<<Furthermore, "Request timed out." occurred twice within 10 consecutive Pings in more than one occasion.
Would this be enough to corrupt the ACCESS database?>>

 Yes, it would be enough if it happened at the right times, although to be certain, you'd have to dig a little deeper.

 On a local LAN, you should be seeing consistent pings < 10 or 15 ms at most, and mostly it should be < than 5, and usually it's <1

 JET/ACE is good up to about 30ms.  After that, you can start having issues.   This is why it doesn't work over a WAN.

 Looking at your log, it does seem fairly good overall, but if he was the only one active while you were doing this, then the request timeouts are a concern.

<<Based on the ping results, I think there is a problem with  Mike’s workstation PC. So I rebooted the PC and started a ping test again.>>

  It's more likely the server.   As John said, if you've got two NICs, use them.   One should be assigned to the physical host and the other to the VM's, or they should be teamed.

  But again,  go back to thinking about anything that has changed recently.  ie. has work load increased?   did you add some new business function to the server?  update of the hosting software? etc.    You may have crept into this over time, but from what you've said so far, it seems rather sudden.

  I'm out here on ten days vacation, so your not going to see a lot of replies from me after this.

Jim.

" This is why it doesn't work over a WAN."
In most cases :-)

Happy vacay Jim. Well deserved I'm sure !

Since I restarted Mike's computer, PING test did not show any failure and the database has not been crashed for a day and a half.

Sooo ... what did you actually fix?  Loosing track ...

I like to watch the database for a few more days to confirm. But no crash for the past two days was an encouraging  sign.

I will report back next week.

The user’s printer driver may have been crashing the database. Since the user stopped printing invoice from his computer, Access database has not crashed.