Synchronising DB's: Best approach?

V

vavroom

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.
 
V

vavroom

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
 
R

richard harris

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
 
V

vavroom

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.
Click to expand...

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.
 
R

richard harris

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 (e-mail address removed). thanks
richard
 
T

Tony Toews

richard harris said:
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 (e-mail address removed). thanks
richard
Click to expand...

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
 
P

Pieter Wijnen

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

Pieter

Tony Toews said:
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
Click to expand...



--
 
T

Tony Toews

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

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
 
A

aaron.kempf

if you used Access Data Projects and not obsolete MDB; then you
woudln't spend all your time syncing data

-Aaron
 
P

Pieter Wijnen

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
 
P

Pieter Wijnen

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 said:
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
Click to expand...



--
 
V

vavroom

if you used Access Data Projects and not obsolete MDB; then you
woudln't spend all your time syncing data
Click to expand...

You assume that it is possible to do so, when I explained in my
original post that one of the problems was there is no common server
available between locations.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Merge 2 Db's 3
Hitting the limits with large table in Excel containing 1 millionrecords. What is the best option fo 2
All DB's Crashing on One Computer 9
Full access DB on-line 8
Handling & comparing old vs new data 0
Creates db1, db2 on "compact on close" 13
MySQL / Access -- Expression web 0
Best approach? 1

Top