Conversion to adp

[John]

Hi

I am converting my access front-end/backend mdb app to adp. Are there any
pitfalls I should be aware of?

Thanks

Regards

[Alan Webb]

Re: Conversion to adp

John,
1. If the new back-end is SQL Server and the old back-end was Jet/Access
then much and perhaps all of the SQL in service may need to be revisited to
ensure it runs as intended on SQL Server. Things like DCount, etc., don't
exist on SQL Server. IIf() is implemented as CASE and works differently.
Date() is GETDATE(), "" has to be '', lots of little details are different.
2. A straight dump of Jet SQL with bug fixes & workarounds to just make the
existing SQL function on SQL Server risks really crappy performance on SQL
Server because it's possible to cause Access to do some really boneheaded
translation of SQL into something that will run on SQL Server.
3. TransactSQL is lightyears more capable than JetSQL & VBA. The
performance & stability gains possible will come from a thorough review of
the business processes implemented in Access and a study of the best way to
implement them on SQL Server.

--
Alan Webb
knoNOgeek@SPAMh otmail.com
"It's not IT, it's IS
"John" <John@nospam.in fovis.co.uk> wrote in message
news:426b927f$0 $290$cc9e4d1f@n ews-text.dial.pipex .com...[color=blue]
> Hi
>
> I am converting my access front-end/backend mdb app to adp. Are there any
> pitfalls I should be aware of?
>
> Thanks
>
> Regards
>[/color]

[Lyle Fairfield]

Re: Conversion to adp

John wrote:
[color=blue]
> I am converting my access front-end/backend mdb app to adp. Are there any
> pitfalls I should be aware of?[/color]

Only a few minor ones:

ADPs are unreliable, exhibit erratic anomalies (they happen one day but
not the next), open many connections (all of which are wonderfully
slow), have difficulty in opening a form at a specific record, have
recurring and sometimes unsolvable updating problems, require arcane
procedures for report filtering, and create a vulnerable insecure route
to the database.

Other than that they're fabulous.

Did I mention that they make your data insecure?
that's INSECURE
I-N-S-E-C-U-R-E!

--
--
Lyle

"The aim of those who try to control thought is always the same. They
find one single explanation of the world, one system of thought and
action that will (they believe) cover everything; and then they try to
impose that on all thinking people."
- Gilbert Highet

[John]

Re: Conversion to adp

I need the sql server backend to establish replication/sync with some laptop
users who would like to disconnect and then sync when they reconnect. In my
case what is the solution? Should I keep the mdb front-end and link the
tables off sql server? Anything else?

Thanks

Regards

"Lyle Fairfield" <lylefair@yahoo .ca> wrote in message
news:0PNae.1411 2$If1.3784198@r ead2.cgocable.n et...[color=blue]
> John wrote:
>[color=green]
>> I am converting my access front-end/backend mdb app to adp. Are there any
>> pitfalls I should be aware of?[/color]
>
> Only a few minor ones:
>
> ADPs are unreliable, exhibit erratic anomalies (they happen one day but
> not the next), open many connections (all of which are wonderfully slow),
> have difficulty in opening a form at a specific record, have recurring and
> sometimes unsolvable updating problems, require arcane procedures for
> report filtering, and create a vulnerable insecure route to the database.
>
> Other than that they're fabulous.
>
> Did I mention that they make your data insecure?
> that's INSECURE
> I-N-S-E-C-U-R-E!
>
> --
> --
> Lyle
>
> "The aim of those who try to control thought is always the same. They find
> one single explanation of the world, one system of thought and action that
> will (they believe) cover everything; and then they try to impose that on
> all thinking people."
> - Gilbert Highet[/color]

[Lyle Fairfield]

Re: Conversion to adp

I yield to the replication experts.

John wrote:[color=blue]
> I need the sql server backend to establish replication/sync with some laptop
> users who would like to disconnect and then sync when they reconnect. In my
> case what is the solution? Should I keep the mdb front-end and link the
> tables off sql server? Anything else?
>
> Thanks
>
> Regards
>
> "Lyle Fairfield" <lylefair@yahoo .ca> wrote in message
> news:0PNae.1411 2$If1.3784198@r ead2.cgocable.n et...
>[color=green]
>>John wrote:
>>
>>[color=darkred]
>>>I am converting my access front-end/backend mdb app to adp. Are there any
>>>pitfalls I should be aware of?[/color]
>>
>>Only a few minor ones:
>>
>>ADPs are unreliable, exhibit erratic anomalies (they happen one day but
>>not the next), open many connections (all of which are wonderfully slow),
>>have difficulty in opening a form at a specific record, have recurring and
>>sometimes unsolvable updating problems, require arcane procedures for
>>report filtering, and create a vulnerable insecure route to the database.[/color][/color]
--
Lyle

"The aim of those who try to control thought is always the same. They
find one single explanation of the world, one system of thought and
action that will (they believe) cover everything; and then they try to
impose that on all thinking people."
- Gilbert Highet

[MacDermott]

Re: Conversion to adp

There's a whole newsgroup at microsoft.publi c.access.replic ation.
It can certainly be done in Access, without requiring SQL Server.

"Lyle Fairfield" <lylefair@yahoo .ca> wrote in message
news:xEOae.2565 $K24.647@read1. cgocable.net...[color=blue]
> I yield to the replication experts.
>
> John wrote:[color=green]
> > I need the sql server backend to establish replication/sync with some[/color][/color]
laptop[color=blue][color=green]
> > users who would like to disconnect and then sync when they reconnect. In[/color][/color]
my[color=blue][color=green]
> > case what is the solution? Should I keep the mdb front-end and link the
> > tables off sql server? Anything else?
> >
> > Thanks
> >
> > Regards
> >
> > "Lyle Fairfield" <lylefair@yahoo .ca> wrote in message
> > news:0PNae.1411 2$If1.3784198@r ead2.cgocable.n et...
> >[color=darkred]
> >>John wrote:
> >>
> >>
> >>>I am converting my access front-end/backend mdb app to adp. Are there[/color][/color][/color]
any[color=blue][color=green][color=darkred]
> >>>pitfalls I should be aware of?
> >>
> >>Only a few minor ones:
> >>
> >>ADPs are unreliable, exhibit erratic anomalies (they happen one day but
> >>not the next), open many connections (all of which are wonderfully[/color][/color][/color]
slow),[color=blue][color=green][color=darkred]
> >>have difficulty in opening a form at a specific record, have recurring[/color][/color][/color]
and[color=blue][color=green][color=darkred]
> >>sometimes unsolvable updating problems, require arcane procedures for
> >>report filtering, and create a vulnerable insecure route to the[/color][/color][/color]
database.[color=blue]
> --
> Lyle
>
> "The aim of those who try to control thought is always the same. They
> find one single explanation of the world, one system of thought and
> action that will (they believe) cover everything; and then they try to
> impose that on all thinking people."
> - Gilbert Highet[/color]

[Steve Jorgensen]

Re: Conversion to adp

From the quesitons you are asking, I'm assuming your situation is that you
have a central LAN with a connection to the internet, laptop users who will
have Internet access where they're trying to do their remote work, and an IT
administrator capable of managing your LAN's firewall ports, etc. If that's
the case, then your best bet is probably to use some kind of remote control
software and allow users to run their desktop systems from remote.

If remote control will not work for you, then your next best choice is
probably replication since Access may not handle the flakey SQL Server
connections you may get over the Internet, and you'll have to add a VPN layer
for security or live with the lack of security (probably not a good idea).

Note that Access replicaiton may or may not be the best way to do replication.
Sometimes, it's better to implement a replication scheme at the application
level. One way to do this is to implement a record ID generation system that
includes a machine identifier so there can't be collisions between IDs
generated on different machines, and add fields for a current and previous
record revision ID so you can tell if a record in the central database is the
same revision that a remote system made a change to while off-line, and allow
the change to post if so.

On Sun, 24 Apr 2005 15:49:14 +0100, "John" <John@nospam.in fovis.co.uk> wrote:
[color=blue]
>I need the sql server backend to establish replication/sync with some laptop
>users who would like to disconnect and then sync when they reconnect. In my
>case what is the solution? Should I keep the mdb front-end and link the
>tables off sql server? Anything else?
>
>Thanks
>
>Regards
>
>"Lyle Fairfield" <lylefair@yahoo .ca> wrote in message
>news:0PNae.141 12$If1.3784198@ read2.cgocable. net...[color=green]
>> John wrote:
>>[color=darkred]
>>> I am converting my access front-end/backend mdb app to adp. Are there any
>>> pitfalls I should be aware of?[/color]
>>
>> Only a few minor ones:
>>
>> ADPs are unreliable, exhibit erratic anomalies (they happen one day but
>> not the next), open many connections (all of which are wonderfully slow),
>> have difficulty in opening a form at a specific record, have recurring and
>> sometimes unsolvable updating problems, require arcane procedures for
>> report filtering, and create a vulnerable insecure route to the database.
>>
>> Other than that they're fabulous.
>>
>> Did I mention that they make your data insecure?
>> that's INSECURE
>> I-N-S-E-C-U-R-E!
>>
>> --
>> --
>> Lyle
>>
>> "The aim of those who try to control thought is always the same. They find
>> one single explanation of the world, one system of thought and action that
>> will (they believe) cover everything; and then they try to impose that on
>> all thinking people."
>> - Gilbert Highet[/color]
>[/color]

[John]

Re: Conversion to adp

Is access replication reliable enough?

Regards

"MacDermott " <macdermott@nos pam.com> wrote in message
news:VFRae.1263 2$go4.10605@new sread2.news.atl .earthlink.net. ..[color=blue]
> There's a whole newsgroup at microsoft.publi c.access.replic ation.
> It can certainly be done in Access, without requiring SQL Server.
>
> "Lyle Fairfield" <lylefair@yahoo .ca> wrote in message
> news:xEOae.2565 $K24.647@read1. cgocable.net...[color=green]
>> I yield to the replication experts.
>>
>> John wrote:[color=darkred]
>> > I need the sql server backend to establish replication/sync with some[/color][/color]
> laptop[color=green][color=darkred]
>> > users who would like to disconnect and then sync when they reconnect.
>> > In[/color][/color]
> my[color=green][color=darkred]
>> > case what is the solution? Should I keep the mdb front-end and link the
>> > tables off sql server? Anything else?
>> >
>> > Thanks
>> >
>> > Regards
>> >
>> > "Lyle Fairfield" <lylefair@yahoo .ca> wrote in message
>> > news:0PNae.1411 2$If1.3784198@r ead2.cgocable.n et...
>> >
>> >>John wrote:
>> >>
>> >>
>> >>>I am converting my access front-end/backend mdb app to adp. Are there[/color][/color]
> any[color=green][color=darkred]
>> >>>pitfalls I should be aware of?
>> >>
>> >>Only a few minor ones:
>> >>
>> >>ADPs are unreliable, exhibit erratic anomalies (they happen one day but
>> >>not the next), open many connections (all of which are wonderfully[/color][/color]
> slow),[color=green][color=darkred]
>> >>have difficulty in opening a form at a specific record, have recurring[/color][/color]
> and[color=green][color=darkred]
>> >>sometimes unsolvable updating problems, require arcane procedures for
>> >>report filtering, and create a vulnerable insecure route to the[/color][/color]
> database.[color=green]
>> --
>> Lyle
>>
>> "The aim of those who try to control thought is always the same. They
>> find one single explanation of the world, one system of thought and
>> action that will (they believe) cover everything; and then they try to
>> impose that on all thinking people."
>> - Gilbert Highet[/color]
>
>[/color]

[Alan Webb]

Re: Conversion to adp

John,
It was good enough for a Y2K inventory done at a financial services company
I worked for back when I was getting paid for this stuff. As Steve points
out, there are cases where writing your own replication code ends up being
cheaper/better than what Microsoft provides in Access without code.
--
Alan Webb
knoNOgeek@SPAMh otmail.com
"It's not IT, it's IS"

"John" <John@nospam.in fovis.co.uk> wrote in message
news:426bb1f2$0 $291$cc9e4d1f@n ews-text.dial.pipex .com...[color=blue]
>I need the sql server backend to establish replication/sync with some
>laptop users who would like to disconnect and then sync when they
>reconnect. In my case what is the solution? Should I keep the mdb front-end
>and link the tables off sql server? Anything else?
>
> Thanks
>
> Regards
>
> "Lyle Fairfield" <lylefair@yahoo .ca> wrote in message
> news:0PNae.1411 2$If1.3784198@r ead2.cgocable.n et...[color=green]
>> John wrote:
>>[color=darkred]
>>> I am converting my access front-end/backend mdb app to adp. Are there
>>> any pitfalls I should be aware of?[/color]
>>
>> Only a few minor ones:
>>
>> ADPs are unreliable, exhibit erratic anomalies (they happen one day but
>> not the next), open many connections (all of which are wonderfully slow),
>> have difficulty in opening a form at a specific record, have recurring
>> and sometimes unsolvable updating problems, require arcane procedures for
>> report filtering, and create a vulnerable insecure route to the database.
>>
>> Other than that they're fabulous.
>>
>> Did I mention that they make your data insecure?
>> that's INSECURE
>> I-N-S-E-C-U-R-E!
>>
>> --
>> --
>> Lyle
>>
>> "The aim of those who try to control thought is always the same. They
>> find one single explanation of the world, one system of thought and
>> action that will (they believe) cover everything; and then they try to
>> impose that on all thinking people."
>> - Gilbert Highet[/color]
>
>[/color]

[David W. Fenton]

Re: Conversion to adp

Steve Jorgensen <nospam@nospam. nospam> wrote in
news:105o61t3cb o0qese9r3up25j2 bnv7tj7lj@4ax.c om:
[color=blue]
> Note that Access replicaiton may or may not be the best way to do
> replication. Sometimes, it's better to implement a replication
> scheme at the application level. One way to do this is to
> implement a record ID generation system that includes a machine
> identifier so there can't be collisions between IDs generated on
> different machines, and add fields for a current and previous
> record revision ID so you can tell if a record in the central
> database is the same revision that a remote system made a change
> to while off-line, and allow the change to post if so.[/color]

This is an enormously difficult task, even if it is only one-way
between a mere two copies of the data file.

I've done it. It's complicated (think about how deletions are
propagated; think about order of inserts and referential integrity).

If you're trying to have multiple dbs in the field, all being
updated, and you want those changes pushed up to the server, and you
also want to make the changes made by people on the servers to be
pulled down to the db in the field, it becomes a hugely complicated
task, unless a few conditions are met:

1. no records are edited in more than one location.

2. each person in the field has their own dataset that they work on
alone, and no other people actually edit that data (though they may
view it and analyze it).

But even then, you have to solve the PK issue. You have three
choices:

1. use a natural key, and run the risk of the same natural key being
used in two different copies of the database.

2. pre-allocated blocks of surrogate keys to each copy of the
database.

3. include a source db identifier in a compound PK in every table.

But if everyone's working on the same datasets, it becomes nearly
impossible to program from scratch.

Keep in mind that, theoretically speaking, there is a form of
heterogenous replication where the main mother ship is a SQL Server
db and the laptops have Jet dbs that synchronize with the SQL
Server. However, like pure SQL Server replication itself, the whole
scenario is much more limited than the capabilities of pure Jet
replication, and the rules much more strict.

I don't think replication is the answer here.

I think the problem needs to be completely re-thought from the
ground up.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

Re: Conversion to adp

"Alan Webb" <knoNOgeek@hotS PAMmail.com> wrote in
news:moWdneuwfK NRr_HfRVn-iA@comcast.com:
[color=blue]
> As Steve points
> out, there are cases where writing your own replication code ends
> up being cheaper/better than what Microsoft provides in Access
> without code.[/color]

Having done both (i.e., rolling my own and using Jet replication), I
strongly dispute this assertion of Steve's.

The number of problems is astronomical, even when you have very
limited synchronization scenarios. It's even complex enough when you
have a master/slave relationship between two or more dbs, where data
is updated/added/deleted only in the master (think about the
deletion problem and how you propagate the deletion of a record that
no longer exists at the time of the synchronization ).

The problem could be much more easily solved with Terminal Server,
or with a browser-based application, but either of those requires
constant Internet access.

I could engineer an all-Jet replication scenario using indirect
replication over dialup networking or over a VPN over the Internet
(i.e., not using Internet replication, which has a host of basic
requirements that makes it extremely prone to fall over), but I
wouldn't want to have to do it.

I don't have any clients who need to update data and synch with the
mother ship while still in the field, so I no longer do that kind of
thing (it used to be one of my specializations ). Nowadays I'm just
supporting travellers who need to take data with them and update it
while on the road, but don't need to re-synchronize with the mother
ship until back in the office. That's *very* easy to do, and can be
done safely with simple direct replication (and some of my clients
do it themselves, via the Access UI, to save the money on
programming that it would cost them).

But it's the requirement for getting and sending updates while in
the field that makes this very hard. If a client required it, I'd
definitely make a VPN a prerequisite to building it, as the
alternative (Internet replication) requires that the client run IIS
and an open FTP server. That's just no longer safe these days, and
it was never ever very stable.

My bet is that given the cost of engineering this with SQL Server
(which lacks some of the flexibility of Jet replication), that the
requirement for synch from the field could be easily dropped. If
that's the case, then the Terminal Server solution starts to look
attractive, as you've saved money you can now throw at the Internet
access costs, instead, while you'll be saving an enormous amount of
money on administrative costs in the long run, perhaps enough to pay
for the in-the-field Internet access costs.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Tony Toews]

Re: Conversion to adp

"John" <John@nospam.in fovis.co.uk> wrote:
[color=blue]
>I am converting my access front-end/backend mdb app to adp. Are there any
>pitfalls I should be aware of?[/color]

I can understand why some apps would want their data to be stored in SQL Server. But
there's no good reason to spend the extra time in converting the FE MDB to an ADP.
Leave the SQL Server tables as linked tables.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at

http://www.granite.ab.ca/accsmstr.htm

[Steve Jorgensen]

Re: Conversion to adp

On Mon, 25 Apr 2005 03:57:55 GMT, "David W. Fenton"
<dXXXfenton@bwa y.net.invalid> wrote:
[color=blue]
>Steve Jorgensen <nospam@nospam. nospam> wrote in
>news:105o61t3c bo0qese9r3up25j 2bnv7tj7lj@4ax. com:
>[color=green]
>> Note that Access replicaiton may or may not be the best way to do
>> replication. Sometimes, it's better to implement a replication
>> scheme at the application level. One way to do this is to
>> implement a record ID generation system that includes a machine
>> identifier so there can't be collisions between IDs generated on
>> different machines, and add fields for a current and previous
>> record revision ID so you can tell if a record in the central
>> database is the same revision that a remote system made a change
>> to while off-line, and allow the change to post if so.[/color]
>
>This is an enormously difficult task, even if it is only one-way
>between a mere two copies of the data file.
>
>I've done it. It's complicated (think about how deletions are
>propagated; think about order of inserts and referential integrity).
>
>If you're trying to have multiple dbs in the field, all being
>updated, and you want those changes pushed up to the server, and you
>also want to make the changes made by people on the servers to be
>pulled down to the db in the field, it becomes a hugely complicated
>task, unless a few conditions are met:
>
>1. no records are edited in more than one location.
>
>2. each person in the field has their own dataset that they work on
>alone, and no other people actually edit that data (though they may
>view it and analyze it).
>
>But even then, you have to solve the PK issue. You have three
>choices:
>
>1. use a natural key, and run the risk of the same natural key being
>used in two different copies of the database.
>
>2. pre-allocated blocks of surrogate keys to each copy of the
>database.
>
>3. include a source db identifier in a compound PK in every table.
>
>But if everyone's working on the same datasets, it becomes nearly
>impossible to program from scratch.
>
>Keep in mind that, theoretically speaking, there is a form of
>heterogenous replication where the main mother ship is a SQL Server
>db and the laptops have Jet dbs that synchronize with the SQL
>Server. However, like pure SQL Server replication itself, the whole
>scenario is much more limited than the capabilities of pure Jet
>replication, and the rules much more strict.
>
>I don't think replication is the answer here.
>
>I think the problem needs to be completely re-thought from the
>ground up.[/color]

All the problems you've described are real, but I've found they can often be
managed by limiting the scope of the replication features to what's important
for the real-world requirements of the app.

Here are some ideas that should help in most applications I can think of:

1. Don't try to resolve replication conflicts. Just let the loser either
abort the replication or continue and lose the conflicting changes. The user
can then abort, copy down the important information, run the replication
again, and manually enter the changes as required.

2. Don't try to replicate every table, just the ones that will really need to
be updated in the field. If the user needs a new lookup value, they can make
a comment in the notes, and fix it later when they're back on the LAN.

3. Don't allow deletions in disconnected mode. Just allow a status change to
something like "Inactive" - your application may work this way anyway.

[Alan Webb]

Re: Conversion to adp

David,
Ok. With the project I worked on there was only a half-dozen replicas.
Even with that I spent a fair amount of administrative time chasing down
replication errors that caused synchronization to fail. We used the
replication setup available through the Access UI. Even with my time
working out synchronization kinks this was still faster than having a clerk
rekey data collected by temps set around the company to inventory equipment
that may or may not be in-scope for Y2K.

--
Alan Webb
knoNOgeek@SPAMh otmail.com
"It's not IT, it's IS"

"David W. Fenton" <dXXXfenton@bwa y.net.invalid> wrote in message
news:Xns9643173 935BDdfentonbwa ynetinvali@24.1 68.128.90...[color=blue]
> "Alan Webb" <knoNOgeek@hotS PAMmail.com> wrote in
> news:moWdneuwfK NRr_HfRVn-iA@comcast.com:
>[color=green]
>> As Steve points
>> out, there are cases where writing your own replication code ends
>> up being cheaper/better than what Microsoft provides in Access
>> without code.[/color]
>
> Having done both (i.e., rolling my own and using Jet replication), I
> strongly dispute this assertion of Steve's.
>
> The number of problems is astronomical, even when you have very
> limited synchronization scenarios. It's even complex enough when you
> have a master/slave relationship between two or more dbs, where data
> is updated/added/deleted only in the master (think about the
> deletion problem and how you propagate the deletion of a record that
> no longer exists at the time of the synchronization ).
>
> The problem could be much more easily solved with Terminal Server,
> or with a browser-based application, but either of those requires
> constant Internet access.
>
> I could engineer an all-Jet replication scenario using indirect
> replication over dialup networking or over a VPN over the Internet
> (i.e., not using Internet replication, which has a host of basic
> requirements that makes it extremely prone to fall over), but I
> wouldn't want to have to do it.
>
> I don't have any clients who need to update data and synch with the
> mother ship while still in the field, so I no longer do that kind of
> thing (it used to be one of my specializations ). Nowadays I'm just
> supporting travellers who need to take data with them and update it
> while on the road, but don't need to re-synchronize with the mother
> ship until back in the office. That's *very* easy to do, and can be
> done safely with simple direct replication (and some of my clients
> do it themselves, via the Access UI, to save the money on
> programming that it would cost them).
>
> But it's the requirement for getting and sending updates while in
> the field that makes this very hard. If a client required it, I'd
> definitely make a VPN a prerequisite to building it, as the
> alternative (Internet replication) requires that the client run IIS
> and an open FTP server. That's just no longer safe these days, and
> it was never ever very stable.
>
> My bet is that given the cost of engineering this with SQL Server
> (which lacks some of the flexibility of Jet replication), that the
> requirement for synch from the field could be easily dropped. If
> that's the case, then the Terminal Server solution starts to look
> attractive, as you've saved money you can now throw at the Internet
> access costs, instead, while you'll be saving an enormous amount of
> money on administrative costs in the long run, perhaps enough to pay
> for the in-the-field Internet access costs.
>
> --
> David W. Fenton http://www.bway.net/~dfenton
> dfenton at bway dot net http://www.bway.net/~dfassoc[/color]

[David W. Fenton]

Re: Conversion to adp

Steve Jorgensen <nospam@nospam. nospam> wrote in
news:f95p619ijh ef9ed519jf9kf1v 7p80a62hb@4ax.c om:
[color=blue]
> On Mon, 25 Apr 2005 03:57:55 GMT, "David W. Fenton"
><dXXXfenton@bw ay.net.invalid> wrote:
>[color=green]
>>Steve Jorgensen <nospam@nospam. nospam> wrote in
>>news:105o61t3 cbo0qese9r3up25 j2bnv7tj7lj@4ax .com:
>>[color=darkred]
>>> Note that Access replicaiton may or may not be the best way to
>>> do replication. Sometimes, it's better to implement a
>>> replication scheme at the application level. One way to do this
>>> is to implement a record ID generation system that includes a
>>> machine identifier so there can't be collisions between IDs
>>> generated on different machines, and add fields for a current
>>> and previous record revision ID so you can tell if a record in
>>> the central database is the same revision that a remote system
>>> made a change to while off-line, and allow the change to post if
>>> so.[/color]
>>
>>This is an enormously difficult task, even if it is only one-way
>>between a mere two copies of the data file.
>>
>>I've done it. It's complicated (think about how deletions are
>>propagated; think about order of inserts and referential
>>integrity).
>>
>>If you're trying to have multiple dbs in the field, all being
>>updated, and you want those changes pushed up to the server, and
>>you also want to make the changes made by people on the servers to
>>be pulled down to the db in the field, it becomes a hugely
>>complicated task, unless a few conditions are met:
>>
>>1. no records are edited in more than one location.
>>
>>2. each person in the field has their own dataset that they work
>>on alone, and no other people actually edit that data (though they
>>may view it and analyze it).
>>
>>But even then, you have to solve the PK issue. You have three
>>choices:
>>
>>1. use a natural key, and run the risk of the same natural key
>>being used in two different copies of the database.
>>
>>2. pre-allocated blocks of surrogate keys to each copy of the
>>database.
>>
>>3. include a source db identifier in a compound PK in every table.
>>
>>But if everyone's working on the same datasets, it becomes nearly
>>impossible to program from scratch.
>>
>>Keep in mind that, theoretically speaking, there is a form of
>>heterogenou s replication where the main mother ship is a SQL
>>Server db and the laptops have Jet dbs that synchronize with the
>>SQL Server. However, like pure SQL Server replication itself, the
>>whole scenario is much more limited than the capabilities of pure
>>Jet replication, and the rules much more strict.
>>
>>I don't think replication is the answer here.
>>
>>I think the problem needs to be completely re-thought from the
>>ground up.[/color]
>
> All the problems you've described are real, but I've found they
> can often be managed by limiting the scope of the replication
> features to what's important for the real-world requirements of
> the app.
>
> Here are some ideas that should help in most applications I can
> think of:
>
> 1. Don't try to resolve replication conflicts. Just let the
> loser either abort the replication or continue and lose the
> conflicting changes. The user can then abort, copy down the
> important information, run the replication again, and manually
> enter the changes as required.[/color]

Steve, that's an absolutely ludicrous proposal. I'd call that data
corruption, because the end result is that you don't know if the
multiple copies of the data file have anything like the same data
set in them.
[color=blue]
> 2. Don't try to replicate every table, just the ones that will
> really need to be updated in the field. If the user needs a new
> lookup value, they can make a comment in the notes, and fix it
> later when they're back on the LAN.[/color]

I don't believe in manual processes for things that can be
automated.
[color=blue]
> 3. Don't allow deletions in disconnected mode. Just allow a
> status change to something like "Inactive" - your application may
> work this way anyway.[/color]

You still have to propagate that field value like other edits, and
the order of it can make a difference to any number of operations.

I am sorry to say that I don't think you have any credibility on
this issue based on the quality of your response. I would never
dream of accepting money for such a slap-dash system as you
describe.

And Jet replication offers much, much more than what you're
offering, without requiring massive amounts of coding (and testing
to see if it works).

I find your attitude towards data here quite inconsistent with your
detail-oriented approach to coding. Why are you running all these
tests on your code and wanting the compiler to catch as many
problems as possible, and then implementing systems like you
describe above, where the data isn't reliable?

Looks like a huge inconsistency to me, as though you are a code geek
who doesn't really care about data integrity.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc