Access really slow over the network

[ddsvi78]

I am a complete idiot when it comes to access. Now that said, I work
for a computer security company and one of our customers came to us
with an access problem.

They had been running fine for a long time. Then about a month ago
there main server that hosted all the .mdb files crashed. So they
restored everything to a new server (I believe 2003 server).
Everything was fine for 2 weeks, then one morning they came in and the
queries were taking from 10 seconds to 5 minutes depending on how many
filters they were using. (Normally it takes 1 to 10 seconds tops to do
the same queries). They said that they didnt change anything at all. I
checked to make sure all the latest patches and updates were done.

The rest of there network is running just fine, no computers
broadcasting or anything. I installed access on the main server and
there was no lag issues what so ever. Everything ran smooth as can be.
But remotely is very sluggish. If you go to my network places and
browse to the directories where the .mdb files are, there is no lag in
doing that. It is only when they open there VB Forms and start quering
the databases that it is slow.

Can anyone please point me in the right direction here. I am sure
there is more info you need. Just tell me what you want to know and I
will try to answer you. Oh the workstations are all XP using Office XP

Thanks for your help.

 

[ddsvi78]

I am a complete idiot when it comes to access. Now that said,
I work for a computer security company and one of our
customers came to us with an access problem.

They had been running fine for a long time. Then about a month
ago there main server that hosted all the .mdb files crashed.
So they restored everything to a new server (I believe 2003
server). Everything was fine for 2 weeks, then one morning
they came in and the queries were taking from 10 seconds to 5
minutes depending on how many filters they were using.
(Normally it takes 1 to 10 seconds tops to do the same
queries). They said that they didnt change anything at all. I
checked to make sure all the latest patches and updates were
done.

The rest of there network is running just fine, no computers
broadcasting or anything. I installed access on the main
server and there was no lag issues what so ever. Everything
ran smooth as can be. But remotely is very sluggish. If you go
to my network places and browse to the directories where the
.mdb files are, there is no lag in doing that. It is only when
they open there VB Forms and start quering the databases that
it is slow.

Can anyone please point me in the right direction here. I am
sure there is more info you need. Just tell me what you want
to know and I will try to answer you. Oh the workstations are
all XP using Office XP

Thanks for your help.

One more thing. They have probably 50 to 100 .mdb databases on there
server. Different ones for different accounts. ALL of the are doing
the same thing. I focused just on one when I was there. Compacted and
optomized the database, but that didnt help at all.

 

[Justin Koivisto]

I am a complete idiot when it comes to access. Now that said, I work
for a computer security company and one of our customers came to us
with an access problem.

They had been running fine for a long time. Then about a month ago
there main server that hosted all the .mdb files crashed. So they
restored everything to a new server (I believe 2003 server).
Everything was fine for 2 weeks, then one morning they came in and the
queries were taking from 10 seconds to 5 minutes depending on how many
filters they were using. (Normally it takes 1 to 10 seconds tops to do
the same queries). They said that they didnt change anything at all. I
checked to make sure all the latest patches and updates were done.

The rest of there network is running just fine, no computers
broadcasting or anything. I installed access on the main server and
there was no lag issues what so ever. Everything ran smooth as can be.
But remotely is very sluggish. If you go to my network places and
browse to the directories where the .mdb files are, there is no lag in
doing that. It is only when they open there VB Forms and start quering
the databases that it is slow.

Can anyone please point me in the right direction here. I am sure
there is more info you need. Just tell me what you want to know and I
will try to answer you. Oh the workstations are all XP using Office XP

First thing I would have checked is if the databases have grown
considerably in size. After all, if there is double the data than a
month ago, the queries probably will take longer since there is more
data to sift through...

 

[Kevin3NF]

Do you mean differnt front-ends pointing to the same back-end?

Do the users have the front-end on their desktops, or are they using a
shortcut to the database on the server?

How many users? Is it still slow when only one person is using it, or just
slows as usage goes up?

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm

www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.

www.experts-exchange.com - experts compete for points to answer your
questions

 

[Tony Toews]

Everything was fine for 2 weeks, then one morning they came in and the
queries were taking from 10 seconds to 5 minutes depending on how many
filters they were using. (Normally it takes 1 to 10 seconds tops to do
the same queries). They said that they didnt change anything at all. I
checked to make sure all the latest patches and updates were done.

Are they now virus scanning MDB files on the server or the client?

There was an Server Message Block (SMB) performance problem. There
may be other similar problems. "Your access to network resources is
slower in Windows XP than in earlier versions of Windows".
http://support.microsoft.com/?id=834350

Those are the only things I can think of that could make a difference
on the server over night.

Otherwise I'd suggest visiting the Access Performance FAQ page at
http://www.granite.ab.ca/access/performancefaq.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

 

[David W. Fenton]

One more thing. They have probably 50 to 100 .mdb databases on
there server. Different ones for different accounts. ALL of the
are doing the same thing. I focused just on one when I was there.
Compacted and optomized the database, but that didnt help at all.

What hotfixes or Windows Updates were installed just before the
problem occurred? I've seen Exchange Server hotfixes hose Access
databases, so don't rule out patches to software that doesn't seem
as though it would be involved in file serving.

 

[Arend Jan Nijenhuis]

I have had various performance issues in my projects. After lots of research
and troubleshooting, I found that it's only a problem in a real
client/server invironment (Windows Server 200x / Windows XP or Windows98
clients).

I have solved the problem as follows.

I have an empty table in the backend (the database file on the server),
called "tblVoid". There's nothing in it, just one empty field in one record.
In the main form of the frontend (I reckon you have some kind of main form
that's always open) I see to it, that this table is ALWAYS open.
In the Load event of the main form I have something like:
set rstVoid = CurrentDb.OpenRecordset("tblVoid", dbOpenSnapshot)
the rstVoid is a global variable, declared in the declarations section of a
public module.

This way, throughout the program, there is a connection to the backend from
this client, and the ldb file doesn't need to be accessed hundreds of times
in succession.

From the day I found this solution, people bring me coffee all day.



***
Arend Jan Nijenhuis (nl)