[AccessD] AccessD Digest, Vol 43, Issue 8

First, thanks for the clarification. That makes the problem much more interesting.

The data must move from your allegedly secure place to another allegedly secure place on an allegedly secure channel (however that might be achived, encryption being one possible answer).

Maybe you've got all that in place already, but maybe not. Once that is all settled, we're back to the simpler part of the problem. To replicate or not to replicate?

If the back-end database is MDB, then replication is a great way to go. I have worked extensively with Access replication, linking together several branch offices and dozens of employees. There was a database server in each office and the home office ran the replicator. Data was at most 5 minutes stale. 

If the database is SQL Server 2005, then you win a cool new thing called "mirrored" databases. You need a pair of servers, yours and theirs, able to communicate. After that, it's a cinch to set up. A wizard takes you through it. 

hth,
Arthur

----- Original Message ----
From: ewaldt at gdls.com
To: accessd at databaseadvisors.com
Sent: Saturday, September 9, 2006 4:26:18 PM
Subject: Re: [AccessD] AccessD Digest, Vol 43, Issue 8

The answer to nearly all of your questions is found in my (apparently less
than clear statement):

      "For purposes of security, it cannot be on a network drive shared by
the two companies."

I guess the problem is the word "security". Don't think of "database
security" or "network security"; note that I work for General Dynamics, and
think of "military security" or even "national security" instead, although
that might be a bit of overkill.

We are working with another company. We cannot have a normal shared network
with them; again, that's because of security (see above). We can, however
deposit the database in a very secure place where they can pick it up, use
it, return it, and then we pick it up again. I was hoping that replication
might avoid this.

Thomas F. Ewald
FCS Database Manager
General Dynamics Land Systems
(586) 276-1256


Message: 16
Date: Thu, 7 Sep 2006 14:45:36 -0700 (PDT)
From: <artful at rogers.com>
Subject: Re: [AccessD] Replication and Referential Integrity
To: Access Developers discussion and problem solving
             <accessd at databaseadvisors.com>
Message-ID: <20060907214536.55515.qmail at web88205.mail.re2.yahoo.com>
Content-Type: text/plain; charset=us-ascii

I have mined Access replication extensively and like to pretend that I know
it very well. So perhaps I can help in that regard. However, before we go
there, I must say that I think something is very wrong if only one company
can use the database at a time. Perhaps you are so fortunate that they
reside in time zones that will never collide.

IMO, your first question should be, Why is it that only one company can use
the DB at a time? (And secondly, does this mean that 20 users within one
company can use it simultaneously? Or is it even worse?) I have been trying
for the past few minutes (not long, admittedly), but I cannot see a
situation which forces you into this "one-company" scenario. Further, I
don't see how replication will get you out of this problem, which seems to
me of your own creation.

Using replication strictly within Access, I have successfully tied together
4 branches distributed across North America, with a total of about 70
users, everyone hitting the same fixed-inventory tables. I can say with
confidence that when this is set up correctly, nothing goes wrong. Not a
single collision in over six months of operation. (After that we migrated
to SQL Server.)

So back to your original question. What is lacking in the design that
forces you to restrict access to just one company? Something is strange in
Denmark, methinks. Without knowing anything about your DB, at the very
least I might suggest creating a Companies table and inheriting its PK in
all the immediate tables, so that Company 1's customers have an FK pointing
to Company 1, etc. This would effectively isolate all rows from each
company, and also permit adding Company 3.

Or perhaps I'm missing something.

----- Original Message ----
From: ewaldt at gdls.com
To: accessd at databaseadvisors.com
Sent: Thursday, September 7, 2006 3:57:39 PM
Subject: [AccessD] Replication and Referential Integrity

I've created a database that is used by two companies. For purposes of
security, it cannot be on a network drive shared by the two companies. More
detail isn't really necessary. What it amounts to is that only one company
can use it at a time.

I have had replication suggested to me. I know what it is, of course, but
have only played with it on a very minor and experimental basis. However,
the individual who recommended it to me is more experienced with it, and he
said that it cannot be used with a database that employs referential
integrity. Is he correct? I am sure that he knows more than I do about
replication, but that doesn't mean much.

TIA.


Thomas F. Ewald
FCS Database Manager
General Dynamics Land Systems
(586) 276-1256






This is an e-mail from General Dynamics Land Systems. It is for the intended recipient only and may contain confidential and privileged information.  No one else may read, print, store, copy, forward or act in reliance on it or its attachments.  If you are not the intended recipient, please return this message to the sender and delete the message and any attachments from your computer. Your cooperation is appreciated.

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com