AAAGGGHHHH!!!  Help ...
Please can two people give me the same answer to a question .....

We are writing a system in VB3.0 using an Access 2 database via the
compatibility layer. There will be about 10 people using this over
a network. I am concerned about the perfomance of the system

So far I've been told "no problems with performance mate .."
right through to "Oh my God !! Everything will stop dead"

Microsoft have now told me that using snapshots will *SLOW* the system
down as they will bring back more data accross the network than a
dynaset
(even though they say that snapshots are normally faster)

Does anyone have a clear idea about what we should or shouldn't be
doing
to ensure that we get a "performing" system  (assume we have done our
homework in terms of database design and systems design) ?

As a clue ... the main sticking points seem to be :-
  Dynasets vs Snapshots
  Multiple users updating one table
  Getting updates to indexes back to each PC

Thanks
A woefully confused Matt Guest

If you want to really speed things up with large recordsets, make sure
you are using VB Pro, and use the SEEK method directly on a Table
instead of using FindFirst etc. The difference here is huge.
                                        ,~,_/\
                                       /      \
                      Allen Browne    {        }
              Perth, WA, Australia =>  *_,~~\_/
http://odyssey.apana.org.au/~abrowne/        v

Now, the keys are:
1. DON'T keep the database open.  Get in and out a quickly as
possible.  If you are just reading data, open it readonly, get your
data, and close it.  If you are writting data, get all your data
together first then, open the database, open the table(s), write the
data (preferably using transactions), close everything.

2. Speed - Use indexes.  If complex queries are not required in a
situation, take a hard look at SEEK and table objects.  The bulk of
your speed problems will be the network, not VB/Access. Keep that in
mind.

What you are asking about can, and does, work and work well. Just
remember that the Access database on the server is just another file
and you are hitting it with (in your example) 10 different database
engines.

Frank....

: Does anyone have a clear idea about what we should or shouldn't be
: doing
: to ensure that we get a "performing" system  (assume we have done our
: homework in terms of database design and systems design) ?

I am in the middle of fixing an existing program that had many
performance problems as well.  They have a Novell NetWare 3.12 network
and are using VB3.0 and MSAccessJet 2.5 (this is the latest version of
the jet engine)

Here are some tips I figure out to increase performance:
1) Dynasets do indeed work faster under these conditions...
2) Avoid queryies that use too many joins.
   More notes on this:
     I have gotten the fastest performance under Access when I query one
table and one table only at a time.  Then, "manually" relate other tables
using different dynasets (a la xbase).  I know this is bad practice for a
relational database, but it works for access.  If you upgrade to Oracle
or Sybase or something, joins should work fine...
3) indexing: I know that too many indexes slow update speed, but I am
willing to risk a few milliseconds when updating (especially when screen
redraws are so damn slow to begin with) tables by adding a few more
indexes that may help a rare query or two...

First those who say the network will stand still are probably
incapable of writing a network app themselves.

Use dynasets for returning data for modification, snapshots if you
don't want the data to change once you've got it.  Don't allow users
to return more than a couple of hundred records at a time.  If you
give people open access they will ask for all records.  

Read and write one record at a time where possible.  Don't use large
open recorsets as an editing tool using the grid control.  If possible
don't use the data access controls.

                            _
                         ,,' ',
Noel Harland           ,~     |
Perth,                {  W.A. |
Australia              }      |

                       <_,--'''

Hope this helps.

Luis Duran

Tony
Tony Toews, Independent Computer Consultant
Jack of a few computer related trades and master (or certified) of none.
Microsoft Access Hints & Tips: Accounting Systems, Winfax Pro, Reports
and Books at http://www.agt.net/public/ttoews/accsmstr.htm

Our group at work has also gone down the same road of trying to develop a
network database using VB 4 and Access.  In our case we were developing a
document tracking system for a Loan Operations groups with 70 users.  Like
Luis we got what you might call acceptable performance pulling queries across
the network ie. 10-20 seconds.  We also had to build in special logic for
handling file locking of the database (Access is wholly inept in handling this
task).  In our final attempt to get the system into production everything
worked perfectly for about a day and  a half.  Then the database became
corrupted and if anyone tried to read the file their machine would lock up.

Since then we have switched to MS SQL Server 6.0 and I would recommend you or
anyone else considering a networked database application consider a SQL
alternative.  The conversion from Access to SQL Server was almost effortless.  
We have re-written the application to go the MS ODBC and it works great.  
Also, SQL server has facilities for importing Access databases and all sorts
of other goodies built in.  Finally, instead of 10-20 second performance per
query, you can expect sub-second response.  Pay the bucks and get the right
tool for the job, it'll keep a few grey hairs off your head.

Basil Evrenidis

If you're planning on doing a lot of searches on this recordset, consider
opening it as a table-type recordset(if possible).  That allows you to use the
Seek method for searching, which is much faster than FindFirst/FindNext.

(Unlike a dbase-style database, Access stores all tables and indexes in a single
file).

Hope this helps!

Eric Rossing
Intec Company, Inc.

http://www.msen.com/~rossing