Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Best Strategy for WAN?
23 views
Skip to first unread message
croy
Feb 7, 2013, 3:23:46 PM2/7/13
to
Using MS Access 2002, Windows 7. Back-end size of the main
mdb is about 65 MB.
We have two, geographically separated field offices, and we
need to get thier data into "our" database.
Once a month we get data from the field offices, and have
been somewhat manually appending it to the main office mdb.
Once the field offices have transferred their data to the
main office, they don't need to store it. They have been
starting with a new, empty copy of the back-end each month.
I'm just starting to look at replication, and getting a
little overwhelmed.
I've read in some articles that replication over a WAN is a
really bad idea, but most of these articles are a few years
old or older yet. Is this still a really bad idea?
Would it be ok to give each of the field offices a replica,
and then have them eMail it once a month to our main office,
and then do the replication?
Is there a way to do one-way replication? In other words,
have the replication master get everything from the field
offices, but the field offices not get main mdb data?
--
THanks,
croy
mdb is about 65 MB.
We have two, geographically separated field offices, and we
need to get thier data into "our" database.
Once a month we get data from the field offices, and have
been somewhat manually appending it to the main office mdb.
Once the field offices have transferred their data to the
main office, they don't need to store it. They have been
starting with a new, empty copy of the back-end each month.
I'm just starting to look at replication, and getting a
little overwhelmed.
I've read in some articles that replication over a WAN is a
really bad idea, but most of these articles are a few years
old or older yet. Is this still a really bad idea?
Would it be ok to give each of the field offices a replica,
and then have them eMail it once a month to our main office,
and then do the replication?
Is there a way to do one-way replication? In other words,
have the replication master get everything from the field
offices, but the field offices not get main mdb data?
--
THanks,
croy
Tony Toews
Feb 12, 2013, 8:59:47 PM2/12/13
to
On Thu, 07 Feb 2013 12:23:46 -0800, croy <ha...@spam.invalid.net> w
>We have two, geographically separated field offices, and we
>need to get thier data into "our" database.
>
>Once a month we get data from the field offices, and have
>been somewhat manually appending it to the main office mdb.
I'd be really tempted to continue with that approach but automating it
so mistakes are harder to make when you manually append it.
Also double check that data hasn't been somehow duplicated so it
doesn't already exist in your system whatever that data might be.
For example if they were entering invoices with invoice numbers then
ensure those invoice numbers don't already exist in your master
database.
The idea being that you'd have a form where you select the folder and
location of a field office database. Then your code looks at the
various tables in that field office database and, by using the IN
clause in queries, ensure duplicate data doesn't exist in the master
database.
Personally I'd also add code that shows how many invoice records and
invoice detail records were going to be appended just so folks get
some warm and fuzzy feelings that things seem about right.
Then do the appending of the records from the field office database.
Hmm, I'd also add a table in the field office database that had a a
few fields in it and contained the userid, workstationid and date/time
when the data was imported. And check that table as well.
See http://access.mvps.org/access/api/api0008.htm and
http://access.mvps.org/access/api/api0009.htm for network userid and
workstation id.
>I'm just starting to look at replication, and getting a
>little overwhelmed.
Agreed.
>I've read in some articles that replication over a WAN is a
>really bad idea, but most of these articles are a few years
>old or older yet. Is this still a really bad idea?
Very likely. Replication hasn't changed much especially in A2007 and
newer.
>Would it be ok to give each of the field offices a replica,
>and then have them eMail it once a month to our main office,
>and then do the replication?
I've read that emailing replication is frowned upon but I don't know
why because I've never read up on it.
>Is there a way to do one-way replication? In other words,
>have the replication master get everything from the field
>offices, but the field offices not get main mdb data?
I think so but I don't know for sure.
Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
>We have two, geographically separated field offices, and we
>need to get thier data into "our" database.
>
>Once a month we get data from the field offices, and have
>been somewhat manually appending it to the main office mdb.
so mistakes are harder to make when you manually append it.
Also double check that data hasn't been somehow duplicated so it
doesn't already exist in your system whatever that data might be.
For example if they were entering invoices with invoice numbers then
ensure those invoice numbers don't already exist in your master
database.
The idea being that you'd have a form where you select the folder and
location of a field office database. Then your code looks at the
various tables in that field office database and, by using the IN
clause in queries, ensure duplicate data doesn't exist in the master
database.
Personally I'd also add code that shows how many invoice records and
invoice detail records were going to be appended just so folks get
some warm and fuzzy feelings that things seem about right.
Then do the appending of the records from the field office database.
Hmm, I'd also add a table in the field office database that had a a
few fields in it and contained the userid, workstationid and date/time
when the data was imported. And check that table as well.
See http://access.mvps.org/access/api/api0008.htm and
http://access.mvps.org/access/api/api0009.htm for network userid and
workstation id.
>I'm just starting to look at replication, and getting a
>little overwhelmed.
>I've read in some articles that replication over a WAN is a
>really bad idea, but most of these articles are a few years
>old or older yet. Is this still a really bad idea?
newer.
>Would it be ok to give each of the field offices a replica,
>and then have them eMail it once a month to our main office,
>and then do the replication?
why because I've never read up on it.
>Is there a way to do one-way replication? In other words,
>have the replication master get everything from the field
>offices, but the field offices not get main mdb data?
Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
croy
Feb 14, 2013, 12:19:52 PM2/14/13
to
On Tue, 12 Feb 2013 18:59:47 -0700, Tony Toews
<tto...@telusplanet.net> wrote:
>On Thu, 07 Feb 2013 12:23:46 -0800, croy <ha...@spam.invalid.net> w
>
>>We have two, geographically separated field offices, and we
>>need to get thier data into "our" database.
>>
>>Once a month we get data from the field offices, and have
>>been somewhat manually appending it to the main office mdb.
>
>I'd be really tempted to continue with that approach but automating it
>so mistakes are harder to make when you manually append it.
>
>Also double check that data hasn't been somehow duplicated so it
>doesn't already exist in your system whatever that data might be.
>For example if they were entering invoices with invoice numbers then
>ensure those invoice numbers don't already exist in your master
>database.
>
>The idea being that you'd have a form where you select the folder and
>location of a field office database. Then your code looks at the
>various tables in that field office database and, by using the IN
>clause in queries, ensure duplicate data doesn't exist in the master
>database.
Yes, that's just about what I've got.
<tto...@telusplanet.net> wrote:
>On Thu, 07 Feb 2013 12:23:46 -0800, croy <ha...@spam.invalid.net> w
>
>>We have two, geographically separated field offices, and we
>>need to get thier data into "our" database.
>>
>>Once a month we get data from the field offices, and have
>>been somewhat manually appending it to the main office mdb.
>
>I'd be really tempted to continue with that approach but automating it
>so mistakes are harder to make when you manually append it.
>
>Also double check that data hasn't been somehow duplicated so it
>doesn't already exist in your system whatever that data might be.
>For example if they were entering invoices with invoice numbers then
>ensure those invoice numbers don't already exist in your master
>database.
>
>The idea being that you'd have a form where you select the folder and
>location of a field office database. Then your code looks at the
>various tables in that field office database and, by using the IN
>clause in queries, ensure duplicate data doesn't exist in the master
>database.
>Personally I'd also add code that shows how many invoice records and
>invoice detail records were going to be appended just so folks get
>some warm and fuzzy feelings that things seem about right.
>Then do the appending of the records from the field office database.
>Hmm, I'd also add a table in the field office database that had a a
>few fields in it and contained the userid, workstationid and date/time
>when the data was imported. And check that table as well.
themselves, but I'm going to ponder your way.
>See http://access.mvps.org/access/api/api0008.htm and
>http://access.mvps.org/access/api/api0009.htm for network userid and
>workstation id.
While writing a reply to Bob Alston, over in
comp.databases.ms-access, I had one of those, "why didn't I
think of this before" moments.
If I simply change the Id field for one table, from a
sequential autonumber, to a random autonumber, I think life
will be much easier.
Thanks for your comments. Good stuff!
--
croy
Tony Toews
Feb 14, 2013, 9:01:44 PM2/14/13
to
On Thu, 14 Feb 2013 09:19:52 -0800, croy <ha...@spam.invalid.net>
wrote:
>While writing a reply to Bob Alston, over in
>comp.databases.ms-access, I had one of those, "why didn't I
>think of this before" moments.
>
>If I simply change the Id field for one table, from a
>sequential autonumber, to a random autonumber, I think life
>will be much easier.
Ahh, so that one table is where the bulk/all of your appends are
coming from?
>Thanks for your comments. Good stuff!
Because we agree? <smile>
wrote:
>While writing a reply to Bob Alston, over in
>comp.databases.ms-access, I had one of those, "why didn't I
>think of this before" moments.
>
>If I simply change the Id field for one table, from a
>sequential autonumber, to a random autonumber, I think life
>will be much easier.
coming from?
>Thanks for your comments. Good stuff!
croy
Feb 15, 2013, 10:23:54 AM2/15/13
to
On Thu, 14 Feb 2013 19:01:44 -0700, Tony Toews
<tto...@telusplanet.net> wrote:
>On Thu, 14 Feb 2013 09:19:52 -0800, croy <ha...@spam.invalid.net>
>wrote:
>
>>While writing a reply to Bob Alston, over in
>>comp.databases.ms-access, I had one of those, "why didn't I
>>think of this before" moments.
>>
>>If I simply change the Id field for one table, from a
>>sequential autonumber, to a random autonumber, I think life
>>will be much easier.
>
>Ahh, so that one table is where the bulk/all of your appends are
>coming from?
Actually, that table is where the least of the appends come
<tto...@telusplanet.net> wrote:
>On Thu, 14 Feb 2013 09:19:52 -0800, croy <ha...@spam.invalid.net>
>wrote:
>
>>While writing a reply to Bob Alston, over in
>>comp.databases.ms-access, I had one of those, "why didn't I
>>think of this before" moments.
>>
>>If I simply change the Id field for one table, from a
>>sequential autonumber, to a random autonumber, I think life
>>will be much easier.
>
>Ahh, so that one table is where the bulk/all of your appends are
>coming from?
from--a lot of months there are none. But when they happen,
they give me headaches because there's nothing in the data
that is assured of creating a unique record--I have to rely
solely on the Id and record date. And every so often,
there's a collision. Less than a hundred records,
currently. It's not really a problem for me, but when I'm
gone, the boss has to do it, and if any records don't go, he
starts doing bizarre things, and I come back to a mess. I'm
trying to smooth things out for when I'm gone for good.
When I get back to work on Monday, I'm going to make that Id
a *random* autonumber!
Thanks for your thoughts.
--
croy
0 new messages