Access Accross A Network 
Author Message
 Access Accross A Network

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



Fri, 11 Sep 1998 03:00:00 GMT
 Access Accross A Network

Quote:

> 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[snip]

I presume you have split the mdb in two: a data mdb, containing only
the tables, and residing on a single PC, and a local code mdb containing
all the other objects (queries, forms, ...) residing on each PC and
attached to the tables in the data mdb. This will enhance performance,
reduce network traffic, and make it easy to update your code mdb on each
PC without affecting the data.

Quote:
> As a clue ... the main sticking points seem to be :-
>   Dynasets vs Snapshots

Use dynasets where possible, though this may not be as big an issue
as you think (unless your recordsets are particularly large).

Quote:
>   Multiple users updating one table

With appropriate record locking, multiple users updating one table
will not be a big problem for you. The only real snag occurs if you
want them to all add new records simultaneously: Access won't be able
to lock the end of the table for them all.

Quote:
>   Getting updates to indexes back to each PC

Again, unless you envision a frantic amount of adding new records, I
wouldn't let this stop me indexing the fields that will regularly be
used for sorting/filtering. In practice, you may find this is a non-
issue as well. It's probably just as important to periodically
compact the database and defrag the drive.

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



Sat, 12 Sep 1998 03:00:00 GMT
 Access Accross A Network

Quote:

>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
>As a clue ... the main sticking points seem to be :-
>  Dynasets vs Snapshots

Yes, Dynasets can be faster depending upon what you are bringing back.
Experiment since each situation can be different. Also, take a hard
look at the table object and SEEKs.

Quote:
>  Multiple users updating one table

In this case, you will get a trappable error returned. Trap it, pause,
retry a few times (I use 5 attempts), then prompt the user to try
again it the update did not get accomplished.  The retry loop usually
takes care of it so you should see few "Please try again" prompts.

Quote:
>  Getting updates to indexes back to each PC

If everyone is going against the same db on the system, there is no
need to bring anything back.

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....




Sat, 12 Sep 1998 03:00:00 GMT
 Access Accross A Network

: 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...



Sat, 12 Sep 1998 03:00:00 GMT
 Access Accross A Network

Quote:

>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

Hi Matt,

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              }      |

                       <_,--'''



Sun, 13 Sep 1998 03:00:00 GMT
 Access Accross A Network

says...

Quote:

>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

We had a system using an Access 2.0 Database over a Network. This was our
setup:
Access 2.0 Database aproximate size of database was 80Mb
The database was accessed across a network from Windows for Workgroups machines
each machine was 486DX2-66 with 8 megs of ram. There were between 10-20 systems
using the database at any one time.
Whenever I pulled data accross that did not need to be modified I used
snapshots. Although I did not notice a huge difference when using Dynasets.
Indexes did increase performance quite noticeably. A query retrieving 500
records would take 10-20 seconds to pull all the data across, with an index as
opposed to 5 minutes without an index.
Performance was overall pretty good. The one problem with this setup was the
database becoming corrupt. This happened way too often and could take up to 30
minutes to repair.
  We finally installed MS-SQL Server and inproved performance and practically
eliminated down time.

Hope this helps.

Luis Duran



Mon, 14 Sep 1998 03:00:00 GMT
 Access Accross A Network

Quote:

>  We finally installed MS-SQL Server and inproved performance and practically
>eliminated down time.

How much effort is it to convert the data to a SQL Server and to
convert the Apps to point to the new SQL Server?

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



Thu, 17 Sep 1998 03:00:00 GMT
 Access Accross A Network

Quote:

>says...

>>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

>We had a system using an Access 2.0 Database over a Network. This was our
>setup:
>Access 2.0 Database aproximate size of database was 80Mb
>The database was accessed across a network from Windows for Workgroups machines
>each machine was 486DX2-66 with 8 megs of ram. There were between 10-20 systems

>using the database at any one time.
>Whenever I pulled data accross that did not need to be modified I used
>snapshots. Although I did not notice a huge difference when using Dynasets.
>Indexes did increase performance quite noticeably. A query retrieving 500
>records would take 10-20 seconds to pull all the data across, with an index as
>opposed to 5 minutes without an index.
>Performance was overall pretty good. The one problem with this setup was the
>database becoming corrupt. This happened way too often and could take up to 30
>minutes to repair.
>  We finally installed MS-SQL Server and inproved performance and practically
>eliminated down time.

>Hope this helps.

>Luis Duran

Matt,

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



Tue, 22 Sep 1998 03:00:00 GMT
 Access Accross A Network

Quote:

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

I doubt it!  :)

Quote:
>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)

It depends on the dataset size.  As I recall from one of the VB4 books(I can't
remember exactly where I read this), they say that a Snapshot-type is faster for
about 500 records or less.  At that point, it becomes more time-efficient to use
a Dynaset.  A snapshot pulls down ALL records when it opens.  A dynaset pulls
down records on-demand.  If your dataset is less than 500 or so records, I'd use
a snapshot.  Otherwise, use a dynaset.

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.

Quote:
>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

Run your program in "test" for a while to get an idea of what record-lock
conflict errors you will get.  Have your code check for these errors and decide
whether to try again, ignore the result, or display a message.

Quote:
>  Getting updates to indexes back to each PC

If you keep the database on the server, the indexes will be on the server, in
the .MDB file.  There is no way to separate them.

(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



Fri, 25 Sep 1998 03:00:00 GMT
 
 [ 9 post ] 

 Relevant Pages 

1. Paradox 7 on accross a Network

2. Setting up Interbase accross a network - Help Please!

3. VFP 6.0 slow accross network

4. Long Checkpoints, balancing network traffic accross multiple Aliases

5. transferring records accross a network (vb6 w/access97)

6. How to detect if a db is served accross a TCP network

7. Can't connect to MSDE accross network

8. Users access accross servers.

9. SQL Server access on Novell network without Client for MS Networks

10. Reading Access 2.0 db on network w/read-only access

11. MS Access 7 Database on Network and VB5 Multi User Access Problem

12. SQL 7 DTS cannot access an MS Access database on the network


 
Powered by phpBB® Forum Software