Splitting back end into static and dynamic tables

[memilanuk]

Hello,

I'm working with splitting a database for the first time, and as I looked at the table relations again, I realized that many (most) of the tables are fairly static - look-up tables for the most part. Most of the other data is in one or two tables. Is there any way to (easily) split the back end up into two databases (one with the static look-up tables, and one with the dynamic member data), while maintaining the various relationships between the various tables?

TIA,

Monte

 

[Banana]

Generally, the backend would always have *all* of tables present with relationship enforced. But the front-end only need to link to "dynamic tables" while having its copy of static tables locally. Note that you do not need to have relationship enforced inside the front-end; that will be handled in the backend (hence the reason for backend having all tables present).

So the only difference is that you should only link to a subset of the backend tables.

Note further that there's a differences of opinions in whether the front-end get to have any local tables especially for lookup, but more specifically for tables that do get change occasionally. Personally, my view is that if the content of lookup table changes occasionally, few times a year, this can be rolled up as an update and distributed with intention to replace the front-end, making any bloat a moot issue. But if you have any table that are meant to be exclusively private to user (e.g. user's own temporary tables, preferences or other data that isn't shared among users), some feel that having a 2nd backend is a good benefit and distribute it side by side with the front-end (so the user have their own copy of the 2nd backend while continuing to centrally link to a common single 1st backend).

HTH.

 

[memilanuk]

I don't know if it matters or not... in this particular situation, the database is single user, sitting on the local PC disk. The current version is not split; I am looking at splitting it partially because everyone seems to think it is a better idea in terms of protecting against data corruption, and partially because it cuts the size of the 'front end' down enough that I can realistically compress it and email it to the end user, rather than having to work out a time when its convenient for both of us for me to drive to his place and copy the whole thing over from a thumb drive, as I am still working on 'improving' it as time goes on and need to update his version from time to time.

Part of my thoughts as far as splitting up the back end further was that if I could do *that*, the actual database/tables containing the membership info could be similarly zipped and emailed back to me for off-site (relatively speaking) archival.

Thanks,

Monte

 

[Banana]

Well, splitting database has benefits even for single-user because it keeps data and application separate. Though they designed the file to contain both as a convenience (who want to drag around a bunch of files anyway? ) it's still problematic in maintaining the file's consistency. Furthermore, even though there may be only one user, it doesn't necessarily mean there is single "connection" - there could be several forms open, piece of VBA codes running which all appear to Access as "separate users" even though there's only one user operating the application.