Splitting an MS Access database--only Tables in Back End?

So, I'm building this brand-new project in MS Access (2007, but I don't think it matters), and I'm getting close to the point where it's time to split the database for final te开发者_开发技巧sting & deployment. The ususal description of a split setup is "All tables in BE, everything else in FE", but I'm wondering if there might not be some appropriate exceptions to that:

1. I plan to keep one table of application-management data in the FE. This is one row with information such as version numbers and the application title. It would be hidden from the users.

2. While following up on the Related Questions, I saw the idea of a small startup form in the BE that tells users (nicely) to go away & open the FE instead.

3. It seems to me that some of my queries would actually belong in the BE instead. These particular queries are, in usage, similar to Views in SQL Server--that is, they draw together normalized data into a more denormalized presentation, and/or do a "first cut" filter on some data that's all stored together but really has one significant difference. An example of the latter is a Personnel table, with a BE query that presents a subset that are all in the same Department for further manipulation in a form (or FE query) that is only concerned with members of that department.

Do these sound like reasonable practices, or should I jsut stick to the "All tables/everything else" division?

Those seem like reasonable practices, with the possible exception of #3. The whole point of splitting an Access DB into front-end/back-end is to allow you to take the front-end off somewhere else and work on it and then replace the version the users are accessing, without replacing their data. Anything you do that doesn't interfere with that "prime directive" should be just fine. Of course, if you want to change the back-end schema, the back-end/front-end distinction doesn't help you, but that's what you get when you're dealing with Access, right?

The possible issue with #3 is that you're effectively lumping any changes you might need to make to the back-end queries in with schema changes. In other words, if you need to change those queries in any way, you need to take the back-end database offline, just like you would need to do if you were changing the schema. If you're confident that's OK, then it's not a problem, but you should be aware that you're limiting yourself in that way. How much of a limitation it is really depends on what goes into those back-end queries. If they're the sort of thing that you should never need to change unless the underlying schema is also changing, then you should be fine. But if they're the sort of thing that might need to change because a user has changed their mind about how they want that data presented, then you'll be in a bit more trouble.

There is no performance to be gained by putting your "views" in the back end. The only QueryDefs that should be stored there are the ones you use when doing schema work. For instance, in all my replicated apps, I keep a handful of queries that display data from the replication system tables. None of those are relevant to the front ends.

In other words, anything that's part of the application belongs in the front end. From Jet/ACE's point of view, there really wouldn't be any difference in terms of performance, but you'd have a lot more work to utilize those back end "views" -- you can't link to them as a table like you can with a view from an ODBC data source.

Point 1: great. The version table, is indeed, part of the app, and not the BE. You could also compare that information in the FE to a matching version in the BE, to make sure everybody upgrades when required.

Point 2: excellent. Kick them out of the back-end. And don't forget to disable the bypass key.

Point 3: I would say no, never. The BE is (a) your tables (b) the relationships. Just think that anything else will cause trouble if you need to change it while the app is in prod. It is really different from a client server approach where everything you merge and filter in the backend is a sure performance improvement, and where you can easily change a view while the app is being used (ok I know some will react on this, but it works just fine as long as you are carefull :)

Edit: The first two paragraphs answered the wrong question as I misread point #1 and thought they meant user specific data. On rereading the original question I do agree with #1 regarding application specific data.

I disagree with #1 and keeping any local settings in the FE. There is no advantage to keeping a local table vs a table with the network user ID as a unique index in the BE database file. The idea is that replacing the FE database file when you make changes should be absolutely as easy as possible. Having to import settings every time there is a new version of the FE is simple some more work. Yes, you can create queries and code to do the work but why even bother.

That said I only ever kept one setting locally and that was the location of the backend database file. For that I used an INI file and API calls. I could've used a registry key or something else. Now when I'm doing custom work for clients on their network then this isn't really required. This was more for shrink wrap software which folks could download from my website. Which had my datasbase location in the linked tables connect string.

I agree with #2 because that's what I do.

As David as stated #3 shouldn't be done because it doesn't do you any good. You need those queries in the FE where you will be using them

I think the gimmik should be changed to "All data in the BE, everything else in the FE". Your front end might need to hold some tables that are not part of your "Database Model", but belong to what I should call your "Front End Metamodel":

• A simple example could be a multilingual interface where all text and caption properties can be displayed in different languages. In this situation, you could have a local\FE table that would hold all references to caption and text properties used in your application.
• Another example is when your application menus are held in a specific table, used as a source for generating form-specific menus/commandbars at runtime. Such a table should also be in your FE.
• A "connections" table, holding the different connection strings to similar databases (such as accounting databases, or different subscribers to the same publisher) should also be on your FE.

Instead of having your Front End metamodel held via tables embedded in your FE mdb file, you can also choose to hold these tables as xml files. They are at first distributed altogether with your app. It is then possible to distribute new versions of these tables (available connections, updated translations, etc) without distributing a complete FE file.

Following this logic, query/view definitions are definitely part of the FE metamodel, and holding all these views in a simple local XML or embedded Tbl_View table (2 fields only: View name and View syntax) does the trick.