Solved

access database issue

Posted on 2014-04-28
18
751 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
ID: 40028804
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
ID: 40028806
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
ID: 40028921
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 84
ID: 40029163
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
ID: 40029239
I'd check anti-virus settings and make sure their not getting scanned.

Jim.
0
 
LVL 57
ID: 40029242
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
ID: 40029613
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
ID: 40029933
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
ID: 40030501
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
 

Author Comment

by:williamstechnologygroup
ID: 40030530
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
ID: 40030603
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
ID: 40047338
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
ID: 40195679
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
ID: 40196418
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
ID: 40197165
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
ID: 40197470
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
ID: 40197487
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
ID: 40206077
none of the original suggestions worked, had to convert to SQL
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

861 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