[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

access database issue

Posted on 2014-04-28
18
Medium Priority
?
909 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Jim.
0
 
LVL 58
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 58
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 85
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 85
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

656 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