Synchronising DB's: Best approach?

Hello,
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/12915-synchronising-dbs-best-approach.html
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12915

I am at the design stage of designing an application. There is an MS
DB in place currently, but it's old, and certainly not optimised, and
as we're expanding to other sites as well, I was asked to create
something solid.

The data in a couple tables needs to be synchronised between sites.
Currently, only two sites are using the DB, but up to 15 locations will
be joining.

I've looked at replication, which seems like a good solution, but with
many pitfalls. One of the problems is that there will NOT be a way to
connect over a LAN (the PTB in their "infinite wisdom" have decreed
that for confidentiality issues, they weren't going to grant anyone
access to the server where the "main" db resides...).

So, can replication by email be done? Seems like it would be a bad
idea, fraught with risks of corruption?

Currently, they export data to CSV, then run an append query from the
CSV. This is kinda ok for someone who has done it, but it's cumbersome
(may be done as often as daily!). I'd like to automate the process and
make it easier, so it's foolproof (well, I know, the moment I do
something foolproof, they'll come up with a better fool, but...)

Any ideas would be appreciated.

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

Well, trying to understand the requirements and specs better, i was
provided with more information that gives me the direction I'll want to
take.

Turns out that only one location will have data from all sites.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12915
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12915
individual sites want to see only the data relevant to them. Which
means some fancy query work. I have an idea of how I'll do this,
wel'll see.

Cheers

Nic

hi nic,

i saw you r post and i have the same issue. i wondered if you had found a
solution. your help would be greatly appreciated. thanks richard
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12915

"vavroom@gmail.com" wrote:

> Hello,
>
> I am at the design stage of designing an application. There is an MS
> DB in place currently, but it's old, and certainly not optimised, and
> as we're expanding to other sites as well, I was asked to create
> something solid.
>
> The data in a couple tables needs to be synchronised between sites.
> Currently, only two sites are using the DB, but up to 15 locations will
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12915
> be joining.
>
> I've looked at replication, which seems like a good solution, but with
> many pitfalls. One of the problems is that there will NOT be a way to
> connect over a LAN (the PTB in their "infinite wisdom" have decreed
> that for confidentiality issues, they weren't going to grant anyone
> access to the server where the "main" db resides...).
>
> So, can replication by email be done? Seems like it would be a bad
> idea, fraught with risks of corruption?
>
> Currently, they export data to CSV, then run an append query from the
> CSV. This is kinda ok for someone who has done it, but it's cumbersome
> (may be done as often as daily!). I'd like to automate the process and
> make it easier, so it's foolproof (well, I know, the moment I do
> something foolproof, they'll come up with a better fool, but...)
>
> Any ideas would be appreciated.
>
>

> i saw you r post and i have the same issue. i wondered if you had found a
> solution. your help would be greatly appreciated.

Hello Richard,

In discussing the issues at play with the people who will be using the
database, it became clear that full replication was not only
unecessary, but undesirable.

Each of the locations want to see only the records that are specific to
their own location, and do not want the other locations to see "their"
records (confidentiality issues).

So, one location is the "warehouse", keeps all the data in, and will
be sending new record/receive updated records from each location.

I'm doing a variation on the theme of exporting a CSV by email, with a
lot of under the hood coding. The data I have lends itself well to
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12915
this.

The users only have to open a form, put in a date or date range for the
records they need to send back to the central location. They click a
command button, which runs an export of the data into a csv, attaches
it automatically to an email, and sends it "home".

Upon receipt of the email, we save the file wherever we want it. Open
a form, browse for the file in question, click a command button, and
the data gets inserted/updated into the appropriate tables.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12915

My coding is probably not all that elegant, and relied on a LOT of good
advice from folks around here (thanks guys and gals), but it works

If this sounds like something you might want to do, let me know, I'll
gladly share with you.

hi nic,

that sound great, i would like to have a look at it, maybe i can share some
other functions i have built that would enhance your system.

i did come accross a really cool bit of software which will automatically
synchronise two databases. it is a very simple peice of kit and can set up a
profile in 5 steps of a wizard. the cost is about $99. i tested it and it
works great. if your interested let me know and i will sedn you a url link
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12915
to the site. my e-mail is richard.harris@mortgage-direcitons.co.uk. thanks
richard

"vavroom@gmail.com" wrote:

>
> > i saw you r post and i have the same issue. i wondered if you had found a
> > solution. your help would be greatly appreciated.
>
> Hello Richard,
>
> In discussing the issues at play with the people who will be using the
> database, it became clear that full replication was not only
> unecessary, but undesirable.
>
> Each of the locations want to see only the records that are specific to
> their own location, and do not want the other locations to see "their"
> records (confidentiality issues).
>
> So, one location is the "warehouse", keeps all the data in, and will
> be sending new record/receive updated records from each location.
>
> I'm doing a variation on the theme of exporting a CSV by email, with a
> lot of under the hood coding. The data I have lends itself well to
> this.
>
> The users only have to open a form, put in a date or date range for the
> records they need to send back to the central location. They click a
> command button, which runs an export of the data into a csv, attaches
> it automatically to an email, and sends it "home".
>
> Upon receipt of the email, we save the file wherever we want it. Open
> a form, browse for the file in question, click a command button, and
> the data gets inserted/updated into the appropriate tables.
>
> My coding is probably not all that elegant, and relied on a LOT of good
> advice from folks around here (thanks guys and gals), but it works
>
> If this sounds like something you might want to do, let me know, I'll
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12915
> gladly share with you.
>
>

richard harris wrote:

>that sound great, i would like to have a look at it, maybe i can share some
>other functions i have built that would enhance your system.
>
>i did come accross a really cool bit of software which will automatically
>synchronise two databases. it is a very simple peice of kit and can set up a
> profile in 5 steps of a wizard. the cost is about $99. i tested it and it
>works great. if your interested let me know and i will sedn you a url link
>to the site. my e-mail is richard.harris@mortgage-direcitons.co.uk. thanks
>richard
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12915

Please post the URL to the newsgroup. I'm sure others would be
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12915
interested.

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

and you're asking for spam when you post your e-mail in plain text

Pieter

"Tony Toews" wrote in message
news:ubbfc2tcghnvk6pkf4kh44t56c1im6qa7c@4ax.com...
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12915
> richard harris wrote:
>
>>that sound great, i would like to have a look at it, maybe i can share
>>some
>>other functions i have built that would enhance your system.
>>
>>i did come accross a really cool bit of software which will automatically
>>synchronise two databases. it is a very simple peice of kit and can set
>>up a
>> profile in 5 steps of a wizard. the cost is about $99. i tested it and
>> it
>>works great. if your interested let me know and i will sedn you a url
>>link
>>to the site. my e-mail is richard.harris@mortgage-direcitons.co.uk.
>>thanks
>>richard
>
> Please post the URL to the newsgroup. I'm sure others would be
> interested.
>
> Thanks, 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12915
> http://www.granite.ab.ca/accsmstr.htm

and you're asking for spam when you post your e-mail in plain text

Pieter
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12915

"Tony Toews" wrote in message
news:ubbfc2tcghnvk6pkf4kh44t56c1im6qa7c@4ax.com...
> richard harris wrote:
>
>>that sound great, i would like to have a look at it, maybe i can share
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12915
>>some
>>other functions i have built that would enhance your system.
>>
>>i did come accross a really cool bit of software which will automatically
>>synchronise two databases. it is a very simple peice of kit and can set
>>up a
>> profile in 5 steps of a wizard. the cost is about $99. i tested it and
>> it
>>works great. if your interested let me know and i will sedn you a url
>>link
>>to the site. my e-mail is richard.harris@mortgage-direcitons.co.uk.
>>thanks
>>richard
>
> Please post the URL to the newsgroup. I'm sure others would be
> interested.
>
> Thanks, 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



--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4367 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len

"Pieter Wijnen"

wrote:

>and you're asking for spam when you post your e-mail in plain text

Oddly enough my ISP does a pretty good job of filtering that email
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12915
account. Last time I checked when the spam filtering was turned off
I was getting 500 to 600 spams a day. With the spam filtering turned
on I'm only getting 10 or 15 per day.

Besides I've been using the same email account now for about 26,000
postings for about ten years now and I have no interesting in changing
it.

TOny
--
Tony Toews, Microsoft Access MVP
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12915
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