Replicate or split database?

[Guest]

I have an Access 2003 database that is approaching 200 meg in size. It is
used by two different computers on a home network and is hosted by one of
them. Data changes by both users all day long.

Performance is slowing as the database gets larger. If I split the database
into front-end/back-end, I assume that performance will speed up. Or, I
could create a replica on the non-host computer and syncronize (several
times/day). Which is better, splitting database or replicating/synchronizing.

 

[Jeff Boyce]

A couple other (potential) issues first...

Have you used the Compact & Repair function? (be sure to back up your db
before running it)

Are you storing images in the db (Access "bloats" when you do)?

Regards

Jeff Boyce
<Access MVP>

I have an Access 2003 database that is approaching 200 meg in size. It is
used by two different computers on a home network and is hosted by one of
them. Data changes by both users all day long.

Performance is slowing as the database gets larger. If I split the database
into front-end/back-end, I assume that performance will speed up. Or, I
could create a replica on the non-host computer and syncronize (several
times/day). Which is better, splitting database or

replicating/synchronizing.

 

[Guest]

I regularly compact/repair. No, there are no images. The large size is
legitimate. Also, the forms are fairly complicated, as I understand it, they
are going through the network along with the data each tme the non-host users
opens them.

 

[John Vinson]

I have an Access 2003 database that is approaching 200 meg in size. It is
used by two different computers on a home network and is hosted by one of
them. Data changes by both users all day long.

Performance is slowing as the database gets larger. If I split the database
into front-end/back-end, I assume that performance will speed up. Or, I
could create a replica on the non-host computer and syncronize (several
times/day). Which is better, splitting database or replicating/synchronizing.

By all means, split. Multiple users sharing the same unitary database
is risky - both for bloat, and for the risk of corruption of the
database. Replication is infinitely more complex; it is valueable and
useful, in its place. But its place is when the two databases are not,
and cannot be, connected on a LAN; for instance, databases at
different geographical locations, or one on a mobile laptop.

Split the database using the database splitter wizard; each user
should have their own copy of the frontend, and the backend should be
on a shared drive. Compact it regularly, and of course keep good
backups.


John W. Vinson[MVP]

 

[Jeff Boyce]

I'm with John, split it. If you're pumping complex forms through the
network, you risk corruption, as he pointed out, and slower performance, as
you have.

By the way, a complex form could still load quickly if it isn't bound to a
large dataset (table, query). Are your forms bound to a full dataset, or
only loading the one record that is needed?

Jeff Boyce
<Access MVP>