Better to keep all tables in the same database (be vs. fe)? (1 Viewer)

[DKO]

Just a quick question for the pros.

Given: Back end and front end are both Access 2007 databases. Back end is on a shared server and the front end is distributed to all users. (with auto updater if that matters)

Generally speaking, is it better practice to keep all tables, static and dynamic, in the back end of a database - or is it better to keep the static tables (tables in which the data never changes), in the front end? Does it make a difference performance wise?

Also - would the answer be different if the back end was a SQL Server?



The reason I ask:
My db is currently in mdb format and I have it setup with only the dynamic tables in my back end and the static tables in my front end (thinking this would reduce network traffic). But, I'm in the process of converting up to accdb format and will soon convert the back end to a SQL Server 2005 db.

 

[DCrake]

To answer your first question I tend to place static tables in one back end and all main tables that collect day to day activity in another then simply link the two.

You second question can be answered by stating that all tables need to stored in the same place.

David

 

[Atomic Shrimp]

I'd agree, with certain exceptions - local tables can be useful for:

-Storing local configuration settings specific to the client machine (can't think of a brilliant example, but there are probably valid ones - maybe if your application has to interact with something that is in different locations or versions on various client machines)

-Temporary storage of entered data before committing to the main DB tables - order entry, for example. This can be done in a temporary table in the BE, keyed to the user so they don't interact or interfere with each others' records, but it's often simpler, cleaner and more reliable just to store temporary data locally.

-Temporary storage of data captured in offline modes - for example, if it's a stocktaking application, you may want it to be able to capture stock count data in a remote location where there is no network access. (it may also in these scenarios be necessary/valid to locally cache copies of back end tables, if they are required to validate data captured offline)

In all of these cases, I would still divorce the interface from the data - and keep the local tables in a separate DB alongside the FE, so that the FE can be simply overwritten with future upgraded versions.

 

[DKO]

Thanks for the quick input, guys.

 

[Galaxiom]

In all of these cases, I would still divorce the interface from the data - and keep the local tables in a separate DB alongside the FE, so that the FE can be simply overwritten with future upgraded versions.

I would like to emphasise this point. I have seen otherwise knowledgable developers discussing temporary data being written into the FE. This should never be done. Firstly it will bloat the FE and require compaction. Secondly it increases the chance of corruption. A static FE is a safe FE.

Temporary data should be written to a separate local database. For want of a better term I call this the "Side End". Compared to writing to a BE across a network this does save traffic and eliminates the need to isolate the temporary tables from other users.

However care is needed with Roaming Profiles. A good location for the FE in a well secured domain is in the User's Application Data folder where they have write permissions. This allows Auto FEUpdater to be used easily and it will roam with the user.

However a large Side End in this folder will also roam and generate a lot of unnecessary traffic at logon and log off, not to mention being saved in the server backup. With a large number of users this can amount to a huge burden of irrelevant data in the backup.

An alternative location for a Side End is the User's Local Settings\Application Data folder where they have write permissions but is not included in the default roaming profile. However it has to be regenerated when the database is run since the user account is deleted at logoff when using roaming profiles.

Otherwise just set up a permanent Side End on each client computer in a specialy created folder with appropriate permissions.

Regarding tables of user settings, I would prefer to keep them in a BE on a network drive where they are accessible regardless of the machine being used. They could be kept with (but not in) the FE for users with Roaming Profiles. This is simpler as the separation of users is automatic.

 

[Atomic Shrimp]

Temporary data should be written to a separate local database. For want of a better term I call this the "Side End".

If that's not already the right term for it, it ought to be. I like it.

 

[Galaxiom]

There is a little more detail about the location and use of those user folders in this thread.

http://www.access-programmers.co.uk/forums/showthread.php?p=900437

 

[Galaxiom]

If [Side End] is not already the right term for it, it ought to be. I like it.

Actually there are two types of side ends. One holds the users settings and the other is the "jotting pad."
While I am happy for them to have the generic Side End, SE, I think they deserve something more specific.

Whimsical perhaps:

The Left End: This remains with the FE and stores dynamic user settings. It is in effect "left" with the database. It is stored with the FE in the roaming profile.

The Right End: This is the end where the temporary tables and values are written. In effect the "write" end (or for the righthanders the one we write with.) It is deleted in a roaming profile because it is stored in the Local Settings folder.

Edit: In retrospect I would probably swap that. The Left one is left behind and the Right one is the right one to keep.
(No marginalisation of Lefties now either.)

And adopting DCrake's above model of the static tables and the main data in separate BEs we might call the static tables a Bottom End. No, unfortunately that would be a BE.

How about Low End? LE. It really does service very low levels in the application. The unchanging low down.

High End: Centralised user settings held a on the server. HE. In a high place on the network.

 

[Banana]

Just to offer one more perspective:

The idea of Side End does appeal to me, but in one of project I did, I would not think it very practical mainly due to the fact that:

1) We were using Citrix for access
2) We don't have complete control over the server- the changes has to be approved by the administrator
3) They already have a certain way of doing things, and in our case, it involves making a throwaway copy on the fly.
4) I did test quickly and find that even if I were to get the permissions, the performance actually would be worse than if it were in a single file.

Because of this, we have everything in single FE except, of course, for the actual data tables which are linked to a SQL Server instance. But the FE does have a number of temporary tables and such.

Of course, the problems identified with storing local tables in FE are non-issue here because it's only a throwaway copy- when the Citrix session ends, that copy of FE get trashed. I can't remember the specific reason why they chose to use a throwaway copy as that would increase bandwidth & start up time (the FE was pretty big... 20 MB?), but well there it is.

In another project I did, I didn't have a Side End at all, though I did use local tables and had auto-compact set upon closing, so it also worked. Can't remember having corruption in that case. Maybe it was just luck.

 

[Galaxiom]

With the side ends it is just down to good design practice. Application files should not write to themselves. Anything goes wrong with a save then it won't run.

Sure it is easy to replace a broken front end or use a didposable but the problem need never happen. A static front end cannot corrupt and never needs compaction.

The file date does not change which is important when using AutoFEUpdater because it uses the date to determinine if a new version needs to be downloaded.

 

[Lightwave]

I have just become a Side End convert

 

[Rabbie]

Just my 2 cents worth. I would only have temporary tables in the FE. If it's staticdata that is needed by all FE databases then I would store it in a separate DB on the FE

 

[gemma-the-husky]

i use local tables for some things, such as temporary order tables etc, as already mentioned

the thing to watch out for, is that if you release an upgraded version of the dbs, then your users will loase their local tables - so you need to take this into account.

 

[Galaxiom]

i use local tables for some things, such as temporary order tables etc, as already mentioned

Just my 2 cents worth. I would only have temporary tables in the FE. If it's staticdata that is needed by all FE databases then I would store it in a separate DB on the FE

Some responses suggest that some other developers employ the concept of a Side End, but the practice of writing temporary data to the Front End is apparently adopted by many very experienced and highly knowledgeable developers.

This might suggest that the potential problems are very rarely encountered. On the other hand the use of disposable FEs is quite common, so any corruption can be immediately overcome by restarting the application to obtain a fresh copy. Perhaps corruption is the reason for the popuarity of disposable front ends?

Like the Side End the disposable also eliminates the need to compact the front end. But compare the user experience. Front Ends clumsily compact themselves by leaving the application but can elegantly initiate the compaction of a side end.

Alternatively a disposable Side End is much like a disposable notepad. It is tiny compared to a Front End. Side End databases can easily be generated on demand from code in the front end making the application self contained. In a server situation a fresh Side End can be copied from the master location where the latest version of the Front End is held.

Moreover this is a matter of implementing best practice at the very foundations of program design principle. Surely it is a corollary from the principle of separation of the user interface and the data?

As a novice in this field, I am actually quite surprised to see apparently widespread acceptance of practices violating a basic tennant of programming that operates at a level far deeper than an Access application.

"A file shalt not write to itself"

A database front end is an application file. As a matter of principle, applications should not write to themselves. This separation of application and data is a fundamental premise of programming as old as programming itself. You won't see an executable writing user settings to itself or using itself as a scratch pad. They use ini files and temporary files.

the thing to watch out for, is that if you release an upgraded version of the dbs, then your users will loase their local tables - so you need to take this into account.

This is best taken into account by never writing to the FE. Replacing the FE does not replace the separate database with the local tables just as upgrading an exe application does not wipe any ini files which carry the user settings.

Where the design of the Side End changes and values need to be retained the FE should include code to upgrade the SE while maintining the existing data.

However anything that matters to a user is better stored on the server so it can be retrieved from any workstation.

Changes to settings such as filters and column widths in Datasheet view are prompted for saving by Access into the FE. Don't even let Access offer this option. Disable the close button and provide your own Close button with SaveNo in the Close Method. With this done the FE will be entirely static and hence utterly incorruptible.

I don't provide a facility for users to save change columns or filers because I know there will be some who invariably mess up their interface and are unable to fix it themselves. However, if desired, these settings can easily be recorded in a table using code and applied with the OnLoad event of the forms or reports.

When recorded on the server this system provides roaming settings for the user and may include machine specific values. A default button can be provided to return them to the designers original settings which may also be calculated for screen specific installations.

Here is the link again to an earlier discussion of Side Ends.

http://www.access-programmers.co.uk/forums/showthread.php?p=900437

 

[Sess]

This is extremely interesting because I have to make my programs all split up so that the data is stored separate from the user interface programs like BE and FE but it will become very complicated for the FE because each department in manufacturing needs a different screen. I also will need to triplicate my work to the three languages English French and Spanish for the user interfaces. The users have already put forward the specification that they do not want to open an application then chose a language they want to open an application in their own language. yikes.
Just putting it out there because in a link earlier someone suggested to have it carried in the individuals logon and they were looking for samples of who and why and so that language issue is a sample.

 

[Galaxiom]

Endre explains how to painlessly achieve multilanguage capabilities in the one interface using textboxes instead of control labels in this thread.

http://www.access-programmers.co.uk/forums/showthread.php?t=177033

You won't have to triplicate your work and it is entirely transparent. I cannot imagine a better solution to multilanguage interfaces.

 

[Sess]

Brilliant - thank you for that link. Saves me lots of work now and in the future.

Endre explains how to painlessly achieve multilanguage capabilities in the one interface using textboxes instead of control labels in this thread.

http://www.access-programmers.co.uk/forums/showthread.php?t=177033

You won't have to triplicate your work and it is entirely transparent. I cannot imagine a better solution to multilanguage interfaces.

 

[gemma-the-husky]

i think galaxiom makes excellent points

on reflection, i probably write stuff into the local database, purely (or merely) for convenience. I dont have to bother linking tables etc, but yes, it does result in some bloat, and it can produce issues of "lost data" if the dbs is replaced/updated

 

[gemma-the-husky]

This is extremely interesting because I have to make my programs all split up so that the data is stored separate from the user interface programs like BE and FE but it will become very complicated for the FE because each department in manufacturing needs a different screen. I also will need to triplicate my work to the three languages English French and Spanish for the user interfaces. The users have already put forward the specification that they do not want to open an application then chose a language they want to open an application in their own language. yikes.
Just putting it out there because in a link earlier someone suggested to have it carried in the individuals logon and they were looking for samples of who and why and so that language issue is a sample.

you do have alternatives, with multi language environments - it is some work, but for each control you need to store alternative texts (maybe use the tag property) - and then based on the language setting, change the displayed text on all controls on a form.

 

[Galaxiom]

you do have alternatives, with multi language environments - it is some work, but for each control you need to store alternative texts (maybe use the tag property) - and then based on the language setting, change the displayed text on all controls on a form.

Endre's technique stores the different language values in tables and controls bound to a record source query to pull ControlDisplay with relationships between:

CurrentUser >
Users.User > Users.Language >
ControlDisplay.Language & ControlDisplay.ControlName > ControlName.DisplayText

This system is used for both controls and control masquerading as labels.
The ordinary labels are deleteted. The display names of value in the RowSource queries of combos, textboxes and listboxes doen with the same techniquie.

No VBA is required. It is a masterpiece of a technique to deal with multiple languages.