We help IT Professionals succeed at work.

MS Access deleting indexes and tables when Compact operation is started

171 Views
Last Modified: 2020-11-03
Is it possible that the current version of windows, office 365 (access) and server 2019 are not compatible with virtualized machines???

MS Access (o365) is deleting indexes and tables during compact and repair operations with Win10 Pro.  The front end machine is an i5 win10 pro machine.  BE virtual machine with one cpu and 4 cores.

I can consistently reproduce the problem just by running the compact operation with multiple databases.  No error messages are produced during the process, however, a system table is created when a failure occurs.. I don't remember the name buts its obviously a system table with very cryptic info.. one or two rows and no significant data...

When I use infinity to disable all but one core, the process compacts without errors consistently.

Please tell me there is a better solution than programmatically using infinity!

Thanks
Comment
Watch Question

Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
I just went back and re-read some of your questions.   First, don't forget that with JET/ACE, the "server" is nothing more than a file sharing device.   There's no server side process as far as Access is concerned.

 So the virtualization/cores on the server is not important, unless it is somehow causing a problem with file sharing.

 Please try the following; with one of the DB's that does not C&R properly over the network, copy it to a PC and then do the C&R locally on the original.

In other words, make a copy, do a C&R over the network, and make sure it looses data.  Then take the copy, copy it to the station you just did the C&R on, and do the C&R locally.

 If it ends up loosing data again, then the DB was corrupt to start with.   If it does not, then you have a network issue.

 and BTW, are you using Hyper V or VMWare?  If VMWare, are you using the vmxnet3 adapter?

Jim.

Author

Commented:
@Jim  You are definitely onto something.
Test 1: C&R through network with all 4 cpus: lost 22 indicies and created a mSysCompactError table
Test 2: C&R through network with 1 cpu: no problems detected
Test 3: C&R on local machine with all 4 cpus: no problems detected

We are using VMWare however I do not know if we are using the vmxnet3 Adapter.  I will ask my server guy and report back.
Test 3 indicates it may be a network issue.  Additionally, I do not believe the problem is due to Virtualization or our new Windows 2019 server.  I previously experienced lost indexes during our transition to this platform with the same MDB during C&R.  I was using a Dell OptiPlex workstation with an i5 and we were still on the same windows 2003 server that we have had for 15 years.

So what does this mean?  
We have mostly GB switches.  Our utility closet uses three 16-port GB unmanaged switches (netgear).  The workstations run directly to any of the three switches.  The server also runs directly to one of the three 16-port switches.
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
<<@Jim  You are definitely onto something.>>

 So let's narrow down a bit further.  Do the following:

1. Create a share on one of the stations so it can act as a "server".
2. Put a "good" copy of the BE there (with all of it's indexes).
3. Do a C&R on the same station where you ran the tests in your last comment.

  If that works, then it suggests that the network in general is fine and specifically it's the server that has the networking issue.

  I think the multi-CPU is not related to Access per say, but the way the virtualization is working related to multiple CPUs.   I asked specifically about VMware because I *know* there are bugs/flaws in the vmxnet3 adapter.

  I had one client where we could not run Access across the network and other apps outside of Access would occasionally see errors as well.  Once we turned off all the advanced features on the virtual NIC, the problems went away and I confirmed that with Wireshark.    Before the change, we were seeing a considerable number of TCP/IP resets.  After they all disappeared.

 I never took the time to narrow it down to a specific setting because they still had decent performance even after turning things off and we had spent a ton of time already getting to that point.

Do the test, find out what the server is running, and if VMWare with the vmxnet3 adapter, I'll dig back into my notes on what I changed.

Jim.
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
I never C&R a back-end file located on a network share from my local computer.

I always copy the back-end to my local machine, perform the C&R, and then copy the version that was C&R'd back to the Network.

Joe Anderson (DatabaseMX), an EE expert and Microsoft Access MVP has an application at his place of business which performs nightly backup and C&R operations on dozens of databases.  That process uses this technique as well, and he swears by it.  He does it this way because he has experienced database corruption as well when performing the C&R across the network.

Dale

Author

Commented:
@Jim  No problems running from a drive shared on another computer.
Please remember, however, I absolutedly did experience the same problem with the Windows 2003 server (Before we attached the Virtualized machine to the network)

Author

Commented:
@Dale.. thanks for the suggestion.. the collective size of my BE databases is about 6GB so this approach would take time, although nothing in more time consuming than fixing corrupted databases!
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
That's a lot of data.

Have you considered moving it to SQL Server, although that presents other issues.

You could write an application to do the C&R for you, and then place it on the file server, since that is running full time.  You wouldn't even need to install Office on the file server, just the Access run-time.  Then you could have Windows Task scheduler kick off the application for you at some predefined time of night.

Dale

Author

Commented:
@Dale  @Jim  In addition to the C&R problems, we are also experiencing frequent messages stating the database is corrupt or not an Access database.  Closing and restarting the application always solves the problem.  No compact and repair required.  The difference between the two problems is that affinity=1 does NOT fix the corrupt database problem.
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
That is indicative of a networking problem.

Jim.

Author

Commented:
Pinging the server with -t options shows Latency<1ms and TTL=128 with no lost packets.  Any other test I should run?
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
You could download Wire Shark and use the bad TCP/IP filter, but that takes time in understanding what's there.

 Perform the check I mentioned with another "server".   really need to pin down if it's the network in general, or the server you've been using.

 Really nice that you have a consistent failure (the C&R) that we can check against.

Jim.
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
It has been common knowledge for years that compacting over the network can cause problems due to spurious interruptions during the process.  The recommendation has always been that compacting be done from an account logged directly into the server.  That will be your simplest solution.  An alternative once the size of the BE or the number of concurrent connections exceeds the capability of Access to handle efficiently, is to convert the BE to SQL Server or some other RDBMS, what ever your company prefers.  I have clients who use Oracle, DB2, and other less well know options as Access BE's.  All to good effect.  Converting an Access BE to a RDBMS can be trivial if you have built the app with upsizing in mind.  If the app was built with old style Access techniques such as form filters, conversion to a RDBMS without application modifications will almost always result in slower response than with the Access BE version.  I can convert my BE's to SQL Server in under an hour because of how I build the apps.  When I convert apps created by others, it can take weeks if the app is large and many objects need to be modified.

Author

Commented:
New info... very confusing...

My DC uses wire # 33 to connect to port 13 of switch A which is 75 feet from the server.
The Win7 shared hosting workstation with the shared drive uses wire 32 which goes to port 4 of switch C.

In this configuration the compact error occurred three of three times on the DC and did not show up 3 of 3 times on the Win7 shared hosting workstation.

I swapped wires 33 and 34 and the compact error went away on both systems.
I returned 33 and 34 to the position that previously failed and the problem did NOT come back.  Both systems successfully compacted.

With this said, the problem is not fixed.  We are still getting the intermittent corrupted database message even though the database is not corrupt at all.

I've ordered new cat 7 wires with pre-terminated ends which should be here tomorrow.
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
We are still getting the intermittent corrupted database message even though the database is not corrupt at all.

 Assuming this is still with it being on the server and  I think it's related to the server and the virtualization.  The key is that changing the affinity to one CPU makes the problem go away.

 That suggests to me it's how the host system is handling the virtual guest OS's.

Jim.
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Just because a particular compact operation doesn't have a problem today doesn't mean that it won't have one tomorrow.  Apparently virtual servers are more susceptible to a problem.  You can spend as much time as you want trying to track down this particular error but whatever you discover, don't expect a permanent fix.  Compacting over the LAN has been a problem since I first started using Access back in the 90's.   The larger your database becomes, the more likely you are to have a problem in the future for the obvious reason that more data is being shuffled back and forth giving more opportunity for a drop to interfere.  If your BE is small and your network is solid and fast, you may never have any problem ever.  Most of us are not so lucky.  It is a matter of self protection and problem prevention.

BTW, BE's should NEVER, under any condition, be set to compact on close because they open and close thousands of times each day depending on how many users you have and whether or not they leave the app open all day.

Author

Commented:
Update: I replaced the Cat5e wire that connects from the Server. (DC) and the main switch with a 75 foot Cat 7 wire.  We did not experienced the corrupt database error but still can not C&R through the network without errors unless we set affinity =1

@Jim. Sorry.. I didn't understand.. might there me a server or network setting I can change?

@pat thanks for your comments.. unfortunately copying to a local drive to perform C&R is not a very good option for us.. our nightly maintenance operation, which includes C&R takes about 3 hours... at least 30% is C & R and mdb backup..

@all  I understand some exports have seen problems using C&R over a network but Using XP computers.. Access 2K we have successfully performed at C&R in this same and the database stored on a win2000 server we have performed more that 35,000 times C&R s with DataBase that grow to 1.8GB without ever a single lost index or table. The database are the same.. only the server.. file server.. operating system.. and access version has changed.. ugh
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
unfortunately copying to a local drive to perform C&R is not a very good option for us..
That is not what I recommended.  I recommended running the process ON THE SERVER rather than on a different PC on the LAN.  If you run the process as a task on the server, no data gets shuffled across the LAN.  It all stays within memory on the server.  This is the fastest solution.  I'm pretty sure that CR is a function of Jet/ACE so you would need to install those libraries on the server but NOT MS Access so there would be no Office license consumed for the server.

But copying the BE, compacting it locally, and putting it back will be SIGNIFICANTLY faster than what you are currently doing.  Whatever procedure you are running can be modified to do that.  Just to prove the speed increase for yourself, try the copy, compact, replace steps manually using a copy of the BE so you won't disrupt production work.

Author

Commented:
Bu
But copying the BE, compacting it locally, and putting it back will be SIGNIFICANTLY faster than what you are currently doing.  Whatever procedure you are running can be modified to do that.  Just to prove the speed increase for yourself, try the copy, compact, replace steps manually using a copy of the BE so you won't disrupt production work.  

@Pat.. your message got me excited.. I don't know how to run CR from the server, but if doing so can save time that will be a very good thing.  I did not understand the message above.  I uploaded the code I wrote to do the CR.  

How do I perform function on the server?

temp-ee.txt
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
How are you running it now?  If you are using  a batch file or script, move that job to the server and schedule it to execute between midnight and 4 AM or whatever makes sense for you.

The code you included looks like VBA.  To run an Access app on the server, you MUST have MS Access installed and that requires an Office license.  Most copanies do not like to have Office installed on their servers.  Your's may not care so check with them.  Otherwise, you can convert this to vb Script  You can also look into the tools at www.FMSINC.com  They have one that manages databases that may work for you.

Author

Commented:
Sorry to take so long to get back to this question... other issues got in the way...

My code is VBA (Office 365) and I am using Win10 workstations with a Virtualized Windows 2016 Server,  My databases are on the Win2016 Server.  When I  attempt to compact my databases from a workstation I occasionally lose indexes.  Setting the affinity to  1 seems to fix the problem, but I do not know how reliable this fix will be.

At the end of each day, the staff runs database maintenance from one of our workstations and then goes home.  Since upgrading our software from XP/Access2K to Win10/Office365 we started having this problem.

We temporarily solved the problem by not running a Compact/Repair operation each night as we previously had done, but I don't think that's a good solution???????

@Pat  We don't want to install Access on the Server.  Do you think that is the best solution?  I thought at one point you indicated we might be able to compact through Jet??? Would that required Access?  Currently we are using VBA.
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
<<I thought at one point you indicated we might be able to compact through Jet??? Would that required Access?  Currently we are using VBA.>>

  Yes, you can compact a DB through DAO and do that in VBA.

 However it's still the same issue; the client is doing the processing and it's still going over the network.   To avoid that, you would:

a. Need Access installed on the server, at which point you could just start a scheduled task with the /compact switch

or

b. As Pat suggested, copy the BE to the local machine, compact it, then copy it back.

Jim.

Author

Commented:
We are using Office 365, what program would I need to install on the server, and is that something you would recommend?

I still find it hard to believe that Microsoft has not fixed this... Seems like a MS Office 365 and MS Server 2019 compatibility issue?
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
<<We are using Office 365, what program would I need to install on the server>>

 You would need to install Access.

<<I still find it hard to believe that Microsoft has not fixed this... Seems like a MS Office 365 and MS Server 2019 compatibility issue?>>

  Well it may not be Microsoft's issue. I've re-read this a few times now and there are a lot of different things that have been said, and it's not clear to me what the situation is, so let's break it down a bit.

 Note that I'm on vacation the next two weeks, but let's see if we can't make some progress on this.

 First step is to get the DB running normally with no corruptions or errors to the effect of "this is not an Access database".   This covers the network in general.  You should be able to run normally without issues.

  The first step with that is that there is a Windows bug that impacts Access DB's which Microsoft still has not fixed.  That's covered here:

https://support.office.com/en-us/article/access-reports-that-databases-are-in-an-inconsistent-state-%EF%BB%BF-7ec975da-f7a9-4414-a306-d3a7c422dc1d

  Follow the instructions to disable leasing.   Run with that for a bit and make sure that your getting no errors.

 Then move onto the C&R problem; does it still exist unless you set affinity to 1?    If so, then it's either a problem with the virtualization or a Microsoft bug.

 Again I would move the BE to another "server" with multiple cores and do some C&R's to see if you can get the error.   If not, then it's related to the virtualization.   You'll need to find out what virtual NIC your using, then turn off the advanced features and see if that resolves the problem.  I have some notes here somewhere on what needs to be turned off.

 But if you still have C&R errors after moving the BE to another machine, then we'll turn this into Microsoft so they can look at it.

Jim.
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
The problem with C&R running from the local PC against a BE on the server is that there is a huge amount of inefficient network traffic that is generated and that is what makes it take so long and also what make it vulnerable to network "blips".  The C&R process should always be run ON the same PC where the BE is located so:
1. Run the C&R process from a script on the server.  You should not need Access installed since this is a Jet/ACE process.  Use the compact argument following the database name.  It will look something like:
D:\myaccessfolder\myaccessdb.accdb /compact
I like to rename the original database before I compact so that no one can link to it while the compact is in process.
https://support.office.com/en-us/article/Command-line-switches-for-Microsoft-Office-products-079164CD-4EF5-4178-B235-441737DEB3A6#ID0EAABAAA=Access

2.  Also using a script,
-- rename the BE to prevent anyone from running the app while the compact is in process- I would suffix with the date in yyyymmdd format.  Use time also if you compact more than once per day.
-- copy the BE to the local drive
-- run the compact as shown in #1
-- copy the BE back to the server
-- move the original to the back up folder and keep for a day.

Author

Commented:
@Pat  
Thanks.  I was unable to get your first solution to work, but the second solution looks promising.

As far as your first solution offered:

<<1. Run the C&R process from a script on the server. >>

I could not get this to work.  The command I used was:  E:\shares\mydirectory\myaccessdb.mdb /compact and I executed it as a batch file from the server.  Windows returned a windows asking me to locate an app.  I'm pretty sure Windows was searching for Access or some other application on the drive.

Can the problem be that I'm using the MDB format and not ACCDB??
Can the problem be that the directory on the VM machine is actually under the shares?

I'm beginning to think I need to bite the bullet and use your second solution (as you have said all along).
When you refer to a script I do you mean a BATCH (.BAT) file?  
Do you have any scripts that I can use to perform this operation?  I wrote a script myself (attached) but I'm not a very good batch file programmer.  I don't want to use my script for several reasons:
1. My script does not include any error checking or any mechanism to automatically abort when problems prevent correct operation.
2. MSAccess is not always installed in the same directory on our work stations.  (I'm not sure why).   This will prevent me from creating one script that can be used on any machine.
3. I need to C%R 10 different BE mdb files.  Unless its possible to use  a variable as a file name and loop, the code will be difficult to create, maintain, and debug when things go wrong.



@Jim  
Thanks.  I've read the article you linked to.  It does not seem to be the issue I am having as I do not get the "Database in an Inconsistent state".  The problem I have is that indices which exist before I C$R randomly disappear.

When you say move the files to another server can this other server simply be a multiple core workstation?  If so I can give it a try... but keep in mind we only have one machine on our network running server software.
TempNote.txt
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
I know the error message doesn’t apply but the problem will.

So you should go ahead and do that. Also yes, “another server “  is any other station on the network that can have a file share and serve to host the backend

Author

Commented:
@Jim  Thanks.  I will.

Author

Commented:
I believe I have correctly tested all the suggestions and still have this problem.
My MDB databases still (occasionally) drop indexes during compact and repair.

To be clear.. I am compacting my BE databases and the problem occurs with multiple databases.
After implementing the leasing bug "fix" I stopped getting several other error messages.. such as this is not an access database...

But the dropped index problem persists.

  1. Affinity 1 does not solve the problem.  Setting affinity 1 definitely reduces how often the problem occurrs but does not stop access from dropping indexes.
  2. Copying the MDBs to a LOCAL drive does NOT solve the problem
  3. Using a different workstation does NOT solve the problem
  4. Upgrading from 32-bit to 64-bit Access does NOT solve the problem
  5. The problem exists with multiple BE MDBs.
  6. I have been unable to get a script to run on the server that will perform a compact as I do not have access installed on the server.

Thinks which seem to effect the problem
   Rebooting the workstation then running C&R often allows the MDB to compact correctly
   If C&R fails and I try again on a copy made before the failure the copy often compacts successfully..

Any other ideas? I'm ready to try anything...







Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
<<and created a mSysCompactError table >>

What are the errors in this table?

<<Any other ideas? I'm ready to try anything... >>

I suppose it's possible that even though you may have addressed the issue with affinity or disabling leasing, the DB is still corrupt even though it has gone through a C&R.

I think the only thing to do is get a copy of the DB to Microsoft to find out what's going on with it.

Jim.

Author

Commented:

<<What are the errors in this table? >>
Almost always the same error as stated below. Not much info.

MSysCompactError
ErrorCodeErrorDescriptionErrorRecidErrorTable
-1404Could not find field 'Description'.
ComponentMaster

Corrupt data is not likely..  consider this:
Name of database: Components.mdb, size 1.2GB, tables: 1

Step1: Make two backups: Components.BAK1.mdb and Components.BAK2.mdb
Step2: reboot workstation
Step3: Compact and Repair Components.mdb... drops indexes.. displays error below..

Step4: Reboot Workstation
Step5: Compact and Repair ComponentsBAK1.mdb... operation completes without errors and without dropping indexes.

One more thing.. the dropped indexes occur both when compacting with the mdb stored on the server connected by Ethernet wires... as well as one the local machine.. and the problem occurs on multiple local machines.


What the chance that you can get someone to look at this that will actually do something about it?
Does Microsoft still offer paid support?
Between this problem and the system resources issue I really need to do something.
Of course sending anyone outside the company this data would require a CDA.

Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
<<Corrupt data is not likely..  consider this: >>

 Actually it is.  This is a common problem and has happened throughout the years.   Do you have memo fields in that table?   If so, open it up and do a sort both ascending and descending on the fields.  Look for any odd characters in the fields.  I would do all the other fields as well.

<<What the chance that you can get someone to look at this that will actually do something about it? >>

  If this happens with a current version of Access (2019 perpetual or o365 subscription), then they would probably want to see it even though it's with the MDB format especially given that it is reproducible.   As far as doing something about it, no guarantees, but they might give you a work around.

Jim.
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions