Solved

access database issue

Posted on 2014-04-28
18
684 Views
Last Modified: 2014-07-19
I have a client that recently upgraded from Windows XP to Windows 7 64 bit workstations. They are running Office 2007 Pro with SP3.
After the upgrade their access database program started failing and giving the following error message:
"Microsoft Office Access has detected that this database is in an inconsistent state, and will attempt to recover the database.
I am not sure why this is happening. Any ideas?
0
Comment
  • 10
  • 3
  • 3
  • +1
18 Comments
 

Author Comment

by:williamstechnologygroup
Comment Utility
Here is the full error message: Microsoft Office has detected that this database is in inconsistent state and this database 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 that were not successfully recovered will be logged in the ?Recovery Errors? table.
The database itself is on a network share (windows server 2012). It was working fine when we were running XP on the desktops. Nothing changed except for the workstations
0
 

Author Comment

by:williamstechnologygroup
Comment Utility
already tried this:
Putting a database on a server is asking for trouble if it also has the forms,
queries, reports, modules and macros. These objects corrupt easily over a slow
network or when multiple users are in the database.

Best practice is to split the database using the Database splitter under the
database utilities on the menu. Be sure to do it on your local copy. Once the
database is split move the back end (the file with just the tables) to the
server and give each user a copy of the front end. best of all, if a user does
manage to corrupt his copy of the FE you just have to give him a new one and the
data remains safe.
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
Comment Utility
The version of Office 2007 - is it 32 or 64 bit. The 64 bit version (of Access) is notoriously difficult to use.

If the 32 bit version. I'd look to uninstall and reinstall - making sure you select Run All from my Computer.


Kelvin
0
 
LVL 84
Comment Utility
So you have split the database, and you now have the Data portion (i.e. the "Backend") on the server, and the UI portion (i.e. the "Frontend") installed directly on each user's workstation? They're not running the Frontend from a network share?

The error you're getting generally means corruption, so have you performed maintenance on the database?

If not, then first make a backup and then do these things:

1. Compact the database
2. Compile the database - from the VBA Editor click Debug-Compile. Fix any errors that are found, and then continue doing this until that menuitem is disabled.
3. Compact the database

You may also need to Decompile it. To do that, create a shortcut with this as the Target:

"full path to msaccess.exe" "full path to your database" /decompile

Run this shortcut, then do the 3 steps above

You might also consider moving everything over to a new, blank database. This takes care of any container corruption.

Along the lines of what Kelvin suggested - be sure that ALL machines are fully up to date regarding Office and Windows. Check the Windows Update history to be sure that you don't have  bunch of failed updates.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
I'd check anti-virus settings and make sure their not getting scanned.

Jim.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
I think I'd also do some spot checking on the network (ie. dropped frames).   New stations means new NIC's and probably a jump up to GB/sec speed.

 If this is an older network in place, is the wiring Cat 5E or better?

Jim.
0
 

Author Comment

by:williamstechnologygroup
Comment Utility
Thanks Guys,
We did try rebuilding the database from scratch, but once it was live for a day or so we got the error message again. I am going to check AV settings and reinstall the access database
0
 

Author Comment

by:williamstechnologygroup
Comment Utility
cabling is cat5. We did not have any issues until Windows 7 was rolled out. I did install SP3 on all of the Office 2007 installs. We are running the regular 32bit edition of office 2007. I have never attempted to run a 64bit version of an office package.
We are running office 2007 on Windows 7 Professional 64 bit O/S
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Hum...well problem is, it could be just about anything.

Just to be clear, it is already split (a "Front end" on each station with a shared "back end" on the server) or no?

If not, that certainly would be step #1

But even so, corrupting every few days, I would lean towards the environment in some way.  With that said however, I have had clients with 2007 where the DB corrupted frequently (once every couple of months) and I'm pretty sure it was 2007.

I think for the present, I'd do a couple of things:

1. Check station and server event logs for errors.
2. See if the current NIC drivers allow a power down of the card.  If so, disable that.
3. See if a testing protocol is installed for the NIC.   It may not have been with XP, but now is with Windows 7.   Most of the bugs with these have been ironed out, but if it's there, I would disable it just to be able to cross it off the list.
4.  Is there a .LDB file getting left behind?   If so, looking at it would tell you what station is corrupting the DB.

 That's all I can think of for the moment.  

 actually, any XP machines still left in the environment?   Always seems like places with a mixed environment has problems.   Microsoft had a number of issues with SMB 2.0 when it first came out and I don't know if all those problems were ever ironed out.

Jim.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:williamstechnologygroup
Comment Utility
My responses below...

Hum...well problem is, it could be just about anything.

Just to be clear, it is already split (a "Front end" on each station with a shared "back end" on the server) or no? ---  YES

If not, that certainly would be step #1--- ALREADY DONE

But even so, corrupting every few days, I would lean towards the environment in some way.  With that said however, I have had clients with 2007 where the DB corrupted frequently (once every couple of months) and I'm pretty sure it was 2007.

I think for the present, I'd do a couple of things:

1. Check station and server event logs for errors.--- DONE
2. See if the current NIC drivers allow a power down of the card.  If so, disable that.--DONE
3. See if a testing protocol is installed for the NIC.   It may not have been with XP, but now is with Windows 7.   Most of the bugs with these have been ironed out, but if it's there, I would disable it just to be able to cross it off the list.-- NONE FOUND
4.  Is there a .LDB file getting left behind?   If so, looking at it would tell you what station is corrupting the DB.--- NO

 That's all I can think of for the moment.  

 actually, any XP machines still left in the environment?   Always seems like places with a mixed environment has problems.   Microsoft had a number of issues with SMB 2.0 when it first came out and I don't know if all those problems were ever ironed out.--- NO XP MACHINES LEFT
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
Comment Utility
For what it's worth, I've used Access 2007 on an Win 7 Prof 64 bit PC as a development environment since mid 2010. No real issues, and there were XP based machines in the network. Apart from the usual hassles with Access 2007, there's been no real issues. I do find it is "easier" to corrupt 2007 databases than many other recent versions (have not tried 2013) - but that could just me a perception rather than fact.

Having said the above, we use Access as a development tool rather than in production, but do ship runtime versions of our database worldwide (over 100 sites with many, many users). We do, however, have either SQL Server or Oracle for backend databases - not Access.

Kelvin
0
 

Author Comment

by:williamstechnologygroup
Comment Utility
Update: We are going to attempt to convert to Access 2010 and rebuild the database, will keep you updated....
0
 

Accepted Solution

by:
williamstechnologygroup earned 0 total points
Comment Utility
Well,
We finally figured out a solution. I used the DB Migration wizard built into Microsoft Access to covert the back end tables and data structure to SQL and kept the front end program in Microsoft Access. Each user has a full copy of the Access front end and it is working great. The only downside is if there are additional changes, queries added, etc.. the Access front end needs to be updated/copied over to each user. But that is not a problem as it is only about 6 users.
Thanks for all of the input.
0
 
LVL 84
Comment Utility
That's not really a "fix", but rather an environment change - sort of like complaining that your car tires keep leaking air, and then buying a new car instead of fixing the leak. Buying a new car stopped the leak (in a way), but it didn't really fix the original problem (since the old tires still leak).

Not that I'm complaining, and I have no issue with the closure, just wanted to note this for others who come across this issue. The most common issue with the symptoms you report is corruption, and the most common cause of corruption is faulty network hardware.
0
 

Author Comment

by:williamstechnologygroup
Comment Utility
I appreciate your comments and feedback. I guess the amount of time I was spending on this issue was not acceptable to the client. I had to find another way around the issue. This client has top of the line equipment, a brand new Dell VM host, switches, etc.. The only thing that changed with the environment was we went from Windows XP workstations to Windows 7 workstations. Same network, same version of msoffice 2007. After researching and reading the posts regarding issues with Access databases shared over a network- I decided it's best not to fight this, but come up with a different solutions. I contacted a SQL programmer I knew- I expected him to propose we rewrite the app in SQL. To my surprise he gave me this as a solution based upon the size of the application and number of users. I was never a fan of MS Access for a shared database in a business network environment. To me it has always been more of a "home user database to track your record collection vs. SQL which is more for business application. I have run into other businesses who had a jr programmer who knew MSAccess write a database and then the person left and the business was stuck with this database that few people would support and they always had issues. I have another client who really needed to be using MAS90, but instead tried to cobble together a system using Peachtree accounting and MS Access to run their engineering business processes. We have had all kinds of network issues, slowness, when this program is running.....They brought in a person who was very familiar with MS Access and spent a lot of money trying to make it work, optimizing the db, etc.. when they really should have been using MAS90 to begin with.
As far as the points go, I am not sure if I should have awarded points to all of the people who responded even though there suggestions did not solve the problem or if that is not keeping with the integrity and rules of this site. I have posted questions before and have gotten good solutions, I am not disappointed at all with this post--- the people were very willing to help, I did try a bunch of things, but nothing worked. I did my due dilligence, but had to come up with another solution.......
0
 
LVL 84
Comment Utility
As I mentioned I have no issue with the closure, just wanted to be sure that someone who ran across this understood that the "fix" is not always to upgrade the database to SQL Server, and in fact in some cases this can cause as much trouble as it fixes.

To me it has always been more of a "home user database to track your record collection vs. SQL which is more for business application.
That's a common misconception, but not without some merit. I've seen some very elegant solutions designed with Access, and I've seen some monstrosities. Same could be said for Java, .NET, PHP - and every other programming language out there. Blaming the platform (Access) for the faults of the developer isn't really fair.
0
 

Author Comment

by:williamstechnologygroup
Comment Utility
I agree with you. I was hoping my comments would not be taken the wrong way. I guess my experiences with Access developers just has not been very good due to the lack of development skills. The resources for Access developers where I am located are very sparse. It's similar to Apple's File Maker Pro software. I had a design firm that developed their entire billing and job tracking system on Filemaker. The developer left and I searched all over creation and could not find a suitable support person. It was like a ticking time bomb waiting for something to go wrong.
0
 

Author Closing Comment

by:williamstechnologygroup
Comment Utility
none of the original suggestions worked, had to convert to SQL
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now