Refer to the CONTENTS of a Global Variable in code.

I have several MS Access applications that use Global Constants to store paths and file names for report templates, save routines, etc. I must now modify these applications so that in the event of a "disaster recovery" scenario, the application can be accessed through Citrix. All the paths to files or folders will change if this is required, so I want to have a single parameter I can toggle so that when the application loads, the globals (now variables rather than constants) can be populated depending on whether the app is running normally on the server, or on the Citrix platform.

I'd like to use a reference table to store BOTH the VARIABLE NAME and the VALUE to place in the variable. Normally in code I would assign the value to the variable explicitly in code, e.g.

modGlobalStringVariable = "Z:\Path\Subfolder\Template.xlsx".

What I would like to do is store BOTH the variable name "modGlobalStringVariable" AND the value "Z:\Path\Subfolder\Template.xlsx" in a reference table. Then I can just pull a record set from the reference table and loop through it, assigning the variable name AND value once each time through the loop, e.g.

Do Until rsVariableNames.EOF
     strVarName1 = rsVariableNames!VarName
     
     strVarName1._______ = rsVariableNames!VarNameValue

     rsVariableNames.MoveNext
Loop

My question: What is the correct VBA Syntax to achieve this? The "________" is analogous to if I was using fields to populate a record set from a reference table, where I could go "Variable1 = rsRecordSet.Fields(strFieldName)"and each time through the loop, the strFieldName can change to correspond to the variable being populated. I need to know the syntax to make the first line of the loop plug in the Variable Name to be populated and the second line actually POPULATE that variable (rather than populating the string that HOLDs the variable name.)

Any suggestions welcomed.
Buck_BeasomDatabase DesignerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fabrice LambertConsultingCommented:
Hi,

First, don't use global variables.
Second, don't use global variables.
Third, did I say: "Don't use global variables" ?

Your issue is pretty simple, create a Parameters table with the following columns:
ID, Autonumber Long, PrimaryKey.
Name, Short Text.
Value, short Text.

In this table you'll store your parameter's names and values

With VBA, create a function to retrieve the parameters, and call it whenever you need it:
Public Function GetParameter(ByVal name As String) As String
    Dim sql As String
    sql = vbNullString
    sql = sql & "PARAMETERS Name Text(255);" & vbcrlf
    sql = sql & "SELECT Value" & vbcrlf
    sql = sql & "FROM Parameters" & vbcrlf
    sql = sql & "WHERE Name = [Name];"

    Dim db As DAO.Database
    Set db = CurrentDb

    Dim qd As DAO.QueryDef
    Set qd = db.CreateQueryDef("ParametersQuery", sql)
    qd.Parameters("Name") = name

    Dim rs As DAO.Recordset
    Set rs = qd.OpenRecordset(dbOpenSnapshot)
    If Not(rs.BOF And rs.EOF) Then
        GetParameter = nz(rs("Value"), vbNullString)
    End If
    rs.Close
    qd.Close
    db.queryDefs.Delete qd.Name
End Function

Open in new window

Fabrice LambertConsultingCommented:
Side notes:
Variables names are not dynamic, so you can't iterate trough their name.
Gustav BrockCIOCommented:
First, don't use global variables.
Second, don't use global variables.
Third, did I say: "Don't use global variables" ?

And forth: Don't listen to such black/white statements.
Do use global variables when you feel so. Nothing wrong about that.

To answer your question: Refer to a global variable as to any other variable. It's that simple.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Fabrice LambertConsultingCommented:
Do use global variables when you feel so. Nothing wrong about that.
Kidding I hope ?
 Unless you love writing unmaintainable applications … (Don't call me to debug that).
Seriously, global variables are bad for a bunch of reasons, no mater the language. This article explain why:
http://wiki.c2.com/?GlobalVariablesAreBad

Plus, in the context of VBA, should an unhandled error occur (and trust me, error handling mecanics are rarely written), the project collapse and all global variables reset to their default values.
Go Wonder what it was supposed to be after that.
Fabrice LambertConsultingCommented:
At worst, sinces they are settings, encapsulate your variables in a class, and implement the Services Locator  design pattern.
Buck_BeasomDatabase DesignerAuthor Commented:
Well, I really appreciate all of the invective and opinions. Since I have been using Global Variables WITHOUT ANY PROBLEMS since I started programming in Access in 1998, I'll be sure to go back to the two dozen applications I presently support for the largest company IN THE WORLD in our line of business and change them all to comply with all of the faith-based arguments on why they shouldn't be used.

So I welcome the responding population to continue those arguments off line.

Meanwhile, if anybody has ONE LINE OF CODE that can allow me to specify a variable name using a value from a record set, THAT would help.

Thanks.
Dale FyeOwner, Developing Solutions LLCCommented:
Buck,

I now (since 2007) have replaced global variables with tempvars.  This is a collection of variables which avoid the problem mentioned by Fabrice of varibles losing their values when an unhandled error is identified.

in your case, you can simply use a loop to go through your recordset, something like:

While not rs.eof
    tempvars(rs!VarName) = rs!VarValue
    rs.movenext
Wend

Open in new window


Then, when you want to refer to these variables, you can use any of the following syntax values:

1.  tempvars!GlobalStringVariable
2.  [Tempvars]![GlobalStringVariable]
3.  Tempvars("GlobalStringVariable")

if you use syntax #2 above, you can even use this value in a query, which you cannot do with a global variable, unless you use Fabrice's advise and create a function to encapsulate the global variable.

Dale
Buck_BeasomDatabase DesignerAuthor Commented:
Thank you. I will give this a shot.
Gustav BrockCIOCommented:
Since I have been using Global Variables WITHOUT ANY PROBLEMS since I started programming in Access in 1998

So have I, though since 1994. Only sloppy programming can cause trouble to global variables.

It is also a great advantage that, during development and testing, you can clear them all by calling the command End.

I believe you can find the value of a named variable with Eval, but I can't recall the syntax - bedtime now …
Fabrice LambertConsultingCommented:
Since I have been using Global Variables WITHOUT ANY PROBLEMS since I started programming in Access in 1998,
As long as you know and you remember what you are doing, it is your business. Nightmares occures when someone else take over your job, without your way of thinking neither all of your background.
The fact that you don't have any issue is a specific case, I'm not convinced it will last forever.

I believe you can find the value of a named variable with Eval, but I can't recall the syntax
Function Eval(Expression As String) As Variant
Use it with extreme caution, as it is an open door to ackward programming and potential security breachs.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.