|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
I have a MS Access 2003 Database in Windows XP, split, front and back ends,
back end on server, front end on users' machines. The problem is that on
some machines, it's too slow. All our machines use Windows XP.
I have carefully worked through the Microsoft Access Performance FAQ at
http://www.granite.ab.ca/access/performancefaq.htm
and that did help. One form speeded up from 49 secs. to 13 secs. (an
average for all users.) (I have a routine which actually records the
times.) Taking car of the ldb locking problem helped the most. Resetting
the subdatasheet Name property from Auto to None didn't help.
The problem is that for the same form, a couple of users can bring it up in
1-2 secs; for others it takes 16 secs. I suspect that optimizing the
database now no longer depends on my Access code, but on the peculiarities
of users' individual machines.
First, of all, I make sure that everybody in the office scans their machines
for viruses and spyware and defrags regularly. That's the most obvious
thing. On one machine I upgraded memory from 512 MB to 1 GB and it had no
effect on the speed of the database. Another machine, which was very slow
running all programs, a consultant wiped out everything on the hard disk,
reinstalled Windows XP and the other programs, and there was a dramatic
speed up. However, nobody has the time to do this for all the machines in
the office! As for interference from an antivirus checker slowing things
down, all our machines use the same one, Symantech, so that does not explain
differences in speed. What should I do?
* Should I get a list of running processes of each machine, and experiment
on killing the unnecessary processes one by one, to see which one is slowing
things down?
* Is there software that would scan your machine and give you suggestions
for speeding it up?
* Some workstations are running Access 2000, some Access 2003. (The
database is in Access 2003.) Would upgrading all workstations to 2003 help?
|
|
|
[Quoted Text] > I have carefully worked through the Microsoft Access Performance FAQ at > http://www.granite.ab.ca/access/performancefaq.htm> and that did help. One form speeded up from 49 secs. to 13 secs. (an > average for all users.) (I have a routine which actually records the > times.) Taking car of the ldb locking problem helped the most. Resetting > the subdatasheet Name property from Auto to None didn't help.
Ok, so, just to be 100% sure here..
You turned off track auto name correct
Your startup code opens up a table in the back end KEEPS IT OPEN AT ALL
TIMES!!!! (this is the persistent connection trick mentioned in that
performance faq)
And, of course, you have a mde on each computer...right?
Other issues such as virus, defrags and the like are likely a wild goose
chase here..
>As for interference from an antivirus checker slowing things
down, all our machines use the same one, Symantech, so that does not explain
differences in speed.
No, the above does not get you off the hook. I seen machines with
*different* virus settings. So, for example some have the setting to ignore
the scanning of mdb files..and some don't....the fact that they are all
running the save version IS A MOOT POINT IF THEIR SETTINGS ARE NOT THE
SAME!!
As I said, likely, the virus stuff is NOT your problem here..but your above
statement is also not correct UNLESS you ensure that ALL computers have the
SAME settings.
>
> * Some workstations are running Access 2000, some Access 2003. (The
> database is in Access 2003.) Would upgrading all workstations to 2003
> help?
Ah hah!!! ......does this mean you are NOT distributing a mde???? That
would be a warning sign to me that the developers have missed the boat
here.....
Perhaps you are distributing a mde to those users..but it is important to do
so.
( could ask a whole bunch of questions like do you decompile..and re-compile
the mdb for the 2000 users, and also for the a2003 users..as they are NOT
binary code compile compatible).
Buy not using a mde, then you risk the problems of not disabling track auto
name correct. Worse, by not using a mde, you often can have the mdb in a
un-compiled state, and that can cause all kinds of performance issues,
including bloat, and including slow load times as the code has to compile
each time (and, using a mdb also means the issue of track auto name correct
can come into play here). Also, the fact of mixed machines may mean you are
developing in a2003..but distributing to machine that are running a2000
(this would requite you to do a de-compile..and re-compile). In fact, even
before creating the mde, I would do a de-compile on the a2000 box..and then
create the mde.
So, lets try this again:
* you distribute a mde to EACH computer
* your startup code opens up a table to the back end...and KEEPS IT OPEN
(you mentioned some terrific performance increases as a result of this...so,
it seems you got this one right).
* you disabled track autoname correct
* you de-compile the a2003 code..and then re-compile on the a2000..and THEN
create the 2000 mde. (note the two steps here!!).
Hum,....so, try the mde...and make sure your have a persistent connection.
Those are the first two things. After that...double check the Tony's list...
Once you done the above..I don't see why one machine should perform much
different then others....assuming they are similar in terms of the OS, and
their network settings (packet size...retry settings etc are simular).
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal[ at ]msn.com
http://www.members.shaw.ca/AlbertKallal
|
|
That reply was extremely helpful.
For the record:
1. I turned off track auto name correct.
2. The first form in the front end (the main menu) keeps a table from the
back end open.
3. Possible problem: I have not checked individual setting of our virus
checker.
4. Probably problem: I have distributed .mdb files to users, not .mde files
for the front end.
As for the last point, I assume I should create an mde of the front end (not
the back end) -- actually, two mde's, an a2000 mde and an a2003 mde and
distribute these appropriately, right?
This database is not that big -- about 8 MB for front end another 8 MB for
the back end.
|
|
|
[Quoted Text] > As for the last point, I assume I should create an mde of the front end
> (not the back end) -- actually, two mde's, an a2000 mde and an a2003 mde
> and distribute these appropriately, right?
yes..you have the above 100% correct. You only need the mde.
Note that like trouble shooting, the mde may NOT solve this..but it
certainly eliminates a few issues.
Also, it not clear which environment you develop in (a2000, or a2003). I
have to admit (for better, or worse), I have some real problems moving my
a2003 code to a a2000 machine unless I do a de-compile first....
It seems you been VERY thorough here..and the list of possible things is
become VERY small!!! I bet this get fixed!!!
If the above don't help, then we down the messy job of looking at each
computer..(network card, connection speed...etc.).
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal[ at ]msn.com
http://www.members.shaw.ca/AlbertKallal
|
|
|
[Quoted Text] > Also, it not clear which environment you develop in (a2000, or a2003). I
> have to admit (for better, or worse), I have some real problems moving my
> a2003 code to a a2000 machine unless I do a de-compile first....
This has been bothering me also.
How does decompile? Just make som code-changes and recompile in ac2000?
If one develops for a mixed environment maybe it's better just to stay with
a2000.
Does using a2002 require a decompile before making a a2000 mde?
Jesper
|
|
|
[Quoted Text] > This has been bothering me also.
> How does decompile?
You need to create a shorcut to the appcaltion. This is exaplned here:
http://www.mvps.org/access/bugs/bugs0008.htm
and
http://www.mvps.org/access/modules/mdl0039.htm
> If one develops for a mixed environment maybe it's better just to stay
> with a2000.
> Does using a2002 require a decompile before making a a2000 mde?
Yes..I am suggesting the decompile
I am using a2003 to develop a applation for a2000. And, yes, I am suggestion
that a de-compile be done with the application BEFORE you build the a2000
mde. So, if using a2002, or a2003, then you want to de-compile before you
create the mde (and, of course, you have to create the a2000 mde on the box
with a2000...since you can ONLY create a mde in the same version of
ms-access).
It is certainly "best" to develop in the same version if possible. I can
only say that I have found a few nasty repot errors and problems that don't
show up in a2003 (this kinds of bugs, and simple increase in reliability is
better in a2003). So, some of my conditional formatting did NOT work
correctly in a2000. (a2000 was the "first" version to have conditional
formatting...so, things simply work a LOT better in later versions). I had
to modify my code to get it to work correctly in a2000. Because of this
problem, I am leaning towards upgrading the client..
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal[ at ]msn.com
http://www.members.shaw.ca/AlbertKallal
|
|
I reckon a good decompile always includes an immediate compact.
You can get the same command line to do both by adding /compact
to the end of the command line.
(david)
"Albert D.Kallal" <PleaseNOOOsPAMmkallal[ at ]msn.com> wrote in message
news:OovB2sg9FHA.1140[ at ]tk2msftngp13.phx.gbl...
[Quoted Text] >> This has been bothering me also. >> How does decompile? > > You need to create a shorcut to the appcaltion. This is exaplned here: > > http://www.mvps.org/access/bugs/bugs0008.htm> > and > > http://www.mvps.org/access/modules/mdl0039.htm> >> If one develops for a mixed environment maybe it's better just to stay >> with a2000. >> Does using a2002 require a decompile before making a a2000 mde? > > Yes..I am suggesting the decompile > > I am using a2003 to develop a applation for a2000. And, yes, I am > suggestion that a de-compile be done with the application BEFORE you build > the a2000 mde. So, if using a2002, or a2003, then you want to de-compile > before you create the mde (and, of course, you have to create the a2000 > mde on the box with a2000...since you can ONLY create a mde in the same > version of ms-access). > > It is certainly "best" to develop in the same version if possible. I can > only say that I have found a few nasty repot errors and problems that > don't show up in a2003 (this kinds of bugs, and simple increase in > reliability is better in a2003). So, some of my conditional formatting did > NOT work correctly in a2000. (a2000 was the "first" version to have > conditional formatting...so, things simply work a LOT better in later > versions). I had to modify my code to get it to work correctly in a2000. > Because of this problem, I am leaning towards upgrading the client.. > > -- > Albert D. Kallal (Access MVP) > Edmonton, Alberta Canada > pleaseNOOSpamKallal[ at ]msn.com > http://www.members.shaw.ca/AlbertKallal> >
|
|
Actually, I don't know what you mean by de-compile. I have no modules and
no VBA code that I have written. I had to create the departmental database
very quickly, and using macros (though a bit old fashioned) expedited
everything. I'm not a programmer, I'm a lowly English teacher.
Just to see what would happen, I tried to create an mde file from my
a2003mdb and Access hung.
I'm beginning to think that I should just distribute 2 different mdbs, one
for a2000 users and the other for a2002-2003 users.
|
|
|
[Quoted Text] >
> Just to see what would happen, I tried to create an mde file from my
> a2003mdb and Access hung.
When you load the appclaton, hit ctrl-g to get to the code envorment. Now do
a debug->compile.....does the applation compile?
If you can't create a mde..then likely your application is not in a state
that should be distributed to users.....
This is a exact example as to why you need to distribute a mde......
Try the above compile. If you can't compile..then genially you can't create
a mde
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal[ at ]msn.com
http://www.members.shaw.ca/AlbertKallal
|
|
|