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.

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/MS-Access-sl...ict232329.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=804774

[ddsvi78]

"ddsvi78" wrote:

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]

ddsvi78 wrote:

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...

--
Justin Koivisto - ju****@koivi.com
http://koivi.com

[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
"ddsvi78" <Do********@dbForumz.com> wrote in message
news:4_***************************************@dbf orumz.com...

"ddsvi78" wrote:

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.

[Tony Toews]

ddsvi78 <Us************@dbForumz.com> wrote:

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]

ddsvi78 <Do********@dbForumz.com> wrote in
news:4_***************************************@dbf orumz.com:

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.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[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)

"ddsvi78" <Us************@dbForumz.com> schreef in bericht
news:4_***************************************@dbf orumz.com...

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.

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL:
http://www.dbforumz.com/MS-Access-sl...ict232329.html
Visit Topic URL to contact author (reg. req'd). Report abuse:
http://www.dbforumz.com/eform.php?p=804774

[corey lawson]

ddsvi78 wrote:

"Kevin3NF1" wrote:

> 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?
>

> > 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.

They have Shortcuts from the mapped drives on there desktops. More
than one person will be on at the same time. How do I a create front
end so that it always syncs up with the mdb files on the main server?
That sounds like that may be the problem.

For one, use UNCs instead of drive maps. (i.e.,
\\server\theshare\...\mydb.mdb) when linking to text files, excel
spreadsheets or other Access MDBs. You'll be much happier...
When there server crashed the IT guy went around and remapped
everyones drives and created shortcuts to the new location. He did not
create any type of front end.
I sort of inherited an app at work that uses a tool that checks the
version in the local copy of an Access app vs the one on the server, and
copies the new one to the local computer if need be, then launches the
file, but I can't recall right now what it is.
Sorry I really dont understand what the Front end/Back end is or how
it works. I know Backend is where the main .mdb files are. I just dont
know how to setup the front end.
The goal is to separate the "front-end" pieces (forms, reports) from the
data (tables). Part of this is to isolate the "I had to shut my computer
off"/lost network access during data update to as few possibilities, and
the other is to isolate MDB "bloat" as forms, queries, reports, etc.
create new data pages for temporary data tables during the operation of
the database. This keeps the "data" MDB from growing profusely even from
read-only users, which minimizes the possibility of the data database
just getting too corpulent and becoming corrupted. Much better to happen
to a separate front-end MDB.

The upside is that it because you're already working with linked tables
via DAO/ADO already (so you're not using some of the DAO data access
methods you can do with local tables), then refactoring that Data.MDB to
a real RDBMS server (i.e., SQL Server, Oracle, etc) will in essence
already be done once you migrate the data. All you have to do is relink
the data links from the Data.MDB to the appropriate ODBC DSN, and rename
the linked objects if necessary (SQL Server links tables as
"DBO_tablename"...). Your VBA data accessing code should work the same...

But it won't speed anything up, it'll just help keep them from slowing
down quickly due to a monolithic MDB taking forever to load from the
fileserver...

I dont think it is the size of the databases. Every databases got slow
at the same time. And I worked on one that was fairly small.
It's a file-server issue. It really has nothing to do with Access
specifically, except that MS never made a Jet server engine
unfortunately. There could be some server optimizations that could be
done (i.e., upgrading from 10 Mbps ethernet to 100 Mbps, or even Gigabit
ethernet, will speed things up, as will using ethernet switches instead
of hubs), as far as drive fragmentation, etc.

If you have problems copying large files on the fileserver (i.e., copy
the MDB to a different folder on the server or to your workstation takes
FOREVER), then you have other issues at hand that aren't related to
Access, but definitely affect Access hard.

As far as the patches go. I would have to check with them and see if
they installed any patches right before the problem started. I do know
that all patches are up to date on all servers and workstations.

Thanks for everyones responses.

Did you check that the tables in the data databases all have appropriate
indexes, use declarative referential integrity (i.e., explicitly defined
foreign key relationships), etc? That can be a factor, too.

Other factors involve how data is joined, accessed, etc. If there are a
lot of VBA functions in queries, then ODBC has a tendancy to pull the
entire datasets involved to the local MDB (which is compounded if
you're running it off of a slow network drive share, because the file is
"local" to the share, not your computer...) in order to apply the
function...

For a quick way to relink tables, here is some code to get you started,
but it is in no way generic enough as it stands...

(for Access 2000/XP/2003, be sure to make a reference to the Microsoft
Data Access Objects 3.6 library first...) this code should work in 2.0
as well...

public sub RelinkTables
dim db as dao.database
dim t as dao.tabledef

set db = dbengine(0)(0)
for each t in db.tabledefs
if t.connect <> "" then
RelinkTable(t, "\\server\share\thedata.mdb")
end if
next t
end sub

public sub RelinkTable(tbl, linkto)
dim db as dao.database
dim t as dao.tabledef

set db = dbengine(0)(0) 'or use CurrentDB...
if typename(tbl) = "String" then
set t = db.tabledefs(tbl)
else 'assume it's a DAO.Tabledef...
set t = tbl
end if

if t.connect <> "" then ' or put in code to match a specific pattern...
t.connect = linkto
t.refreshlink
end if
end sub