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