Hi, database gurus,

I am new to database and appreciate if someone can help me on this. I
am developping a VB6 front end app (using DAO) with an Access2000 back
end which contains about ten tables. I have a copy of the backend on
my local computer while I am working on the frontend. For the several
queries I wrote so far, they behave just fine this way. But if I try
the queries with the backend on network server, the response time for
some of the queries is very long and one query just hangs up the
computer every time I try it.

Thanks in advance.

G. Du

This is because the Jet engine runs on the client PC and not on the
machine where the database is located. When you say something like
"SELECT * FROM TableX WHERE Type = 'B'", Jet will read the entire
contents of TableX (across the network if necessary)and then go
through picking out the qualifying rows. If the database is on the
local machine the overhead is just some disc accessing but, if the
database is on another machine, you also incur the network traffic
overhead.

So that's your problem. What you can do about it depends in what your
app is doing and how. Performance tuning Access is difficult because
of the way it works. If a query is slow the problem can be the
network, the table design, the query design, the machines involved.

Target the slow queries and examine them to see why they are slow.
Some of the things you could consider are (in no particular order):

 - Weed out-of-date rows from your tables to make them smaller
 - Denormalize some of the data so you don't need so many joins
 - Get a faster network
 - Put the database on a fast machine
 - Add indexes

I am by no means an expert on this so search somewhere like Google and
also MSDN. You will almost certainly find some really good advice.
Good luck with it.