Trying to Adapt an Existing Database Design with backend/frontend split

[robert demo via AccessMonster.com]

I have an application that is split into a front end and a backend. The
application is designed for facilities at specific sites. The upshot of my
question is: How can I use the current front-end design to access multiple
sites.

Let's use an example (the following is a hypothetical example).

1) Client 1 is Site = New York City, back-end filename: NY_be.mdb
2) Client 2 is Site = Albany, back-end file name: Albany_be.mdb

Clients 1 and 2 know nothing of the other. Let's say that a new client, The
State of New York, wants to be able to view data from both New York City and
Albany using a single copy of the current front end. The backends for both of
these sites have the exact same table names. So with the current front end,
how can I link to both Client1 site and Client2 site. The back-end file names
are different, but the tables contained within those backend files are named
exactly the same.

So, how can I link to both clients such that when the code tries to retrieve
data for Table A, it looks for data in both copies of Table A, one contained
in NY_be.mdb and one in Albany_be.mdb. The idea is to combine the data.

Logistically, the idea is that each site would simply e-mail a copy of their
back-end to a user for The State of New York, who would then try to link to
each back-end file. The file names are different, but the problem is that the
table names in each file are the same.

I do not use the built-in MS Access linking and importing features. I have my
own routine that functions quite nicely. The way my application links to the
back-end at startup is that it looks for the existance of just one of the
tables in the backend. Also, the linking is complicated by the fact that, for
security reasons, I have revoked all permissions to the back-end tables and
am using RWOP queries in the front end.

I'm finding that, at the moment, if I try to link to each back-end file, the
last back-end file linked to is the only one that stays linked. Perhaps this
result is just due to the way I'm linking to the back-end files. But, still,
I don't see how the front end would be able to link to two copies of Table A.
Although each Table A contains different data, the table name is still the
same.

Is there anyway I can handle this with a minimimal amount of changes to the
front-end code? At the moment, I think it is safe to say that, if both Table
A tables were combined, that each row could be uniquely returned using the
current front end code.

Thanks for any help and suggestions.

 

[Sharkbyte]

Robert:

What is the possibility of combining the tables, from each db, into single
copies? You mention that Table A would probably work, but what of the
remainder?

You could create a multi-site version, of the back-end, which includes a
site-code in each table. Then simply import all the data, from each db, into
the multi-side tables. This would, however, require a modification to
identify which dataset the front-end wants to work with.

Good luck.

Sharkbyte

 

[robert demo via AccessMonster.com]

Thanks for the response. Yes, I think I will do something like this.

I plan to create a small app that clients can use to link to each backend for
each site. The small app will add the records from all tables to a
corresponding master table. Then when data has been imported from all
backends, the small app will become a backend to the application. So
initially the small app will be a frontend for the individual sites. Then
when all the site information is added to the master tables, the small app
becomes a back end to the application. This should work as long as each site
name is unique, which I'm fairly confident is the case.

The only hurdle is the table names. The master table names need to be what
the main app is expecting. But the individual site backends also use these
names. So, could I have RWOP queries on the small app master tables with the
correct name that the main app is expecting. Then the master table names
could be different, allowing for easy appending of individual site data.
Then the main app links to the queries in the small app. I don't think this
is possible, but if I could set something up like that.

Or alternatively, how about whenever linking occurs from the small app to the
individual site backends, the code changes the name of the table by appending
say "1". Then when the user indicates that he is finished linking, the code
removes the 1 from all the tables in the small app and it now is the backend
for the main app.

Suggestions are welcome

Robert:

What is the possibility of combining the tables, from each db, into single
copies? You mention that Table A would probably work, but what of the
remainder?

You could create a multi-site version, of the back-end, which includes a
site-code in each table. Then simply import all the data, from each db, into
the multi-side tables. This would, however, require a modification to
identify which dataset the front-end wants to work with.

Good luck.

Sharkbyte

I have an application that is split into a front end and a backend. The
application is designed for facilities at specific sites. The upshot of my

[quoted text clipped - 43 lines]

Thanks for any help and suggestions.