Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Synchronising DB's: Best approach?

Discussion in 'Information Technology' started by vavroom@gmail.com, Jul 28, 2006.

  1. vavroom@gmail.com

    vavroom@gmail.com
    Expand Collapse
    Guest

    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
    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.
     
  2. vavroom@gmail.com

    vavroom@gmail.com
    Expand Collapse
    Guest

    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.
    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
     
  3. richard harris

    richard harris
    Expand Collapse
    Guest

    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

    "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
    > 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.
    >
    >
     
  4. vavroom@gmail.com

    vavroom@gmail.com
    Expand Collapse
    Guest


    > 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
    gladly share with you.
     
  5. richard harris

    richard harris
    Expand Collapse
    Guest

    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
    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
    > gladly share with you.
    >
    >
     
  6. Tony Toews

    Tony Toews
    Expand Collapse
    Guest

    richard harris <richardharris@discussions.microsoft.com> 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
    http://www.granite.ab.ca/accsmstr.htm
     
  7. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

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

    Pieter

    "Tony Toews" <ttoews@telusplanet.net> wrote in message
    news:ubbfc2tcghnvk6pkf4kh44t56c1im6qa7c@4ax.com...
    > richard harris <richardharris@discussions.microsoft.com> 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
    > http://www.granite.ab.ca/accsmstr.htm
     
  8. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

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

    Pieter

    "Tony Toews" <ttoews@telusplanet.net> wrote in message
    news:ubbfc2tcghnvk6pkf4kh44t56c1im6qa7c@4ax.com...
    > richard harris <richardharris@discussions.microsoft.com> 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
    > 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
     
  9. Tony Toews

    Tony Toews
    Expand Collapse
    Guest

    "Pieter Wijnen"
    <it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.replace.with.norway>
    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
    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
    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
     
  10. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    same for me <g>
    and yes, I've seen you around as long as I've been in the group - on and of
    depending on what I've been doing, I admit.

    Pieter

    "Tony Toews" <ttoews@telusplanet.net> wrote in message
    news:si7gc2lq0t5l56vikc7iu3a2l4c1olto6k@4ax.com...
    > "Pieter Wijnen"
    > <it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.replace.with.norway>
    > 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
    > 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
    > 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
     
  11. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    same for me <g>
    and yes, I've seen you around as long as I've been in the group - on and of
    depending on what I've been doing, I admit.

    Pieter

    "Tony Toews" <ttoews@telusplanet.net> wrote in message
    news:si7gc2lq0t5l56vikc7iu3a2l4c1olto6k@4ax.com...
    > "Pieter Wijnen"
    > <it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.replace.with.norway>
    > 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
    > 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
    > 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
     

Share This Page