Access 2000 FE Shuts Down

[gwegner]

Back End: Access2000 data.mdb on a server running
under Access 2000 Operating
System Windows 2000.
The Microsoft database Jet is version: 4.00.8618.0
(Security Bulletin
MS04-014).
After compact data.mdb is about 29MB.

Front End: Access2000 app.mde installed on the user
site. Env: Microsoft Windows
XP, SP#2, Office 2000 ProWe. Access 2000 9.0.4402 SR-1.
The Microsoft database Jet is version: 4.00.8618.0
(Security Bulletin
MS04-014).Written using VBA.
After compact app.mde is about 5.5MB.

Issue: This application was originally written in
Access97 for Windows NT 4.0 SP #6.0. Later on it was
converted to Access2000 using DAO 3.6. Several steps were
taken to improve the original poor performance. Once the
performance was under control, the app.mde was installed
on the clients PCs (3).

Problem started 6 months ago (2.5 years later) when
client's PC got new operating system; Windows XP SP#1.
User noticed that once awhile the app.mde (originally
created under Windows NT 4.0) closes and Access shuts down
(or vice versa). 2 weeks ago users got SP#2 and running
the app.mde under Windows XP SP#2 is not fun anymore.
- performance slow down badly from 4 sec to 18 sec
waiting time to switch from add record to couple tables,
or open new form with a requested data.
- Several times a day, the app.mde/Access Window
shuts down.

The format for the name is 5.3 for .mde, .mdb
The app and data files are located in a folder under the
root
The folder names are short
The track name AutoCorrect info is off in FE and BE
The SubdataSheet Name for every table is set to NONE
The default record locking is NO LOCKS with Open databases
using record-level locking
There is a dummy table that is opened at the start time
that keeps the connection to the BE unchanged.
The recordset and db have prefix DAO. Objects are
instantiated, used, closed and set to nothing to prevent
any memory leek. The application is using DAO 3.6.
For over 2 years the BE and FE were compacted once a month
and once a week last 6 months. Couple days ago, I changed
the FE app.mde to compact on close.
The FE is heavy. Often 4 forms are open; Form1 to perform
search, Form2 to do requests (this form has 9 tabs, each
tab requires data from several tables), form3 to finalize
requests (this form is as heavy as Form2), a dummy form
with a dummy table that keeps the connection between BE
and FE. One form is visible; other are hidden.

To fix this problem I did the following (no success):
- rebuilt the back end using Access2000 under
Windows XP. I created new .mdb file, copied all tables,
compacted, reset the option. This new BE was copied back
to the server.
- App.mdb - relinked all tables, compacted under
Windows XP using Access2000.
- Created new App.mde from refreshed App.mdb.
I loaded new App.mde on the client site to test. Within 5
minutes of testing the Access2000 and the App.mde shut
down (no error message) 3 times. I used the App.mdb for
testing and the Access2000 was shutting down more often.
The results were not acceptable, I went back to the
original compacted on Windows NT 4.0 App.mde.

Do you know what is going on?
Why an application that ran so well for 3 years, it works
so bad under Windows XP SP#2?

I appreciate your reply.

GWegner

 

[Allen Browne]

Hopefully others will reply too, as I don't have a definitive answer for
you.

Your post indicates that you have tried most of the standard techniques. You
did not mention decompiling: definately worth a shot. Close Access. Make a
backup copy of the file. Decompile the database by entering something like
this at the command prompt while Access is not running. It is all one line,
and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"
Then compact.

Next, set minimal references - just VBA, Access, and DAO if possible. Check
the version of each of the files in these references. They should be up to
date, and consistent across the users. If you which files they are or which
you need, see:
http://allenbrowne.com/ser-38.html
It might also be worth checking that no one machine has a different version
of msjet40.dll anywhere on its hard drive, as inconsistent versions of jet
from simultaneous users will cause problems.

You say the app.mde shuts down "(no error message)". I presume you see only
the Windows message to the effect that "This program has been shut down by
Windows, sorry for the inconvenience." It may be possible to look at the
details of that message and at least find out whether the crash is happening
in msaccess.exe, msjet40dll, dao360.dll, msacc9.olb, or whatever. (With
error trapping in the code, it would be possible to have an mde shut down
with no message at all, but that would not happen in the mdb, so is probably
not what you are talking about.)

Your server is running Win2000. There was an issue with "opportunistic
locking" on the server causing problems with Access. From memory, Microsoft
turned that off when you installed SP3 for Win2000, so if the server has
that patch or later this should not be the problem.

You say the machines have Acccess 2000 SR-1. Again from memory, there was a
difference between SR-1 and SR-1a, and the difference was significant. Any
chance of taking them up to SR-3 for Office 2000?

You might like to try running without record-level locking on if you can try
it for a week or two. This will give a slight performance gain, It also
circumvents some problems we saw (though these related to using A2000 with
linked tables from an A97 database.)

There is a new issue with getting a runtime version of Access 2002 to
install on WinXP SP2, but I doubt that has anything to do with your issue:
http://support.microsoft.com/?id=837150

There is also an issue where Access 2002 and 2003 crash if they have a
subform that does not have a text box for the field named in the
LinkChildFields, but I have not seen that on A2000.

Are there any subqueries running at the time of the crash? Some subqueries
will crash JET no matter what you do. Again, this is probably not the cause
if the app worked fine previously.

BTW, if you do edit the database with A2002 or A2003, be sure to decompile
before you switch back to A2000 to edit further or to create the MDE. Lots
of intermittent inconsistencies seem to arise from expecting Access to
discard the binary-incompatibilities between the versions. The decompile +
compact removes the compiled code more cleanly, and then the older version
can create its complied code more reliably.

HTH

 

[Paul Overway]

One comment on the form with 9 tabs...this is a trick I use and it can make
a huge difference in perceived performance. If you're using any subforms on
the tabs, don't load them unless the tab is showing....set the source object
to "" until the user selects that tab....then set it to the proper source
object. I make this easier by using the same name for the subform control
as that used for the source object. I have a loop that runs through all the
subforms and sets the source object based on whether the subform is on a tab
that is visible. It is really a noticable improvement moving from record to
record in the main form.

I'm not seeing any issues like you've noted for users running Windows XP.
I'd suspect hardware or an OS that is hosed, unless this is occuring on
multiple PCs of different vintage. Is EVERY PC on the same service pack for
Jet 4.0?

 

[gwegner]

Thank you both for your reply. I appreciate your answers a
lot.
I will try the decompile. Do I need to decompile the BE
data.mdb as well? The BE holds only tables.

I need to create a test version of the production FE + BE
and test with debugging tools.

I asked the hardware/network person to check the jet
version on all 3 users PCs.

App and Access shut down/crashe without any error message.
The user ends up with a naked Desk Top.
To start all over takes time; open app, sign on, call
search from from the request form, enter search criteria,
proceed with the search, select the record to view/update,
check what is there and continue the work.

-----Original Message-----
One comment on the form with 9 tabs...this is a trick I use and it can make
a huge difference in perceived performance. If you're using any subforms on
the tabs, don't load them unless the tab is

showing....set the source object

 

[Allen Browne]

Decompile relates to the code.

Typically the back end has no code, and so does not need to be decompiled.

 

[Tony Toews]

I asked the hardware/network person to check the jet
version on all 3 users PCs.

What I've done is use the various API calls available and am checking
the version number and date/time of a crucial dll, msjetxx.dll, to
ensure it matches what I have on my system. See the Verify
Appropriate Jet Service Pack is installed page at my website for more
details including sample code:
www.granite.ab.ca\access\verifyjetsp.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm