Ken's Sample db's

Ken's Sample dbs

Sample db using conditional formatting with a boolean (yes/no) field. (Access 2002-2003 format and '97 format)

Sample db using using a list box to build a sql statement to use as a recordsource for a subform. (Access 2002-2003 format)

Sample db using the running sum feature. (Access 2002-2003 format)

Sample db with a simple function. (Access 2002-2003 format)

Sample db takes common data and consolidates it into a single field. (Access 2002-2003 format)

Sample db using using a popup form to enter date range for a report and display this date range on the report. (Access 2002-2003 format)

This illustration shows one of the main upsides to using SQL Server (or other database sever), as a back-end to an Access front-end:

Sample db that returns focus to the original control after the secondary control executes code. (Access 2002-2003 format)

Sample db uses a combo box to filter records in a form. (Access 2002-2003 format and Access 97 format)

Task DB

Here's a older version of a db I did - Was wondering if anyone had any suggestions / ideas on how to improve / add functionality to it... I know the project form needs work and and the 'tag' bit is unfinished... Any other ideas?

Sample database with a form and subform and their associated one to many tables (Access 2002-2003 format and Access 97 format).

Access Tip 1: Default Values

To set the default values for controls, select the control on the tool bar and press F4. The default value properties list will appear. I like to turn off all of the 3D stuff by resetting some the values like 'Special Effect', 'Border Style', etc. This way I don't have to manually change them after the fact...

Simple db used to print labels. (Access 2002-2003 format)

http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=6405&d=1090579677

Adding 'All' option to combo or list box control

Sample database showing one way to add 'All' as an option to a combo or list box control (Access 2002-2003 format).


ken

Linking to table with code

Sample database with simple code linking to a table in another databse via code.

Front-End Token

When I set up an MS Access database in a multi-user environment and need to have a front-end on each client I will deploy the application with 3 basic .mde files. The first is the back-end file copied to the server which contains all of the data tables. The second is the master front-end file and is also copied to the server. I refer to this file as ‘master’ front-end because it is not executed but is copied to the client’s local workstation when the database is first deployed and when a new version of the front-end needs to be distributed. The third file is what I refer to as the front-end ‘loader’. This is the file that the end user runs on their local workstation when they want to open the database. As far as they know this loader file is the actual database. I usually email them the loader file which they save to their desktop.

The first time the user executes the loader file it copies the master front-end to the local workstation, launches the local copy of the front-end and closes itself. The next time the user wants to open the database, providing there is not a newer version of the front-end available on the server, the loader file skips the step where it copies the master front-end over to the local workstation and simply opens the local front-end and closes itself.

The final scenario is where a new version of the master front-end needs to be deployed and has been copied to the server. The master front-end, and hence the local copy of the front-end, has been set up with a database variable that I refer to as the front-end version number. When a new version of the front-end is ready to be deployed I change the master front-end database version variable and copy it to the server deleting the older version.

Now when the user wants to run the database, they execute the loader file, it compares the front-end version numbers in the local and the master front-end files. When it finds they are different it replaces the older local version with the newer master version. Then again, it executes the local front-end and closes itself.

All of this is pretty standard but here's the problem; What if the user tries to open the local front-end file directly? None of the version checking routines will execute and the user will continue to run the older version of the front-end!

To overcome this I pass a token from the loader file to the front-end that must be validated in order for the front-end to open. If the user tries to run the front-end directly, the token will be absent and the front-end will not open.

In the loader file I use the command line option '/cmd' in the shell() function to pass the token to the front-end. When the front end opens, it checks the token with the complementary command() function. If it fails to receive the proper token, the front-end advises the user of the error and closes.

Code

In the loader, after the version checking routines have executed, the code that opens the front-end looks something like following:


In the front-end the code that checks the token looks something like:

Of course the front-end and loader files need to have the shift key disabled and be compiled into .mde files to protect the token.

--Note-------------------
I have included sample front-end and loader files. All that you should have to do is copy them into a folder and go into the loader file (holding the shift key down so that you get to the code), and change the string vars to your file locations. When you open the loader it should open the FE. If you try to open the FE by itself it should error and close. Or if you change the token value it should error and close...

Passing a string value to a form or report when it opens

The following database (2003), demonstrates how to pass a value to a form or report when it opens by using the forms openargs property.

Not so much a sample database but several good videos:

http://www.youtube.com/results?search_query=Microsoft+Access&search_type=

Custom Security

Sample database with basic custom security objects (Access 2002-2003 format).

(User name: John Doe, Password: password)

Disable Shift Key

Sample db with example of how to disable the shift key when the database opens. (Access 2002-2003 format)