Global variables are a wonderful way to make your code consistent, powerful and easy to use, but the native Access global variable, one declared in a stand alone module has a critical flaw: it will reset if there is a problem in your code, and it does not retain it’s value once you close Access.
Use TempVars if you don’t need multi-session
If your looking for a reliable global variable than look no further: use TempVars in your code. But they don’t last between sessions so I came up with a hearty replacement I use everywhere in my code, hence it’s my first coding article of 2011, I’ll be referring back to this article constantly in the future.
So what are multi-session global variables, (GV)? They are used to store information between starts in your Access program. You may use your program on Monday and the variables will still retain their value on Tuesday, even after restarting Access.
I’ve created three methods of global variables:
- ReadGV(VariableName, VariableType): Will read the value of the global variable
- WriteGV(VariableName, VariableType, VariableValue): Will write the value of the global variable
- DeleteGV(VariableName): Will delete the global variable
Doc with All Code
All of the code can be found here.
Step 1: Create tblProgramOptions table to store your global variables
You could use other methods, but the fastest way to store variables are in a local access table. Each user has their own copy, allowing you to customize the experience for each. If you’re looking for a GV that can be consistent across all users, then you’re probably better off with another technique.
Enum ProgramOptions
The first time you use one of the three methods you are going to be pleasantly surprised with the intellisense when you get to the VariableType portion of the method. The list you defined in the Enum is the same one that pops when you press space bar after the first comma.
Example Uses
The first thing you need to do is invoke WriteGV in your code to store the variable name. If it’s the first time you’re using it the code will create a record, otherwise it’ll replace the prior value:
WriteGV "strLastName", strText, "Soto"
The method will save the value “Soto” in a new record with a variable named strLastName. Notice the lack of parenthesis around the function call, you are expected to use WriteGV on it’s own line of code.
To read the value use ReadGV:
Me.lblUserLastName.Caption = ReadGV("strLastName", strText)
Here I’m using the function to read the variable into the Caption property of the label control lblUserLastName.
Finally, if I want to delete the variable, (which I often don’t do), I would use:
DeleteGV "strLastName"
This article introduced you to Enum possibilities in your code and provides you with a easy way to store variables too. Let me know in the comments section your thoughts and if you’ve developed any alternatives to this problem.
If you love this article than consider subscribing to my blog here. I’m also available for speaking engagements and consulting on Access with SQL Server projects, you can contact me here.
I know a really old posting but was hoping to use this code but I get compile error on ADODB.Recordset. User defined type not declared. Access 2007 accdb. It appears I need to add some more references.
Not keen on this idea, firstly extra references don’t always seem to port to other computers, especially in runtime runtime without full access installation.
Secondly, & more important is I am little worried adding a extra type of recordset/DB I am worried if I have not declared specifically throughout my very large database I might get unexpected result that don’t show up until too late.
I am no expert and might be talking rubbish, I wrote most a long time ago & just make little additions from time to time.
Can I declare it differently, what if I take out the adodb.?
Is adding this reference 100% sure NOT to effect anything even if I have declared recordsets previously with perhaps poor protocol?
thanks I/A
Greg –
You could use late-binding with ADODB if you wanted but this is too much work. Also, the way how ADO libraries are versioned is a little different from how other libraries (e.g. other Office product’s object library) versions. Because of that, it’s not usually a problem resolving the early binding for ADO on different computers. The notable exception was Windows 7 SP1 fiasco that happened few years but it already has been fixed. Generally we use either 2.8 and 6.1.
HTH.
Just thinking of doing something similar to retain incomplete new order entry data between sessions.
Saved me some time.
Thanks!
GoogleDocs… great… my IT departement blocks all offsite storage and “cloud” based services such as GoogleDocs or GoogleApps.
Worse yet, now Google has closed down its GoogleApps so no more of that easy access.
z,
We’ve updated the blog post so that the code can be downloaded from the blog itself rather than Google Docs. Hopefully, that will be more accessible and add a bit of sunshine to your day.
Unfortunately my company seems to be blocking google docs. Since the articles rely on other stuff on Google Docs that makes the article itself less than useful.
jwc
hello I have read your articles with great interest. I am attempting to connect to sql server bypassing the login screens and use your “OpenMyRecordset” code, however I do not understand how to open the data in a query/table format? ie. I investigated the ” .CreateQueryDef” code but cant see how to use it with your code. Any help would be great.thanks.
David, thank you for your comment & question.
Because OpenMyRecordset creates an ADODB.Recordset, you can’t just bind it to a form the same way you would bind an Access query. In this scenario, you have 3 possible choices:
1) Use DSNLess connections to link to the table or view you want to be able to edit and use all Access functionailty. Juan talks a bit about managing DSNless connection for linked tables here:
http://accessexperts.net/blog/wp-admin/post.php?post=471&action=edit
This is what we usually do for editable forms; note that for complex query, we use a view.
2) Use a passthrough query for read-only view and bind it to a datasheet form
3) Bind ADO recordset to a form which requires VBA coding and has some caveats. You can see how how to bind an ADO recordset here:
http://support.microsoft.com/kb/281998
Do note that doing so has several restrictions; you can’t use several Access functionality, linked subforms and so forth.
Hello Juan,
I read with interest all your articles about Access+ SS, but I see that you write the connection string in the VBA code.
I have a little question.
If we need to change the connection we must modify the code…
It’s not better have a file or table where we save our connection(s) (for example: production instance, test instance or development instance)?
We like storing the string in code to prevent users from changing it.
You could save it using our multi-session technique explained here:
http://accessexperts.net/blog/2011/01/12/multi-session-global-variables/
You would then just call the connection string by using ReadGV().
Thanks
Juan
Too bad Galaxiom never got back because it’d have been interesting to see example(s) of classes being implemented as multisession global variables
The basic concept of a Multisession Global is good. However I would suggest taking it one step further by implementing them as a class.
This would make the code portable. It would also support multiple instances of the class to connect to different global variable tables. This would allow the same class to be used, for example, with personal settings and another for system wide settings applied to all users.
Also the “methods” really would be Methods rather than Functions and their scope tightly controlled to the instance.
Galaxion,
Thanks for that tip, if you do use classes please report back your experience.
Regards
Juan