Global variables

Having been an Access guru until 2004 and then not really doing anything with it since, it has been joyous recently to get drawn back into the world of Microsoft's desktop database. There have been a lot of changes since 2004 though, mostly for the best. Even the changes that are not so good (the demise of user-level security, for example) I can understand - Microsoft want to push developers who need user-level permissions towards other platforms, like SQL Server.

But I digress. Doing Access development again has been fun, and I found myself thinking about global variables on the way into work this morning. What are they, and where should you put them?

Well, a global (or environment) variable is a variable with global scope, meaning that it is visible and accessible throughout the program. And in Access, there are two places you can put them:

1. Create a module called something like basGlobal and add the following:

Option Explicit

Public strMsg As String 'Adds a global string variable called strMsg, accessible everywhere because it's public
Public Const lngSomeNumber As Long = 994 'Adds a numeric global constant called lngSomeNumber

2. Create a table and store it there. Create a table called USysMetadata (and watch how prefixing a table with USys ["user-system"] hides it from default users) with two columns, MetaName and MetaValue. Store the name of the global variable in MetaName and the value in MetaValue.

Easy, right? But which approach to use, when? Especially when both have their advantages. The module approach is better for performance, especially if your database is deployed compiled (MDE or ACCDE). Retrieving variable values from USysMetadata might typically involve a DLookup("MetaValue", "USysMetadata", "MetaName='YourVariableNameHere'") or some SQL, neither or which is so fast. But on the flip side, if you need to add more global variables, well, you can't do that in a multi-user environment so easily for the basGlobal approach, since you need exclusive access to make design changes. By contrast, to add more using USysMetadata, just add a new row to the table. Couldn't be easier, and exclusive access is not required.

The other difference here is that USysMetadata allows variable values to be retained after you've logged out - basGlobal doesn't allow this.

So, some general rules (whilst noting that there are always exceptions to such rules):

• For global constants (i.e. not variables) use basGlobal for performance.
• For session variables (i.e. that don't need to be retained between log-ins) use basGlobal for performance.
• For true system variables (i.e. that can be set and read by all users and/or that need to be retained across sessions) use USysMetadata for flexibility.

It's nice to be back, offering up my Access two penn'orth, even if no-one reads it.