Best Way to Synch 2 Tables?

All,

I have an Access DB. On a nightly basis, I want to look at an Other
DB (not Access, but SQL) and:

+ Add any new records from Other.Clients into Access.Clients

> Update 9 fields from Other.Clients into Access.Clients where changes have occured in Other.Clients.

Is this something I should use a "tool" (SQL Data Compare, SqlSync,
etc.) to do, or could I pull this off reliably every night just using
SQL from the query I could construct to do the job from inside my
Access DB?

And for question Deux, if I can just use the SQL I will write, what's
the best way to kick off the process at 3:00 am every morning?

Thanks,

Patrick

Re: Best Way to Synch 2 Tables?

On Thu, 08 Nov 2007 14:32:15 -0800, Patrick A
wrote:
>I have an Access DB. On a nightly basis, I want to look at an Other
>DB (not Access, but SQL) and:
>+ Add any new records from Other.Clients into Access.Clients
>> Update 9 fields from Other.Clients into Access.Clients where changes have occured in Other.Clients.
>Is this something I should use a "tool" (SQL Data Compare, SqlSync,
>etc.) to do, or could I pull this off reliably every night just using
>SQL from the query I could construct to do the job from inside my
>Access DB?
>And for question Deux, if I can just use the SQL I will write, what's
>the best way to kick off the process at 3:00 am every morning?

Yes, this can be done all in Access (via a macro that runs the
queries), although someone's PC will have to be logged in at 3:00am
for it to happen. Once those conditions are set up, use Scheduler to
run the macro at 3:00am. See Access Help for the command line on how
to run a macro when the database is opened.

You could also do this all in SQL with a scheduled job, but the server
will need the rights to the person's PC the MDB is on.

Which is better? without knowing more about your application and the
network, I really can't say.

Please Post Any Replies To This Message Back To the Newsgroup.
There are "Lurkers" around who can benefit by our exchange!

Re: Best Way to Synch 2 Tables?

Thanks, Chuck.

When you say "use Scheduler", you mean the Windows Task Scheduler,
a.k.a. Control Panel / Add Scheduled Task?

Patrick

On Nov 10, 4:26 pm, Chuck Grimsby
wrote:
> On Thu, 08 Nov 2007 14:32:15 -0800, Patrick A
> wrote:
>
> >I have an Access DB. On a nightly basis, I want to look at an Other
> >DB (not Access, but SQL) and:
> >+ Add any new records from Other.Clients into Access.Clients
> >> Update 9 fields from Other.Clients into Access.Clients where changes have occured in Other.Clients.
> >Is this something I should use a "tool" (SQL Data Compare, SqlSync,
> >etc.) to do, or could I pull this off reliably every night just using
> >SQL from the query I could construct to do the job from inside my
> >Access DB?
> >And for question Deux, if I can just use the SQL I will write, what's
> >the best way to kick off the process at 3:00 am every morning?
>
> Yes, this can be done all in Access (via a macro that runs the
> queries), although someone's PC will have to be logged in at 3:00am
> for it to happen. Once those conditions are set up, use Scheduler to
> run the macro at 3:00am. See Access Help for the command line on how
> to run a macro when the database is opened.
>
> You could also do this all in SQL with a scheduled job, but the server
> will need the rights to the person's PC the MDB is on.
>
> Which is better? without knowing more about your application and the
> network, I really can't say.
>
> Please Post Any Replies To This Message Back To the Newsgroup.
> There are "Lurkers" around who can benefit by our exchange!

Re: Best Way to Synch 2 Tables?

>although someone's PC will have to be logged in at 3:00am
> for it to happen. Once those conditions are set up, use Scheduler to
> run the macro at 3:00am.

The computer only needs to be on, not logged in for Windows Task Scheduler
to run the code. You will need to supply Scheduler a name and password.

Re: Best Way to Synch 2 Tables?

Yes, Correct. The Windows Task Scheduler.

As Ron mentioned, the computer doesn't really need to be logged in for
this happen, but the scheduler will need a *valid* login (and
password!) to run. This can be a problem on some networks that
enforce a regular password change, as the user will have to remember
to change the password in the scheduler as well whenever they change
their password.
A better solution, if you can get the Network Administrator to agree,
is to set up a user and password that never changes to run this task.
Remember however, that the "fixed" user will need access to the
database and the SQL database.

On Mon, 12 Nov 2007 10:57:28 -0800, Patrick A
wrote:

>Thanks, Chuck.
>
>When you say "use Scheduler", you mean the Windows Task Scheduler,
>a.k.a. Control Panel / Add Scheduled Task?
>
>Patrick
>
>On Nov 10, 4:26 pm, Chuck Grimsby
>wrote:
>> On Thu, 08 Nov 2007 14:32:15 -0800, Patrick A
>> wrote:
>>
>> >I have an Access DB. On a nightly basis, I want to look at an Other
>> >DB (not Access, but SQL) and:
>> >+ Add any new records from Other.Clients into Access.Clients
>> >> Update 9 fields from Other.Clients into Access.Clients where changes have occured in Other.Clients.
>> >Is this something I should use a "tool" (SQL Data Compare, SqlSync,
>> >etc.) to do, or could I pull this off reliably every night just using
>> >SQL from the query I could construct to do the job from inside my
>> >Access DB?
>> >And for question Deux, if I can just use the SQL I will write, what's
>> >the best way to kick off the process at 3:00 am every morning?
>>
>> Yes, this can be done all in Access (via a macro that runs the
>> queries), although someone's PC will have to be logged in at 3:00am
>> for it to happen. Once those conditions are set up, use Scheduler to
>> run the macro at 3:00am. See Access Help for the command line on how
>> to run a macro when the database is opened.
>>
>> You could also do this all in SQL with a scheduled job, but the server
>> will need the rights to the person's PC the MDB is on.
>>
>> Which is better? without knowing more about your application and the
>> network, I really can't say.
>>
>> Please Post Any Replies To This Message Back To the Newsgroup.
>> There are "Lurkers" around who can benefit by our exchange!
>

Please Post Any Replies To This Message Back To the Newsgroup.
There are "Lurkers" around who can benefit by our exchange!

Re: Best Way to Synch 2 Tables?

Thanks for the tips. I think I can get the network admin to agree, he
doesn't want to get up at 3:00 am either.

Can I check out my macro/query logic with you?

With 2 db's, A and B:

I want to look up existing records in B and Update them from A
I want find records in A that meet my criteria and don't exist in B
and Append them to B
I don't want to delete any records from B or A.

So, is it "better" for me to write one macro that calls the Update
query and the Append query, (really, I think, a combination of "find
unmatched" and append queries), or two macros, (one calling the next),
or does it matter?

Thanks,

Patrick

On Nov 12, 6:11 pm, Chuck Grimsby
wrote:
> Yes, Correct. The Windows Task Scheduler.
>
> As Ron mentioned, the computer doesn't really need to be logged in for
> this happen, but the scheduler will need a *valid* login (and
> password!) to run. This can be a problem on some networks that
> enforce a regular password change, as the user will have to remember
> to change the password in the scheduler as well whenever they change
> their password.
> A better solution, if you can get the Network Administrator to agree,
> is to set up a user and password that never changes to run this task.
> Remember however, that the "fixed" user will need access to the
> database and the SQL database.
>
> On Mon, 12 Nov 2007 10:57:28 -0800, Patrick A
> wrote:
>
>
>
>
>
> >Thanks, Chuck.
>
> >When you say "use Scheduler", you mean the Windows Task Scheduler,
> >a.k.a. Control Panel / Add Scheduled Task?
>
> >Patrick
>
> >On Nov 10, 4:26 pm, Chuck Grimsby
> >wrote:
> >> On Thu, 08 Nov 2007 14:32:15 -0800, Patrick A
> >> wrote:
>
> >> >I have an Access DB. On a nightly basis, I want to look at an Other
> >> >DB (not Access, but SQL) and:
> >> >+ Add any new records from Other.Clients into Access.Clients
> >> >> Update 9 fields from Other.Clients into Access.Clients where changes have occured in Other.Clients.
> >> >Is this something I should use a "tool" (SQL Data Compare, SqlSync,
> >> >etc.) to do, or could I pull this off reliably every night just using
> >> >SQL from the query I could construct to do the job from inside my
> >> >Access DB?
> >> >And for question Deux, if I can just use the SQL I will write, what's
> >> >the best way to kick off the process at 3:00 am every morning?
>
> >> Yes, this can be done all in Access (via a macro that runs the
> >> queries), although someone's PC will have to be logged in at 3:00am
> >> for it to happen. Once those conditions are set up, use Scheduler to
> >> run the macro at 3:00am. See Access Help for the command line on how
> >> to run a macro when the database is opened.
>
> >> You could also do this all in SQL with a scheduled job, but the server
> >> will need the rights to the person's PC the MDB is on.
>
> >> Which is better? without knowing more about your application and the
> >> network, I really can't say.
>
> >> Please Post Any Replies To This Message Back To the Newsgroup.
> >> There are "Lurkers" around who can benefit by our exchange!
>
> Please Post Any Replies To This Message Back To the Newsgroup.
> There are "Lurkers" around who can benefit by our exchange!- Hide quoted text -
>
> - Show quoted text -

Re: Best Way to Synch 2 Tables?

Assuming you will be appending only a limited number of records, I would
lean towards doing the append 1st so that the update selection becomes
simple. But more important, consider using VBA and running the 2 queries
inside a Transaction. You will then know that both queries run and can send
a message on fail.

"Patrick A" wrote in message
news:1194963424.308970.88150@d55g2000hsg.googlegroups.com...
> Thanks for the tips. I think I can get the network admin to agree, he
> doesn't want to get up at 3:00 am either.
>
> Can I check out my macro/query logic with you?
>
> With 2 db's, A and B:
>
> I want to look up existing records in B and Update them from A
> I want find records in A that meet my criteria and don't exist in B
> and Append them to B
> I don't want to delete any records from B or A.
>
> So, is it "better" for me to write one macro that calls the Update
> query and the Append query, (really, I think, a combination of "find
> unmatched" and append queries), or two macros, (one calling the next),
> or does it matter?
>
> Thanks,
>
> Patrick
>
> On Nov 12, 6:11 pm, Chuck Grimsby
> wrote:
> > Yes, Correct. The Windows Task Scheduler.
> >
> > As Ron mentioned, the computer doesn't really need to be logged in for
> > this happen, but the scheduler will need a *valid* login (and
> > password!) to run. This can be a problem on some networks that
> > enforce a regular password change, as the user will have to remember
> > to change the password in the scheduler as well whenever they change
> > their password.
> > A better solution, if you can get the Network Administrator to agree,
> > is to set up a user and password that never changes to run this task.
> > Remember however, that the "fixed" user will need access to the
> > database and the SQL database.
> >
> > On Mon, 12 Nov 2007 10:57:28 -0800, Patrick A
> > wrote:
> >
> >
> >
> >
> >
> > >Thanks, Chuck.
> >
> > >When you say "use Scheduler", you mean the Windows Task Scheduler,
> > >a.k.a. Control Panel / Add Scheduled Task?
> >
> > >Patrick
> >
> > >On Nov 10, 4:26 pm, Chuck Grimsby
> > >wrote:
> > >> On Thu, 08 Nov 2007 14:32:15 -0800, Patrick A
> > >> wrote:
> >
> > >> >I have an Access DB. On a nightly basis, I want to look at an Other
> > >> >DB (not Access, but SQL) and:
> > >> >+ Add any new records from Other.Clients into Access.Clients
> > >> >> Update 9 fields from Other.Clients into Access.Clients where
changes have occured in Other.Clients.
> > >> >Is this something I should use a "tool" (SQL Data Compare, SqlSync,
> > >> >etc.) to do, or could I pull this off reliably every night just
using
> > >> >SQL from the query I could construct to do the job from inside my
> > >> >Access DB?
> > >> >And for question Deux, if I can just use the SQL I will write,
what's
> > >> >the best way to kick off the process at 3:00 am every morning?
> >
> > >> Yes, this can be done all in Access (via a macro that runs the
> > >> queries), although someone's PC will have to be logged in at 3:00am
> > >> for it to happen. Once those conditions are set up, use Scheduler to
> > >> run the macro at 3:00am. See Access Help for the command line on how
> > >> to run a macro when the database is opened.
> >
> > >> You could also do this all in SQL with a scheduled job, but the
server
> > >> will need the rights to the person's PC the MDB is on.
> >
> > >> Which is better? without knowing more about your application and the
> > >> network, I really can't say.
> >
> > >> Please Post Any Replies To This Message Back To the Newsgroup.
> > >> There are "Lurkers" around who can benefit by our exchange!
> >
> > Please Post Any Replies To This Message Back To the Newsgroup.
> > There are "Lurkers" around who can benefit by our exchange!- Hide quoted
text -
> >
> > - Show quoted text -
>
>

Re: Best Way to Synch 2 Tables?

Thanks,

Hmmm...

running the 2 queries inside an Access Transaction?

In the help it mentions that "Transactions are not supported for
linked tables."

I bet I forgot to mention that the "A" table will be linked...

So what if I
- Write the code (including the bit to email me if the SQL fails) into
the "OnOpen" event of a form
- Put the form in a db and have the form load when the db opens
- Schedule a 3:00 am task of "Start Acces and open mydatarobot.mdb"

Unless there's a simpler/smarter way...


On Nov 13, 9:53 am, "paii, Ron" wrote:
> Assuming you will be appending only a limited number of records, I would
> lean towards doing the append 1st so that the update selection becomes
> simple. But more important, consider using VBA and running the 2 queries
> inside a Transaction. You will then know that both queries run and can send
> a message on fail.
>
> "Patrick A" wrote in message
>
> news:1194963424.308970.88150@d55g2000hsg.googlegroups.com...
>
>
>
> > Thanks for the tips. I think I can get the network admin to agree, he
> > doesn't want to get up at 3:00 am either.
>
> > Can I check out my macro/query logic with you?
>
> > With 2 db's, A and B:
>
> > I want to look up existing records in B and Update them from A
> > I want find records in A that meet my criteria and don't exist in B
> > and Append them to B
> > I don't want to delete any records from B or A.
>
> > So, is it "better" for me to write one macro that calls the Update
> > query and the Append query, (really, I think, a combination of "find
> > unmatched" and append queries), or two macros, (one calling the next),
> > or does it matter?
>
> > Thanks,
>
> > Patrick
>
> > On Nov 12, 6:11 pm, Chuck Grimsby
> > wrote:
> > > Yes, Correct. The Windows Task Scheduler.
>
> > > As Ron mentioned, the computer doesn't really need to be logged in for
> > > this happen, but the scheduler will need a *valid* login (and
> > > password!) to run. This can be a problem on some networks that
> > > enforce a regular password change, as the user will have to remember
> > > to change the password in the scheduler as well whenever they change
> > > their password.
> > > A better solution, if you can get the Network Administrator to agree,
> > > is to set up a user and password that never changes to run this task.
> > > Remember however, that the "fixed" user will need access to the
> > > database and the SQL database.
>
> > > On Mon, 12 Nov 2007 10:57:28 -0800, Patrick A
> > > wrote:
>
> > > >Thanks, Chuck.
>
> > > >When you say "use Scheduler", you mean the Windows Task Scheduler,
> > > >a.k.a. Control Panel / Add Scheduled Task?
>
> > > >Patrick
>
> > > >On Nov 10, 4:26 pm, Chuck Grimsby
> > > >wrote:
> > > >> On Thu, 08 Nov 2007 14:32:15 -0800, Patrick A
> > > >> wrote:
>
> > > >> >I have an Access DB. On a nightly basis, I want to look at an Other
> > > >> >DB (not Access, but SQL) and:
> > > >> >+ Add any new records from Other.Clients into Access.Clients
> > > >> >> Update 9 fields from Other.Clients into Access.Clients where
>
> changes have occured in Other.Clients.
>
>
>
> > > >> >Is this something I should use a "tool" (SQL Data Compare, SqlSync,
> > > >> >etc.) to do, or could I pull this off reliably every night just
> using
> > > >> >SQL from the query I could construct to do the job from inside my
> > > >> >Access DB?
> > > >> >And for question Deux, if I can just use the SQL I will write,
> what's
> > > >> >the best way to kick off the process at 3:00 am every morning?
>
> > > >> Yes, this can be done all in Access (via a macro that runs the
> > > >> queries), although someone's PC will have to be logged in at 3:00am
> > > >> for it to happen. Once those conditions are set up, use Scheduler to
> > > >> run the macro at 3:00am. See Access Help for the command line on how
> > > >> to run a macro when the database is opened.
>
> > > >> You could also do this all in SQL with a scheduled job, but the
> server
> > > >> will need the rights to the person's PC the MDB is on.
>
> > > >> Which is better? without knowing more about your application and the
> > > >> network, I really can't say.
>
> > > >> Please Post Any Replies To This Message Back To the Newsgroup.
> > > >> There are "Lurkers" around who can benefit by our exchange!
>
> > > Please Post Any Replies To This Message Back To the Newsgroup.
> > > There are "Lurkers" around who can benefit by our exchange!- Hide quoted
> text -
>
> > > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

Re: Best Way to Synch 2 Tables?

Transactions will work on linked Jet "Access" tables if both tables are in
the same MDB file. If they are not, VBA will at least allow you to check and
report if one or both of the queries fail.

No need for a form, put the code in a module and use a macro to call the
function.

"Patrick A" wrote in message
news:1194980478.863478.204930@o3g2000hsb.googlegroups.com...
> Thanks,
>
> Hmmm...
>
> running the 2 queries inside an Access Transaction?
>
> In the help it mentions that "Transactions are not supported for
> linked tables."
>
> I bet I forgot to mention that the "A" table will be linked...
>
> So what if I
> - Write the code (including the bit to email me if the SQL fails) into
> the "OnOpen" event of a form
> - Put the form in a db and have the form load when the db opens
> - Schedule a 3:00 am task of "Start Acces and open mydatarobot.mdb"
>
> Unless there's a simpler/smarter way...
>
>
> On Nov 13, 9:53 am, "paii, Ron" wrote:
> > Assuming you will be appending only a limited number of records, I would
> > lean towards doing the append 1st so that the update selection becomes
> > simple. But more important, consider using VBA and running the 2 queries
> > inside a Transaction. You will then know that both queries run and can
send
> > a message on fail.
> >
> > "Patrick A" wrote in message
> >
> > news:1194963424.308970.88150@d55g2000hsg.googlegroups.com...
> >
> >
> >
> > > Thanks for the tips. I think I can get the network admin to agree, he
> > > doesn't want to get up at 3:00 am either.
> >
> > > Can I check out my macro/query logic with you?
> >
> > > With 2 db's, A and B:
> >
> > > I want to look up existing records in B and Update them from A
> > > I want find records in A that meet my criteria and don't exist in B
> > > and Append them to B
> > > I don't want to delete any records from B or A.
> >
> > > So, is it "better" for me to write one macro that calls the Update
> > > query and the Append query, (really, I think, a combination of "find
> > > unmatched" and append queries), or two macros, (one calling the next),
> > > or does it matter?
> >
> > > Thanks,
> >
> > > Patrick
> >
> > > On Nov 12, 6:11 pm, Chuck Grimsby
> > > wrote:
> > > > Yes, Correct. The Windows Task Scheduler.
> >
> > > > As Ron mentioned, the computer doesn't really need to be logged in
for
> > > > this happen, but the scheduler will need a *valid* login (and
> > > > password!) to run. This can be a problem on some networks that
> > > > enforce a regular password change, as the user will have to remember
> > > > to change the password in the scheduler as well whenever they change
> > > > their password.
> > > > A better solution, if you can get the Network Administrator to
agree,
> > > > is to set up a user and password that never changes to run this
task.
> > > > Remember however, that the "fixed" user will need access to the
> > > > database and the SQL database.
> >
> > > > On Mon, 12 Nov 2007 10:57:28 -0800, Patrick A
> > > > wrote:
> >
> > > > >Thanks, Chuck.
> >
> > > > >When you say "use Scheduler", you mean the Windows Task Scheduler,
> > > > >a.k.a. Control Panel / Add Scheduled Task?
> >
> > > > >Patrick
> >
> > > > >On Nov 10, 4:26 pm, Chuck Grimsby

> > > > >wrote:
> > > > >> On Thu, 08 Nov 2007 14:32:15 -0800, Patrick A

> > > > >> wrote:
> >
> > > > >> >I have an Access DB. On a nightly basis, I want to look at an
Other
> > > > >> >DB (not Access, but SQL) and:
> > > > >> >+ Add any new records from Other.Clients into Access.Clients
> > > > >> >> Update 9 fields from Other.Clients into Access.Clients where
> >
> > changes have occured in Other.Clients.
> >
> >
> >
> > > > >> >Is this something I should use a "tool" (SQL Data Compare,
SqlSync,
> > > > >> >etc.) to do, or could I pull this off reliably every night just
> > using
> > > > >> >SQL from the query I could construct to do the job from inside
my
> > > > >> >Access DB?
> > > > >> >And for question Deux, if I can just use the SQL I will write,
> > what's
> > > > >> >the best way to kick off the process at 3:00 am every morning?
> >
> > > > >> Yes, this can be done all in Access (via a macro that runs the
> > > > >> queries), although someone's PC will have to be logged in at
3:00am
> > > > >> for it to happen. Once those conditions are set up, use
Scheduler to
> > > > >> run the macro at 3:00am. See Access Help for the command line on
how
> > > > >> to run a macro when the database is opened.
> >
> > > > >> You could also do this all in SQL with a scheduled job, but the
> > server
> > > > >> will need the rights to the person's PC the MDB is on.
> >
> > > > >> Which is better? without knowing more about your application and
the
> > > > >> network, I really can't say.
> >
> > > > >> Please Post Any Replies To This Message Back To the Newsgroup.
> > > > >> There are "Lurkers" around who can benefit by our exchange!
> >
> > > > Please Post Any Replies To This Message Back To the Newsgroup.
> > > > There are "Lurkers" around who can benefit by our exchange!- Hide
quoted
> > text -
> >
> > > > - Show quoted text -- Hide quoted text -
> >
> > - Show quoted text -- Hide quoted text -
> >
> > - Show quoted text -
>
>

Re: Best Way to Synch 2 Tables?

Chuck Grimsby wrote in
news:fpmhj3l4ltaf5ffplu8hrt4i5nebmkdcoi@4ax.com:

> A better solution

would be Jet replication, seems to me.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Re: Best Way to Synch 2 Tables?

On Tue, 13 Nov 2007 15:51:11 -0600, "David W. Fenton"
wrote:

>Chuck Grimsby wrote in
>news:fpmhj3l4ltaf5ffplu8hrt4i5nebmkdcoi@4ax.com:
>
>> A better solution
>
>would be Jet replication, seems to me.

I've never trusted Jet replication. No specific reason, I just don't
trust it. I'd rather "roll my own". Personally, I have enough
experience doing it, so it's not a problem.

Your milage may vary, of course.

And as this is replication with a SQL Server DB, I'm not sure that it
would work anyways....

Please Post Any Replies To This Message Back To the Newsgroup.
There are "Lurkers" around who can benefit by our exchange!

Re: Best Way to Synch 2 Tables?

Chuck Grimsby wrote in
news:k5ckj390brfcuvti8r9bdb3jdelujm8llm@4ax.com:

> On Tue, 13 Nov 2007 15:51:11 -0600, "David W. Fenton"
> wrote:
>
>>Chuck Grimsby wrote in
>>news:fpmhj3l4ltaf5ffplu8hrt4i5nebmkdcoi@4ax.com:
>>
>>> A better solution
>>
>>would be Jet replication, seems to me.
>
> I've never trusted Jet replication. No specific reason, I just
> don't trust it. I'd rather "roll my own". Personally, I have
> enough experience doing it, so it's not a problem.

You've programmed your own merge replication? That's pretty
impressive. I've done master/slave manual replication and that's
hard enough (deletes are the hardest).

> Your milage may vary, of course.
>
> And as this is replication with a SQL Server DB, I'm not sure that
> it would work anyways....

Jet 4 can be a merge subscriber of SQL Server before 2005. They
removed that from 2005, because Microsoft hates Jet Replication and
wants to kill it entirely.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Re: Best Way to Synch 2 Tables?

On Wed, 14 Nov 2007 14:02:05 -0600, "David W. Fenton"
wrote:

>Chuck Grimsby wrote in
>news:k5ckj390brfcuvti8r9bdb3jdelujm8llm@4ax.com:
>
>> On Tue, 13 Nov 2007 15:51:11 -0600, "David W. Fenton"
>> wrote:
>>
>>>Chuck Grimsby wrote in
>>>news:fpmhj3l4ltaf5ffplu8hrt4i5nebmkdcoi@4ax.com:
>>>
>>>> A better solution
>>>
>>>would be Jet replication, seems to me.
>>
>> I've never trusted Jet replication. No specific reason, I just
>> don't trust it. I'd rather "roll my own". Personally, I have
>> enough experience doing it, so it's not a problem.
>
>You've programmed your own merge replication? That's pretty
>impressive. I've done master/slave manual replication and that's
>hard enough (deletes are the hardest).

Replication isn't all that hard, David. It's just a matter of marking
what changes when, and then updating both databases with the changes.

As for deletes... I don't allow deletes in my databases. Users can
*mark* records as being deleted, but they aren't allowed to actually
delete anything. Not only does it make sense as far as replication is
concerned, but it also resolves those issues where a user "deletes"
something accidentally.

Please Post Any Replies To This Message Back To the Newsgroup.
There are "Lurkers" around who can benefit by our exchange!

Re: Best Way to Synch 2 Tables?

Chuck Grimsby wrote in
news:1q0nj39jm16tpf0euhppaauidh9fdts3mi@4ax.com:

> On Wed, 14 Nov 2007 14:02:05 -0600, "David W. Fenton"
> wrote:
>
>>Chuck Grimsby wrote in
>>news:k5ckj390brfcuvti8r9bdb3jdelujm8llm@4ax.com:
>>
>>> On Tue, 13 Nov 2007 15:51:11 -0600, "David W. Fenton"
>>> wrote:
>>>
>>>>Chuck Grimsby wrote in
>>>>news:fpmhj3l4ltaf5ffplu8hrt4i5nebmkdcoi@4ax.com:
>>>>
>>>>> A better solution
>>>>
>>>>would be Jet replication, seems to me.
>>>
>>> I've never trusted Jet replication. No specific reason, I just
>>> don't trust it. I'd rather "roll my own". Personally, I have
>>> enough experience doing it, so it's not a problem.
>>
>>You've programmed your own merge replication? That's pretty
>>impressive. I've done master/slave manual replication and that's
>>hard enough (deletes are the hardest).
>
> Replication isn't all that hard, David. It's just a matter of
> marking what changes when, and then updating both databases with
> the changes.

It's *very* hard *if* both databases can update any data in any
record at any time.

> As for deletes... I don't allow deletes in my databases. Users
> can *mark* records as being deleted, but they aren't allowed to
> actually delete anything. Not only does it make sense as far as
> replication is concerned, but it also resolves those issues where
> a user "deletes" something accidentally.

That is, of course, the easiest way to handle it, but not all apps
use that approach.

I think you're *vastly* underestimating the level of difficulty of
N-way merge replication that preserves all edits in all instances of
your data file.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Re: Best Way to Synch 2 Tables?

On Fri, 16 Nov 2007 16:40:00 -0600, "David W. Fenton"
wrote:

>Chuck Grimsby wrote in
>news:1q0nj39jm16tpf0euhppaauidh9fdts3mi@4ax.com:
>
>> On Wed, 14 Nov 2007 14:02:05 -0600, "David W. Fenton"
>> wrote:
>>
>>>Chuck Grimsby wrote in
>>>news:k5ckj390brfcuvti8r9bdb3jdelujm8llm@4ax.com:
>>>
>>>> On Tue, 13 Nov 2007 15:51:11 -0600, "David W. Fenton"
>>>> wrote:
>>>>
>>>>>Chuck Grimsby wrote in
>>>>>news:fpmhj3l4ltaf5ffplu8hrt4i5nebmkdcoi@4ax.com:
>>>>>
>>>>>> A better solution
>>>>>
>>>>>would be Jet replication, seems to me.
>>>>
>>>> I've never trusted Jet replication. No specific reason, I just
>>>> don't trust it. I'd rather "roll my own". Personally, I have
>>>> enough experience doing it, so it's not a problem.
>>>
>>>You've programmed your own merge replication? That's pretty
>>>impressive. I've done master/slave manual replication and that's
>>>hard enough (deletes are the hardest).
>>
>> Replication isn't all that hard, David. It's just a matter of
>> marking what changes when, and then updating both databases with
>> the changes.
>
>It's *very* hard *if* both databases can update any data in any
>record at any time.
>
>> As for deletes... I don't allow deletes in my databases. Users
>> can *mark* records as being deleted, but they aren't allowed to
>> actually delete anything. Not only does it make sense as far as
>> replication is concerned, but it also resolves those issues where
>> a user "deletes" something accidentally.
>
>That is, of course, the easiest way to handle it, but not all apps
>use that approach.
>
>I think you're *vastly* underestimating the level of difficulty of
>N-way merge replication that preserves all edits in all instances of
>your data file.

And I think you're *vastly* overestimating the level of difficulty,
David, but this isn't really worth discussing anymore.

All I can say is that I haven't had too many problems so far. Mostly
just "collisions" which have to be solved "manually", just as in any
other form of replication.

As I've oft said before: "your milage may vary".

Happy coding!

Please Post Any Replies To This Message Back To the Newsgroup.
There are "Lurkers" around who can benefit by our exchange!

Re: Best Way to Synch 2 Tables?

Chuck Grimsby wrote in
news:45t1k3186oer7fdqfaa0206oq3gvks8ts0@4ax.com:

> On Fri, 16 Nov 2007 16:40:00 -0600, "David W. Fenton"
> wrote:
>
>>Chuck Grimsby wrote in
>>news:1q0nj39jm16tpf0euhppaauidh9fdts3mi@4ax.com:
>>
>>> On Wed, 14 Nov 2007 14:02:05 -0600, "David W. Fenton"
>>> wrote:
>>>
>>>>Chuck Grimsby wrote in
>>>>news:k5ckj390brfcuvti8r9bdb3jdelujm8llm@4ax.com:
>>>>
>>>>> On Tue, 13 Nov 2007 15:51:11 -0600, "David W. Fenton"
>>>>> wrote:
>>>>>
>>>>>>Chuck Grimsby wrote in
>>>>>>news:fpmhj3l4ltaf5ffplu8hrt4i5nebmkdcoi@4ax.com:
>>>>>>
>>>>>>> A better solution
>>>>>>
>>>>>>would be Jet replication, seems to me.
>>>>>
>>>>> I've never trusted Jet replication. No specific reason, I
>>>>> just don't trust it. I'd rather "roll my own". Personally, I
>>>>> have enough experience doing it, so it's not a problem.
>>>>
>>>>You've programmed your own merge replication? That's pretty
>>>>impressive. I've done master/slave manual replication and that's
>>>>hard enough (deletes are the hardest).
>>>
>>> Replication isn't all that hard, David. It's just a matter of
>>> marking what changes when, and then updating both databases with
>>> the changes.
>>
>>It's *very* hard *if* both databases can update any data in any
>>record at any time.
>>
>>> As for deletes... I don't allow deletes in my databases. Users
>>> can *mark* records as being deleted, but they aren't allowed to
>>> actually delete anything. Not only does it make sense as far as
>>> replication is concerned, but it also resolves those issues
>>> where a user "deletes" something accidentally.
>>
>>That is, of course, the easiest way to handle it, but not all apps
>>use that approach.
>>
>>I think you're *vastly* underestimating the level of difficulty of
>>N-way merge replication that preserves all edits in all instances
>>of your data file.
>
> And I think you're *vastly* overestimating the level of
> difficulty, David, but this isn't really worth discussing anymore.

I've programmed it and I've used Jet Replication extensively. The
latter is orders of magnitude easier to implement and keep running.

> All I can say is that I haven't had too many problems so far.
> Mostly just "collisions" which have to be solved "manually", just
> as in any other form of replication.

Except that in Jet Replication most conflicts *don't* need human
intervention. And, in fact, every conflict gets resolved one way or
another without user intervention. The conflict resolver is only a
way to allow you to check that Jet guessed right.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/