Community Pick: Many members of our community have endorsed this article.
Editor's Choice: This article has been selected by our editors as an exceptional contribution.

Table Based Access Variables

Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Microsoft SQL Server database developer, architect, and author specializing in Business Intelligence, ETL, and Data Warehousing.
Published:
Updated:
This article is about how to create your own variables in Microsoft Access that can be used anywhere and saved between user sessions, resulting in making life easier on users, make maintenance easier for developers, and frames up Super Awesome Developer Ninja Stuff!    

All code is in the attachment at the end of this article.
 

Summary


  • The Variables Table
  • How To Get And Set Variables
  • Use in Queries, Forms, and Reports
  • Damn Handy Stuff you can Implement Right Now
  • Super Awesome Developer Ninja Stuff!!
 

The Variables Table

Create a simple table named VARIABLES, with only four columns:

  • txt_name, short text-50, Indexed = Yes (Duplicates OK), which is the name of the variable.  Here I'm using ALL CAPS with underscores, which is just a style preference.
  • txt_value, Short Text-255, Indexed = Yes (Duplicates OK), which is the value, and long enough to hold the longest possible value, which is typically a file path.
  • txt_description, Short Text-255, which is developer love notes on the purpose of the variable.
  • id, integer (not AutoNumber), which is a numeric value to uniquely identify the variable.
VARIABLES table in design viewHere's a sample in datasheet view sorted by id.  

VARIABLES table in datasheet view


How to Get and Set Variables

Before we can write code, since we're accessing tables a reference will need to be set to the Microsoft ActiveX Data Objects Library (DAO can also be used, but this article uses ADO.  

In any code window, mouse to the Tools menu and select References.  The below dialog box will appear.  Navigate to the M's and click on 'Microsoft ActiveX Data Objects {whatever version is available} Library', and hit the Ok button.
Tools : ReferencesBelow are two public functions named fn_get (that's shorthand for function get, or maybe effin' get) and fn_set.   If you prefer you can use your own names, such as fn_read and fn_write, but I'm using fn_get and fn_set because it's a shorter name.  

The fn_get function passes the id number and returns the value as a string.

fn_getTo set a variable, call public function fn_set and pass two parameters:  One for the id, and one for the new value.  Will return True if successful.

fn_set


A quick and dirty Get and Set in VBA code


Immediate Window demo of how to call in VBA code


One limitation:  The String return value may need converting

One limitation of this methodology is that fn_get returns a string variable, which will require converting if the value needs to be manipulated as a date, number, or boolean value.
 Immediate Window use of conversion functions


Use in Queries

Queries can use fn_get just like any other criteria.  For example, the below query returns all policies that are greater than or equal to the variable where id = 61, which is currently one million dollars (1000000).
Query Design


Let's use names instead of numbers:  Enumerations

I don't know about you guys, but my ability to memorize lots of numbers and be able to map them to an English-sounding name is not all that good.  Queries must use the number, but for forms and reports a quick way around this is to declare an enumeration called AppVariables containing all names equal to all numbers.

 
'Application variables stored in VARIABLES.
                      Public Enum AppVariables
                          'Application Information
                          APP_NAME = 1
                          APP_VERSION_NUMBER = 2
                          APP_VERSION_DATE = 3
                          APP_OWNER = 4
                          APP_NAME_LEGAL = 5
                      
                          'Maintenance Stuff
                          LAST_ARCHIVE_DATE = 11
                          LAST_ARCHIVE_RECORDS_DATE = 12
                          LAST_REPAIR_COMPACT = 13
                      
                          'User interactions
                           LEVEL = 20
                          DEBUG_MODE = 21
                          VIEW_MSGBOX_DO_YOU_WANT_TO_QUIT = 23
                          
                          'Most Recently Used (MRU)
                          MRU_LAST_REPORT_1 = 31
                          MRU_LAST_REPORT_2 = 32
                          MRU_LAST_REPORT_3 = 33
                          
                          'Output paths, back-end data locations
                          PATH_EXCEL_EXPORT = 51
                          PATH_DATA_FILE = 52
                          PATH_ARCHIVE_FILE = 53
                          
                          'Single amounts-Easy to store in VARIABLES.
                          MIN_DOLLAR_AMOUNT_POLICY = 60
                          MAX_DOLLAR_AMOUNT_POLICY = 61
                      
                      End Enum

Open in new window


This way, Intellisense will display the names and not the numbers
Intellisense with AppVariablesThe only change in fn_get and fn_set needed to pull this off is to change the id argument from an Integer to AppVariables enumeration.
 

Use in Forms

Every form's Open event should call fn_get to populate text box values with variables.  For values that the user can write, in that textbox AfterUpdate event call fn_set.
An Options form with six textboxes that match variablesOptions form, VBA code


Use in Reports

Here's how to use fn_get to get the application owner and name, and always display them at the top of every report.  Here's the design, with the report header having three labels named lbl_report_header_1 through _3, and the page header having a label named lbl_page_header_1.  
Report Design ViewHere's the VBA code behind it
Private Sub Report_Open(Cancel As Integer)
                      
                      Dim sMessage As String, sAppOwner As String, sAppName As String
                      
                      sAppOwner = fn_get(APP_OWNER)
                      sAppName = fn_get(APP_NAME)
                      
                      With Me
                          'Report Headers
                          .lbl_report_header_1.Caption = sAppOwner
                          .lbl_report_header_2.Caption = sAppName
                          .lbl_report_header_3.Caption = "Demo of how to use in reports"
                          
                          'Page header (will appear from Page 2 onward.)
                          .lbl_page_header_1.Caption = sAppOwner & " - " & sAppName & " - All Indicators (continued)"
                          
                      End With
                      
                      DoCmd.Maximize
                      
                      End Sub

Open in new window


Here's what the first page of the report looks line...

Report Page 1... and then every page after that...
Report every page after 1

Damn Handy Stuff you can Implement Right Now


  • Version Name and Number
  • Company Name on forms and reports
  • Most Recently Used (MRU)  The last report you ran, the last form you opened, on a tabbed form the last tab that was open, …
  • The current accounting month:  Are we closed yet?
  • Back-end database locations (when using automated Linked Table functions for automated re-linking):  Data file, archive file.
  • File locations:  Where you like to export files to, where’s my Help doc
  • Single values that are too small for their own table:  Commission rate, minimum birthday allowed for data entry, limits to data entry, etc.
  • Stalking your users:  Who was the last person to log in, and when…
  • Roll your own security level
  • Do you want to see this message again?  If not, check this checkbox, and it’ll write to a table..

Super Awesome Developer Ninja Stuff!!

Note:  Will require some form of bribery for me to write these articles…

How to kick users out of the databases
Handy when you need to force downtime to deploy a new version or run reports when no one is in the database.
  • Create a variable that is a flag for whether users can access the database.
  • On startup check the flag.  If true, proceed with entry.  If false, display a message and close.
  • To kick out all current users, have a main form always open with a Timer event of five minutes, that checks this value.  If true, give them a message and a warning that the next time the app will automatically closed.
Version Control and How to automatically deploy front-ends to all users
If you are responsible for deploying Access applications to multiple desktops, you can build functionality using Visual Basic or another Access app to use the Version Number to verify that users are running the correct version, and if they’re not, kill their copy, and copy the correct version from a network drive to their pc.  This saves a TON of time walking over to everyone’s pc and deploying a new Access app.

Archiving data
Typically keeping an Access database small improves performance, so 'old' data can be moved to an 'Archive' database and kept separate from production data.
  • Create a variable for last backed up value (say the end of the year)
  • Create an archive back-end database, with all of the tables that are in production, without relationships, and with Long Integers instead of AutoNumber columns.
  • Create append queries that append all rows from production table to archive table.  
  • Create delete queries that delete all rows from production table where the timestamp is <= the variable.
Debug Mode
How to see controls that your users can't see.  Useful in development.
  • On forms and reports you can have textboxes with all of the id fields to aid development so that they are visible only to the developer.
  • On the Form Open event, test the variable for Debug Mode.  If 1, set certain textboxes .Visible property to True, and if 0 set it to False.
  • For extra caution, set the Background Color to Bright Orange, which no one would ever actually expose to users, to separate them from controls that users can see.
Help
A variation of this idea is to have a table where each row holds 'Help' text for a given form or form control, and by clicking a button and calling a specific fn_get(#) a popup form will appear and display that field.  This enables a 'Help' system within the Access database.  Further possibilities are to use fn_set to allow users to create their own 'Help' text.

Thank you for reading my article, please leave valuable feedback. If you liked this article would like to see more, please click the Yes button near the: Was this article helpful? at the bottom of this article just below and to the right of this information.

I look forward to hearing from you. -  Jim - twitter @sqljimbo
access-variables-tutorial.mdb
9
10,568 Views
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Microsoft SQL Server database developer, architect, and author specializing in Business Intelligence, ETL, and Data Warehousing.

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.