[AccessD] Sort of like a filter only not

Andy,

  Partial replication.  Each customer would have a replicated MDB with only
their data.

Jim Dettman
President,
Online Computer Services of WNY, Inc.
(315) 699-3443
jimdettman at earthlink.net

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Andy Lacey
Sent: Friday, December 12, 2003 5:46 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Sort of like a filter only not


Morning everyone. Poets day is with us again but in the meantime I'm looking
for bright ideas here.

I have a biiiiig system here. I mention that it's big because any solution
to this cannot involve changing every screen, report and query that's
affected - far too much work. And the challenge is this: the users want/need
to put selected parts of the system in front of customers. When they do so,
however they do not want to show the names of other customers. So, for
example, if they pull down a combo to select a customer they don't want to
suddenly show that the customer's main competitor is also a customer. If
they print a report of what the customer has received in the past 12 months
they don't want to accidentally request All Customers and be embarrassed
like that. In other words, in that situation, the system needs to be
cast-iron certain to only ever show the details of one customer, or at least
a controlled sub-set (may be more than one as the customer may belong to a
group).

So my idea was that before the meeting one could run a process which would
extract all data for the required customer(s) into an identical set of
tables in another MDB then relink to that MDB. Not all tables would be
affected. Look-up tables, for example, would still point to the live MDB.
But there would be quite a few involved because I'd need the customer table
plus all related ones. This approach sounds ok, and an appropriate set of
queries wouild soon do it, but it has its hassles, such as changes to the
table structures which would require me to either keep the copy MDB
up-to-date as well as the live one OR to create the copy one from scratch
each time - but then I've also got to create indexes and relationships too
and that's quite a job.

Another idea I had, but I have to reject, was to rename the linked table (ie
give it a different alias) then create a query with the same name as the
table which filters the customers. All queries, screens and reports would
then see that. But that's out because I use SEEKs on the customer table to
access records quickly, and I can't SEEK on a query.

So, has anyone got a better idea? Or a simple way of resolving the issue of
changes in the table structures (can you somehow clone a set of tables
complete with all indexes and relationships)?


--
Andy Lacey
http://www.minstersystems.co.uk

________________________________________________
Message sent using UebiMiau 2.7.2

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