slow speed on network

[Guest]

I have a secure database which I have copied to a network drive from my PC
along with its security file in order for a few users to run queries/reports
off it. The master table has about 700,000 records and would grow with time.
I have developed the application on my PC. The performance of this db on the
network is extemely slow compared to the copy on my PC so much so that a
report which takes about a minute to run off my computer takes about 10
minutes to do so on the network. How can I increase the speed of the db on
the network? Any other suggestions for improvement would be appreciated

 

[Albert D.Kallal]

A few things here:

First, what kind of network?

I am temped to give a strong lecture about you not having tested, and
developed on a network (what is usually 10 times, or more slower then your
hard drive).

However, lets save the lectures for another day.

First, what is the speed of your network.

You can read about deploying ms-access on a network here:

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

Further, is your database split? Again, to save you a big lecture, you can
read the following, please read!!!

http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

Ok, now that we got up to speed on how things are to be, I would check:

#1 - do you have a persistent connection from the front end to the back end?

#2 - are you using mde's for the front end? (you should)

#3, work you way thought he best faq on the internet for ms-access, and you
can find it here:
http://www.granite.ab.ca/access/performancefaq.htm

 

[Guest]

It was an interesting reading. To answer your questions first, we have a WAN
with speeds in excess of 100MB.
I have split my database now following the steps in your tutorial. I was
able to split it but when I try to make an MDE file out of my FE, it first
said that it cant be done in the present 2000 format and I would have to
convert it into current version. (I have Access 2003 on my computer so I dont
know how I had a 2000 file format to start with?, my database called say db1
said on title bar :- Db1: Database(Access 2000 file format). Is MDE not
supported in 2000 format?
Anyways, I converted to 2002-2003 file using the Tools - Database Utilities
- Convert Database to 2002-2003 format and named it say db2. Surprisingly,
db2.mdb (2002-2003 format) is only 544kb and db1.mdb (2000 format) is 444,224
kb. Ignoring this huge difference, I went ahead to make an MDE out of db2.
This time it says "Access cant make MDE" because "This error is usually
associated with compiling a large database into an MDE file" . After this, I
repaired and compacted my database but it still doesn't make MDE file

 

[Douglas J. Steele]

Check the "Can't make an MDE!" checklist at
http://www.access.qbuilt.com/html/conversion.html#CannotMakeMDE

 

[Guest]

I read through the article: There was not any question to which a 'no' would
jump out. I am trying to make an MDE out of db2 which has 2003 format, the
same version of Access that I am using to make MDE, I am the owner of this
database with exlusive access...
I am doubtful about 2 things though:
first, the most common reason for failure: does the code compile: how does
one determine that?
second, about the Table ID limit being 1000: I have about 15 linked tables,
20 queries referencing those tables and each other, 2-3 forms and 4-5
reports, as such I wont expect the TableID figure to approach 1000

 

[Albert D.Kallal]

It was an interesting reading. To answer your questions first, we have a
WAN
with speeds in excess of 100MB.

Great stuff. 99% of the time, people do have a regular network. However,
when I get a call about the application being slow. further questions result
in me finding out that the client is using the application in a trailer in a
oil field, and the connection speed is via a cellular node, and the
connecting speed is 2 times that of a 56k modem, then it becomes quite
obviously why things are slow!!!

I have split my database now following the steps in your tutorial. I was
able to split it but when I try to make an MDE file out of my FE, it first
said that it cant be done in the present 2000 format and I would have to
convert it into current version.

Yes, I make mention of this issue in the split article.

I have Access 2003 on my computer so I dont
know how I had a 2000 file format to start with?, my database called say
db1
said on title bar :- Db1: Database(Access 2000 file format). Is MDE not
supported in 2000 format?

Yes, access 2003 defaults to a2000, as this covers a2000 a2002, and a2003.
This allows users to exchange data with ease. So, this default in
intentional, and I think a good idea. It makes no sense to ALWAYS adopt a
new file just because you got the latest version of office. So, to cut down
on this nonsense, ms-access defaults to a2000, and that is a great idea.

However, when you start developing software, then using a mde is a good
idea. This becomes a different, and larger issue then that of being able to
exchange data files with other users of office. When you start writing
software, then you got to tighten up the environment used, and things become
less loosely goosey.

Anyways, I converted to 2002-2003 file using the Tools - Database
Utilities
- Convert Database to 2002-2003 format and named it say db2. Surprisingly,
db2.mdb (2002-2003 format) is only 544kb and db1.mdb (2000 format) is
444,224
kb. Ignoring this huge difference,

Yes, a compact and repair also will reduce your file, and when developing, I
do a compact and repair MANY times a day.

I went ahead to make an MDE out of db2.
This time it says "Access cant make MDE" because "This error is usually
associated with compiling a large database into an MDE file" . After this,
I
repaired and compacted my database but it still doesn't make MDE file

Just go ctrl-g, and then go debug-compile. When you try and compile, any
compile errors will now be pointed out to you.

You can't create a mde with compile errors, but that EXACTLY the WHOLE point
of this process!!!

You don't want to be distribution software with compile errors. You don't
HAVE to distribute a mde but there is a good many reasons, and the fact that
is forces you to do this "last check" before you deploy makes a lot of
sense.

assuming that basic things like track-auto name correct (which by the way,
is ignored as a mde) has been disabled, and you got a persistent connection
open at all times, then your application should run well indeed..

 

[Guest]

Debug-Compile removed the compile errors and I was able to make the MDE file.
I have a few questions :
1. The MDE file looks like the mdb file that the FE mdb that it was made
from...it still shows queries, reports, forms etc. I have my shortcut for
security pointing to the mde now and teh shortcut opens the mde. It shows the
queries, reports and forms. I thought that the mde encrypts all this.
2. The backend mdb that was created after the splitting process is not
secure even though the original db was secure; however, the FE is secure. Is
this normal ? Do I have to take extra steps to make my BE secure? Security
for BE is important because it is the BE which will be sitting on the shared
drive.
3. How do I deploy the FE (either by itself or in MDE form) to my users
located in remote sites?
4. I will need to make changes to the BE sitting in the shared drive
frequently. If a user whose FE is linked to this BE is using the application
at the same time, can that lead to abnormaties/corruption? If yes, how to
avoid it?
5. What do you mean by "persistent connection open at all times"
6. Using the linked table manager, I tried to change the BE that the FE was
connected to...there are about 20 tables that I have and I had to do them one
by one...is there a way to change the location of tables at one time?
Thanks

 

[Guest]

I was able to take care of the few questions from my earlier posts: These
questions are Q2, Q6 but my biggest concern is still Q4. Also, I split my
database into FE on my computer and BE on a network drive. Running the
program in this split fashion has not led to any improvement in terms of
reducing time to run a query compared to running the query in an unspilt
database on the network drive. Actually it took a minute less to run the
report(query) on the unsplit database on the network drive. Also the time
taken to run a query when FE is in form of MDE is about the same as when FE
is not converted into an MDE. My main concern was speeding up my queries
before I split my database; given this, is there still a cause for splitting
the database or can I let the users run the reports off he unsplit database

 

[Guest]

The users of my database with backend on network and frontend on their
computer are complaining about the slow speed of running reports. I have got
only a few users and the users dont make any changes to tables. If I were to
install the entire unsplit database on each user's computer, they would be
able to able to run reports much faster. I have to make fortnighly additions
to these tables so I will have to update the database on their computers
every time but I have to update backend tables anyways with the existing
setup. I am hoping to get feedback and ideas on this.