Access 2000 Front-End needs constant Repair 4

[TommieB]

Scenario: Large Access 2000 application running on a server at a customers site. Data is stored in SQL 2000 on the server. A client machine and the server machine use the application at the same time. No problem with speed or functionality. SQL Server data is backed up from the application at close each night.

Problem: The user states that he gets an error message to repair the database after running the backup (this would happen before the access application is closed). Which he does. Then a second message is generated stating that "The database is in an unexpected state." He can not reopen the application unless I remove the old copy and replace it with a backup copy kept on the server for this purpose. I could understand data corruption but why would an .mde become corrupted on a SQL backup? Or is this only masking the real problem? Could the user be doing something that would cause this to happen, like having the application open on the client while running the backup from the server or maybe a network card or hub is dropping the connection for a split second? Or am I causing this with some code that I am not aware of.

There are only 3 real possibilities: user error, hardware problems, or coding errors. I can run the backup in my office 200 times in a day and not have any problems. I can even run the backup while a client machine has the application open and I can not reproduce the error. I just don't see any code that would cause this problem.

The user is upset and I am frustrated. Does anyone have any suggestions?

 

[bluecjh]

An access db that I am administering at has stalled on several occasions with the message:

" the database is in an unexpected state Microsoft Access can't open it. The database has been converted from a
prior version of MS Access by using the DAO CompactDatabase method instead of the Convert Database command
on the Tools menu (Database Utilities submenu). This has left the database in a partially Converted State"

Users are then unable to log on and are forced to resort to backup copies with some potential data loss. On investigation
(See Microsoft Product Support Services), this information message may relate to Access 'having some problems with Win NT 4
and NT 2000 servers commonly known as the 'OpLocks problem'.

Windows 2000 SP3 is stated as having the necessary fix for this problem. It must be loaded on both the server and the workstations.
Can you make any suggestions as to whether this documented bug is relevant?


PS. Microsoft's explanation of this problem includes:
Microsoft:
Opportunistic Locking (oplocks) on the Network File Server
Microsoft has discovered an issue where opportunistic locking can increase the risk of Jet database corruption when the file is shared by two or more clients on a network file server. This issue applies to Microsoft Windows NT 4.0, Microsoft Windows 2000, and Novell file servers that support opportunistic locking. This issue also applies to clients that are running Microsoft Windows NT 4.0, Microsoft Windows 2000, or Microsoft Windows XP, and that connect to a file server that supports opportunistic locking.

Windows 2000

To avoid the problem on computers that are running Windows 2000, you must install Windows 2000 Service Pack 3 (SP3) on the Windows 2000 file server where the Access database file is located, and then on each Windows 2000 client computer that connects to the Windows 2000 file server. For information about how you can obtain and how you can install Windows 2000 SP3, read the following Knowledge Base article (end)

A less vague explanation is that when the database is compacted, a version parameter in the destination file is being corrupted, resulting in improper version
information in the resulting file.

Ulitimately I found no proper solution as ICT were not very
helpful. My solution is never to compact the offending b-end directly. Instead compact it and then create a new empty db and import the objects etc.. in. THis has so far
never failed and recreating a back end in this fashion takes only 2 mins. But really this is just a
much needed workaround and not a solution.

I hope this is of assistance.

 

[bluecjh]

Continued
TommieB
I would also add that my databases f-e and b-e also reside on
a the server (we are precluded access to hard drives officially). My gut feeling is that as per previous comments made that moving the f-e may be the solution to this op locks problem that occurs on the f-e. I have some
evidence that this is the case.

 

[TommieB]

bluecjh, yes I found that moving the fe to each client and leaving the be on the server solved my problem.

The service pack that you mentioned did not seem to help in my case.

 

[gransbpa]

Hi there,

Hope you don't mind that I jump into this discussion a little late.

I have a backend database which is crashing repeatedly, and it might have begun last year (I'm not sure, wasn't responsible at the time) when some clients moved to Win2k and some remained at win98 (with access97 fe and be). Do you think this might cause databases to crash?

Another think is a timer-event in the front-end. Every client is checking every second if a flag in the backend has been reset. Is something like that maybe likely to cause crashes?

 

[TommieB]

Are the Win2K machines running access97 as well? If some of the clients are running access2000 and some are running access97, that might be part of your problem. You said it crashes, do you mean it truly goes down or are you getting error messages or corrupted data?

you did not say whether your backend is access or sql, but if you have 10 clients querying for this flag every second that is 600 queries per minute. That's a lot of traffic for an access backend.

My personal opinion (which I will probably get flamed for) is that if you have more than 5 users and the app is being used for continuous data input and you have lots of data, I would move the data to SQL server and rewrite the app in something else. Access as a development environment (not as a database) was never intended to be used for full-scale, high-traffic applications.

 

[psemianonymous]

I don't know if this helps, but I found some other threads with the same error message:

http://groups.google.com/groups?hl=...le+Search&meta=group=comp.databases.ms-access

The first thread suggested that the problem was a user punched the power button on their PC, improperly shutting down Access and thus corrupting the database. --
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

 

[psemianonymous]

PS TommieB - feel free to read all of this tek-tips thread, particularly the first item, in which he states "Data is stored in SQL 2000 on the server."

Hey, we all make mistakes, right? --
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

 

[psemianonymous]

byah, speaking of mistakes....ignore last post. YOU were the one. I see. --
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

 

[TommieB]

That will definitely cause the database to have to be repaired.

Seen it happen many times, but it also happens if the application gets into a situation where a error message is returned and on hitting OK it stops execution of the program. The program is waiting for something to happen and you can't do anything about it but shut the system down.

Add error handlers to all events and functions.

 

[TommieB]

foolio12, that's okay.

After all, we all make mistakes don't we?

I should have prefaced my post with the fact that I was addressing grandsbpa's post asking for advice.

I have long sinced fixed my problem which meant moving the app to the client's machines and leaving the be on the server.

Of course, now I seem to be having memory issues on the server which also happens to be a client running this app. It could be SQL server which will use as much memory as it can get. I have double checked all of my recordsets and they are all closed and set to nothing, so I know that the parts that I wrote are not leaking memory, but the majority of the app was written before I got here and it is all linked tables which I assume is a constant open connection, and forms bound to tables and queries.

 

[gransbpa]

Thanks TommyB,

The Backend is Access 97, and all clients are Access97. There are 60 users, about 15-20 of them working simultaneously on one key table exceeding 30.000 records, so something must go wrong here I see now...

Anyway, they'll upgrade to SAP soon.

 

[sshah07424]

Did anyone find the permanent fix for this problem. We recently upgrade from office97 to office2000. We have couple databases in access2000 and they worked fine for about a two months and now we are facing these problems.

1) Two databases, it's coming up with "This Database is in unexpected state; Microsoft can't open it...." error. We have front end and back end database and the both resides on sever. I was reading the treads and it mention that front end should be on client. In our situation database front end changes more often sometime couple times a day and we have multiple users uses this database(fe). It's very hard to updata everybody's fe database on their machine.

2) One database, it open up the database window instead of opening swithboard form which I have setup in startup window. In this case I have to logoff every user connected to the database and do compact & repair database and then specify the switchboard form in startup window again and disable other options in startup window again. Then works fine for sometimes couple days and sometime only couple hours. Again this database has front end and back end and they both resides on the server.

In all databases front end database has link table from backend database and also link tables from Centura SQLBase database. Some other databases works fine for now.(thank God)

Any help will be appreciated.

 

[BobJacksonNCI]

sshah07424,

Sorry to protrude...

I'm watching 12 Lab puppies and hadn't visited tek-tips in a while.

Oh, and I am old, but not an expert.
[disclaimer switch OFF]

You will probably be better off with the front ends on client PC's. Do a search here and you should uncover some mechanisms to automatically copy your current front end from the server to the client when the client first initiates the database. In most cases, a mechanism exists to determine if the copy is needed (version number).

Good Luck!

Now, back to my 12 starving puppies. (They are NOT, they just THINK they are if it's been more than an hour since they last ate.)
Bob

HTH,
Bob

 

[sshah07424]

Bob,
Thanks for the reply. I will try that.

 

[BobJacksonNCI]

Follow-Up

Here are some FAQ's with code to ensure users have latest front end:

faq181-5559

faq705-3579

faq705-5629

faq705-2010



HTH,
Bob

 

[scking]

I would like to add a little note of experience. My application front-end was being used on the network and it caused not amount of grief. The fix amounted to placing the front-end on the client computers. Three years ago it caused me to travel across country to make the configuration changes and keep a good customer happy with our service. I have never been sorry about making the change, even though it causes a deployment issue. That was solved by writing a function that wakes up when the application is opened and checks the user properties I created on both front-end and back-end. If the versions are not identical it posts a notice to the user to contact our office. It effectively eliminates old front-end problems and allows distribution by storing the current front-end in a directory on the server where users can simply copy it to their servers. We have had NO issues since the change.

---------------------
scking@arinc.com
---------------------

 

[BSman]

I have many client server applications running with Access front ends. Some with larger back end databases have SQL Server 2000 databases, while the rest are typically Access 97 back end databases. All except the newest and oldest front ends have both an Access 97 and an Access 2000 front end. The Access 2000 version is created by taking the latest Access 97 version and opening it with Access 2000 and selecting the convert option. There are two or three small apps that won't convert easily to Access 2000, generally because they were originally written in Access 2.0 (!) and converting to Access 2000 is just asking too much.

All of the apps run from server locations. The front end and back end (or SQL back end) are on servers. Been running these successfully for quite a few years. I dislike having copies of an application on client machines because it makes upgrading the front end (and version control) a nightmare.

I don't understand the need to initiate a backup procedure for a SQL Server database from a front end application. It's very easy to set standard backup times within SQL Server where the database is backup up (middle of the night, for example). SQL even takes care of backing up the transaction logs and you can set up for incremental as well as full backups.

Here are some problems I have encountered with the apps on the server:

On rare occasions I have had a front end application file left in a locked state because the server backup program didn't release the file after backing it up.

I've seen some situations recently where an application database gets left in a read only state after having been opened by an Access 2003 user (yes, now I work in an environment with Access 97, 2000, and 2003).

As far as version control, I always include on each app's main menu the last date it was changed (as well as including a version to be displayed in the Windows application bar at the bottom of the screen).

These applications range from ones with one or two users to those with 40 or more simultaneous users. The back ends range from quite small to SQL Server databases that are over 6 gig of data in one database (and data is also used from data master SQL databases that contains some data common to many applications as well as application log in data.

The front end (always .MDE files) needs to be compacted on occasion because it grows a bit every time it is opened by a user. With lots of users going in frenquently it can grow significantly over a period of time.

My complaint about Access centers around calculating subtotals and totals in reports. I have been calculating them manually in reports for many years, rather than relying on the running sum property, which I've found to more often than not calculates incorrectly.