473,554 Members | 3,008 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Access really slow over the network

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
Nov 13 '05 #1
7 26939
"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.
Nov 13 '05 #2
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.co m
http://koivi.com
Nov 13 '05 #3
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********@dbF orumz.com> wrote in message
news:4_******** *************** *************** *@dbforumz.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.

Nov 13 '05 #4
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
Nov 13 '05 #5
ddsvi78 <Do********@dbF orumz.com> wrote in
news:4_******** *************** *************** *@dbforumz.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
Nov 13 '05 #6

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.OpenR ecordset("tblVo id", 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_******** *************** *************** *@dbforumz.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

Nov 13 '05 #7
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\thesha re\...\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(tb l)
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

Nov 13 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
1599
by: EKT32 | last post by:
Hi, I'm facing problem with file upload over a slow network. Whenever I tried uploading a file that exceeds 3Kb, the browser will freeze for a few minutes before I get the "page over found" error. I've tried different ASP upload solutions, including 1 from ABCupload, Smartupload and ASPupload from Microsoft, and the result is the same. ...
2
4554
by: Ronny sigo | last post by:
Hello all, A big part of my life I have been working with databases (as a non professional) on a local machine. Apart from a small problem now and then, for which I find the solution myself, or some of you guys provide me with the solution, all goes well. But now, for the first time I am faced with the porblem that I have to run Access over a...
0
1022
by: Dean R. Henderson | last post by:
I have a project built for ASP.NET that recently started running really slow in debug mode (it takes about 10 seconds or more to step from one line of code to the next). This just started recently and only impacts one ASP.NET project. I have some other ASP.NET projects that I can step thru with the debugger with almost instant response. I...
1
2426
by: z. f. | last post by:
Hi, i have a dot-net app. the first load is really slow, about 4 seconds at least. next requests are very fast. is there a way to analyze the buttleneck in the load process of a asp-net application / aspx page. the global.asax has no code in it. the dll is using 2 "home-made" dot-net DLLs.
21
1539
by: TryingLikeHeck | last post by:
I have an application that uses a LIstView. Maybe 100 items each with 20 subitems. The app looks at eack item and subitem twice. I.e., it scans the entire set of data item1, sub1,sub2,... item2,sub1,sub2... .. .. twice
7
1446
by: Fredrik Melin | last post by:
Running a solution with about 20 sub-projects total about 150.000 lines of code. Typing code is slow (delay when typing fast). Pressing next line while debugging is slow. Pressing Ctrl-F for quick find is rediculous slow. Everything is slow. Running Exe is fine though This is on a HP Workstation machine with Xeon 3GHz (HT Enabled) with...
1
4425
by: Piyush Trivedi | last post by:
I am experiencing difficulty to access a mapped network drive in an ASP.NET application. While using the .Net framework v 1.1 implementation of System.IO.Directory.Exists() to check if the directory is exists and then create the directory if not exists, but it fails to recognize a mapped network drive. I have set also set the <identity...
10
2667
by: cellus205 | last post by:
Hows it going everyone. Im still pretty new to vb .net, and am having a little trouble. I am loading a tab delimited txt file into an access database, and parsing the text to extract some of the needed information from each field, and while this works, it is really slow right now. Does anyone have any suggestions on how I could speed this up? The...
0
7596
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7798
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8039
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7560
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7887
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6140
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5152
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3556
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
838
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.