Access performance

[Guest]

hello
my question is about if i sould be using access, or if i should be using
other programs to do the job at my factory.
working with access trought the network is a bit slow, with frontends and
backends working with link tables.
is there any program that u would recomend to use? is access able to do any
type of operation? is visual Fox Pro better than Access? or i should be
looking at something like Sql Server?

thanks for your opinion..

 

[Albert D. Kallal]

You don't mention how fast your network is, and how large your data tables
are (in terms of record counts).

You also don't mention the number of users.

I mean, you might need to haul some goods, but is a small compact car good
enough....or do you need a Mack truck? Well, to deliver a small parcel..you
don't need a Mac truck.

You also need to realize that ms-access is a development tool. It lets you
build forms, reports etc. Something like sql-server does not let you build
forms or build a user interface to the user (you can use ms-access for
that).

So, when using ms-access, you can choose what data engine you want. you can
use Oracle, or sql server for the data part, and continue to use ms-access.

Have you yet outgrown the JET engine? Hard to tell with the amount of
information you gave (by the way, the "JET" data engine is the default
engine you use with ms-access, but as I mentioned, you can use Oracle, or
ms-sql server with ms-access for the data part, and continue to use
ms-access as the front end. This kind of setup can easily scale to a 100+
users with no problems.

So, if you got only 5-15 users, and very small data tables in the 60,000 to
150,000 record range, and say only about 10-50 related tables, then response
times, and form load times should be well under the 2 second time mark. In
fact, response times should be near instant for most operations. If your
tables are in this small data set size range, and your application runs
slow, then perhaps the designs and developers are suspect. It is very hard
to tell with the information you given so far as know if you need to replace
the back end database with a server based engine like sql server.

 

[Guest]

thanks.

i think i may be doing something very wrong...
is the back end as to be in any special location?? because i copy it to a
simple folder in the network!! how to outgrown the JET engine?? my be is a
simple .mdb file with the tables only. my fe is .mdb file with link tables to
be. the size of the tables will never get sow big as 60000 and the number of
users will never be bigger than 15. what m i missing? if the question has to
much to answer, where can i find manuals to explain how to build o proper fe
be system..

thanks

 

[John Vinson]

thanks.

i think i may be doing something very wrong...
is the back end as to be in any special location?? because i copy it to a
simple folder in the network!! how to outgrown the JET engine?? my be is a
simple .mdb file with the tables only. my fe is .mdb file with link tables to
be. the size of the tables will never get sow big as 60000 and the number of
users will never be bigger than 15. what m i missing? if the question has to
much to answer, where can i find manuals to explain how to build o proper fe
be system..

You might want to check out some of the suggestions at

http://www.granite.ab.ca/access/performancefaq.htm

Tony Toews has compiled a lot of good suggestions for improving the
performance of a networked Access application. For a database the size
you're describing, Access should be quite capable of handling the
data.

OF course, "fast enough" depends on expectations as well as the raw
performance. I've had users who were ecstatic at 30-second response
(compared to the many minutes from their prior mainframe system) and
others who grumbled loudly because there was a perceptible half-second
or so lag filling the screen.

John W. Vinson[MVP]

 

[Albert D. Kallal]

Ok, well 60,000 reocrds is a very small data file indeed.

We have several things that can dramatically effect performance here.

The first one is the network. Are we talking about a LAN, or a WAN. I
explain the performance problems (and solutions) here:

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

So, after reading the above, if you network is a standard 100 base network,
then your network is likely ok.

The next thing to check is your software setup and install correct. The
following list of tips needs to be checked,a nd some of the tips will
increase operational speed by as much as 50 to 100 times. Check out the
following list:

http://www.granite.ab.ca/access/performancefaq.htm

The next thing to check to ensure that you have a split database, and that
EACH workstation gets the front end part. And, those front ends should be a
mde.

You can read up on splitting here:

http://www.granite.ab.ca/access/splitapp.htm

So, up to this point, we have not done ANY changes to the application, but
have just got a CORRECT setup. Just like working on a car that has problems,
often a mechanic will change the spark plugs, air filter..and few others
things. Only AFTER that point, can now the real performance trouble shooting
occur, and the same applies to ms-access.

The #1 tip from the above (if you are split) is to keep a persistent
conneciton open.

If after doing all of the above steps, the next part is that of optimizing
the application, and looking at the designs of the software to see if they
are the problem (and, if all the above stuff has been done and performance
is still slow, then software/design is the problem).