Replication and splitting for Dummies

[Guest]

I have read several very helpful posts about replication and splitting but
would love someone to confirm I'm on the right track before I go ahead:

I have an Access DB (v2003) with macros set up to log drill core data to
tablet PC's in the field. I would like to update a main DB on the network
from the tablets (currently up to 4 tablets).
Ideally when syncronizing, the data from the tablet will be copied into the
main DB on the PC and any updates to the forms, queries etc on the main PC
are copied onto the replica tablet. I believe replicating the entire DB can
cause issues during sync, corrupting the DB so...
I must first split the DB into a FE and BE. Copy the FE onto each tablet
and replicate the BE for each of the tablets. Any chages to the FE on the
master will not auto update and the new version will have to be copied onto
each tablet.

Is this correct?

Finally will the entire BE data be on each laptop after syncing or can it be
made so only recent/current data (ie the current drill hole being logged) is
left on the tablet.

Thanks and apologies for the long explaination.

 

[John Vinson]

I have read several very helpful posts about replication and splitting but
would love someone to confirm I'm on the right track before I go ahead:

Note that Replication and Splitting are two ALTOGETHER different
techniques. They can be used together - you might need to Replicate
the backend of a split database (never the frontend) if users not on
the same network need to share the data.

I have an Access DB (v2003) with macros set up to log drill core data to
tablet PC's in the field. I would like to update a main DB on the network
from the tablets (currently up to 4 tablets).

That's a case for replication... *of the backend*.

Ideally when syncronizing, the data from the tablet will be copied into the
main DB on the PC and any updates to the forms, queries etc on the main PC
are copied onto the replica tablet. I believe replicating the entire DB can
cause issues during sync, corrupting the DB so...
I must first split the DB into a FE and BE. Copy the FE onto each tablet
and replicate the BE for each of the tablets. Any chages to the FE on the
master will not auto update and the new version will have to be copied onto
each tablet.

Is this correct?

Yes. And well expressed as well!

Finally will the entire BE data be on each laptop after syncing or can it be
made so only recent/current data (ie the current drill hole being logged) is
left on the tablet.

You would need to run Delete queries to delete the "old" data; I
haven't done enough replication to be certain how that would interact
with later synchronization. It sounds like you want to keep only
recent data on some replicas, and all historic data on another replica
(the "data master" let's call it...?); I'm sure this can be done but
I'd want to pick MichKa's brain on just how! See if there is
discussion of the subject at http://www.trigeminal.com, the mother
lode of replication information.

John W. Vinson[MVP]

 

[Guest]

Thanks for the help. I've just tested this on a tablet and have found the FE
is not updating the BE (syncing however seems to work fine but then again
there are no changes yet).

Do I have to create links between the FE and BE on the tablet to get the
data into the BE of the database?

I assumed these links were formed during the split.

 

[John Vinson]

Thanks for the help. I've just tested this on a tablet and have found the FE
is not updating the BE (syncing however seems to work fine but then again
there are no changes yet).

Do I have to create links between the FE and BE on the tablet to get the
data into the BE of the database?

I assumed these links were formed during the split.

They are, but if you move the frontend to a different computer (your
tablet) you may need to use Tools... Database Utilities... Linked
Table Manager to reconnect.

I'm really unclear here whether you're talking about Replication
("syncing") or Linking (frontend/backend). These are ENTIRELY
different processes. Replication should be done ONLY on the backend
(the tables); if you need a replicated database in order to take the
tablet/laptop away from the network, then you should have the split
database, front and back ends, on *both* the tablet and the base
station; the two backends would be in a replication group and you'ld
sync them when the portable device is attached to the network. The
frontend need not and should not be replicated.

John W. Vinson[MVP]

 

[David W. Fenton]

Note that Replication and Splitting are two ALTOGETHER different
techniques. They can be used together - you might need to
Replicate the backend of a split database (never the frontend) if
users not on the same network need to share the data.

It's not just that they *can* be used together. It's that they
*must* be used together. The reason for that is that Jet Replication
simply doesn't work in the long run for the Access project. It was
designed for pure Jet objects (tables and queries) and works
beautifullly with those. But for Access objects
(forms/reports/macros/modules), it causes problems and can easily
get so corrupted that you lose the entire project. The Jet
Replication White Papers all recommend using replication as a viable
method of pushing out front end updates, but they are wrong, as
you'll see if you look at the Jet 4 Replication FAQ, which
recommends against it. See (all on one line, unwrapped):

http://www.dfenton.com/DFA/Replication/index.php/Things_not_to_believ
e_in_the_MS_documentation

The problems with replicating front ends were exacerbated with the
release of Access 2000, where the entire Access project is stored as
a BLOB in a single system table (this is also the reason for the
switch to the monolithic save model, and the loss of individual
modified/dates for Access objects). If you consider that several
changes to objects in your front end are actually going to be
propagated as multiple edits to a single record, and those edits
have to be reconciled between the two sides of the synchronization
(properties like filters and so forth get saved, too, and will get
synched in both directions), you will see why this is dangerous.

The split architecture is the only viable architecture for a
replicated Access database.

Of course, I'd also say it's the only viable architecture for any
multi-user database, replicated or not.

[]

You would need to run Delete queries to delete the "old" data; I
haven't done enough replication to be certain how that would
interact with later synchronization. It sounds like you want to
keep only recent data on some replicas, and all historic data on
another replica (the "data master" let's call it...?);

If you delete data from one replica, it will be deleted from each
other replica that synchs with it, so, no, this can't be done.

There are two issues here:

1. restricting the data in a particular replica to the data
generated at that replica, AND

2. filtering that data to the recent data.

The former can be done with partial replicas, where you filter which
data goes into a particular replica. However, I see no point in
using partial replicas, because then you lose the backup redundancy
of having lots of spread-out replicas with all your data in them
(it's kind of like the issue you have with full, differential and
incremental backups in regular backup software -- the latter two
take up less space and are faster to write, but it's much harder to
recover when you need to restore data, since you have to work from a
whole bunch of backup sets). If you don't want the end users to see
anything but their own data, that's easy enough to implement in the
front end, filtering on whatever criteria brand their records as
belonging to them.

As to the second issue, the filtering can be done in the
application. Unless you're running up against the 2GB data limit,
there really is no issue with performance or storage space, even
with 100s of thousands of records. Yes, you'll need your data tables
appropriately indexed, but once that's done, data retrieval should
be blazingly fast in any context in which you're filtering the data
set presented to the user (in a form or a report).

I'm sure this can be done but
I'd want to pick MichKa's brain on just how! See if there is
discussion of the subject at http://www.trigeminal.com, the mother
lode of replication information.

There is no way to restrict data in a replica by a changing piece of
criteria. Once the data is in the replica, it can't be removed
without it also being removed from all the other replicas that had
that data in them.

It really ought to be a complete non-issue if proper Jet user-level
security is put in place. If that's done, then all the restrictions
would be done in the application itself, and end users would have no
access to data that wasn't theirs.

 

[Guest]

They are, but if you move the frontend to a different computer (your
tablet) you may need to use Tools... Database Utilities... Linked
Table Manager to reconnect.

I'm really unclear here whether you're talking about Replication
("syncing") or Linking (frontend/backend). These are ENTIRELY
different processes. Replication should be done ONLY on the backend
(the tables); if you need a replicated database in order to take the
tablet/laptop away from the network, then you should have the split
database, front and back ends, on *both* the tablet and the base
station; the two backends would be in a replication group and you'ld
sync them when the portable device is attached to the network. The
frontend need not and should not be replicated.

John W. Vinson[MVP]

John, That's exactly what I have done. Created a replica of the BE on the
tablet, and a copy of the FE on the tablet. I assume I do need to relink the
FE-BE after the move as you said and it should hopefully fix the problem. By
the syncing I was refering to the replicated BE.

Thanks again.

 

[Guest]

John,

I noticed that my links between the fe and be are not forming during the
split. I got an warning message when i split the db:
....database engine could not lock table 'switchboard items' because it is in
use by another person or process'

Followed by:
Invalid procedure call or argument.

I tried closing the switchboard but this shuts the db. I also relinked the
be to the fe with file>getdata... but this just gave me two of every table,
one linked and one not.

Any clues as to where i am going wrong?

 

[John Vinson]

John,

I noticed that my links between the fe and be are not forming during the
split. I got an warning message when i split the db:
...database engine could not lock table 'switchboard items' because it is in
use by another person or process'

Followed by:
Invalid procedure call or argument.

I tried closing the switchboard but this shuts the db. I also relinked the
be to the fe with file>getdata... but this just gave me two of every table,
one linked and one not.

Any clues as to where i am going wrong?

AHA! It's not you, it's the Database Splitter wizard not talking to
the Switchboard wizard. The Switchboard requires that the table named
Switchboard be a *local* (frontend) table; the splitter goes ahead and
moves it to the backend anyhow.

Open the frontend, *holding down the Shift key*. Delete all the
imported backend tables (after making sure that they still exist in
the backend) except Switchboard; delete the link to Switchboard (at
some point you'll want to delete the Switchboard table from the
backend). Compact the frontend. This should let you get around this
problem.

John W. Vinson[MVP]

 

[aaron.kempf]

Splitting and replication inside of MDB is a joke.

Just use Access Data Projects.. you don't have 'could not lock table'
errors when you use Access Data Projects

MDB is for losers and retards and people that are too senile to learn
SQL Server

 

[aaron.kempf]

oh yeah.. sorry

so you're saying not to split ALL of the tables; you just split SOME of
the tables?

What a laughable strategy

KEEP ALL YOUR TABLES AND QUERIES IN ONE DATABASE: USE ACCCESS DATA
PROJECT INSTEAD OF DEALING WITH ALL OF THIS _CRAP_

 

[Guest]

John, thanks for getting back to me so quickly. I'll run you through what I
have done to see if I've understood, it appears to be working though.

The FE and BE both had the switchboard and all tables and no links. I
removed the switchboard from the BE and removed all tables but the
switchboard on the FE. I then created links using the 'get external data'
function to all the tables in the BE database.

The database is a bit slower to open but runs fine once inside. From my
earlier reading I believe this is common.

Does this sound to be set up as it should be?

Cheers

 

[John Vinson]

John, thanks for getting back to me so quickly. I'll run you through what I
have done to see if I've understood, it appears to be working though.

The FE and BE both had the switchboard and all tables and no links. I
removed the switchboard from the BE and removed all tables but the
switchboard on the FE. I then created links using the 'get external data'
function to all the tables in the BE database.

That should work. Several ways to get there but... you need the
Switchboard table as the only local table in the frontend, and all the
other tables linked.

The database is a bit slower to open but runs fine once inside. From my
earlier reading I believe this is common.

Yep. See Tony Toews' performance FAQ at
http://www.granite.ab.ca/access for suggestions about how to keep the
speed adequate.

John W. Vinson[MVP]

 

[Guest]

Thanks very much for you help. Much appreciated.