Global Variables (1 Viewer)

[ChrisO]

I am not in a position to test this but I would like someone to test if TempVars are reset on a VBA Quit command. It would seem consistent for Microsoft to reset them but, if they do, then they would not be as 'robust' as some people like to think they are.

And, in general, I'm not against the Reset on VBA Quit because it might have been seen by Microsoft as a security measure to disconnect cleanly from the backend before closing. But that is just a guess on my part and it would not explain why a Quit from other than VBA does not do a Reset… menu Exit or top right ‘X’.

And does a Quit from automation Reset Globals or TempVars?

There are plenty of things to test in the real world before we need to get to 'Fairies at the bottom of the garden'.

Chris.

 

[ChrisO]

To answer one of my own questions…

With CreateObject("Access.Application")
    .OpenCurrentDatabase strDatabasePathAndName
    
    ' Do something.
    
    .Quit
End With

The above .Quit command does not Reset Global variables in the remote application. So the above .Quit is similar to executing Exit from the File menu.

However, if the remote application did a VBA Quit before the above .Quit then the remote application would Reset its Globals.


And something for Microsoft here as well…
From the help topic on the Quit method:

"Remarks
The Quit method has the same effect as clicking Exit on the File menu."

No it doesn't. VBA Quit resets Globals and Exit does not.

Chris.

 

[The_Doc_Man]

Galaxiom

We can not get to the correct result by jumping to the wrong conclusion.

I disagree in a VERY narrow sense. If we jump to the wrong conclusion BUT as a result of that precipitous leak we decide to "tighten up" the code in a given area of our program, we can get to a correct result anyway.

I will acknowledge that such a sequence of development events might have occurred with me in the past. I have been known to go around three left turns when one right turn might have been easier - had I seen the opportunity at the time. Who among us has not? However, since I no longer have a copy of the programs on which the errors occurred, all I can say is that behavior was suspiciously like a reset Global variable. Yes, it is anecdotal, but it's what I've got.

Let's leave it at that and suggest that good, bad, or indifferent, it is always a good idea to handle traps carefully in any critical code areas - which is really what I was suggesting in the first place. If for no other reason, at least you can capture information at the time of the trap and use it for later analysis.

 

[DrallocD]

I put an unbound text box on my form. In the form's OnLoad event, I look up the user by getting an environmental variable called "Username" - which works for us because we are in a domain-base login environment. Windows puts the name of the logged in person in the environmental variable where it can be queried. I look up the person's login name, which is unique across the domain, in my user properties table. I put the user's "Display" name in the unbound text box. I put their role name in another box.

TempVars seem to be a good alternative to this. I use the same approach as you with a SessionID since the global variables are reset if there is an unhandled error and the user selects "End" in the Debug dialogue, or if there is a compile error in the debugger. In both of these cases the TempVar is retained.
Database Properties should not be used for user or session because they hold a single value shared by all users of the database.

 

[ChrisO]

DrallocD.

>>I use the same approach as you with a SessionID since the global variables are reset if there is an unhandled error and the user selects "End" in the Debug dialogue, or if there is a compile error in the debugger.<<

If that is what you do then you must allow the user to use an MDB or ACCDB file. In an MDE or ACCDE file there is no going into debug mode and no End button. Therefore nothing gets Reset except on a VBA Quit command. Furthermore, there are no compile errors simply because there is no source code to compile.

----------

>>Database Properties should not be used for user or session because they hold a single value shared by all users of the database.<<

That would only be true if you have more than one user using the front end. The database properties are unique to each front end and if only one user is using that front end then the database properties are unique to that one user.

----------


If both of the above are true then I can not see it happening any other way other than you are allowing more than one user to use the same MDB or ACCDB front end at the same time.

One user, one front end, where that one front end is a compiled MDE or ACCDE.

Any other way then people are just asking for trouble and deserve it when, not if, they get it.

Chris.

 

[DrallocD]

Chris,

Agreed, but I have 2 sets of users. The first set sometimes share an ACCDB which they customize and change/add code at will. The second set use an ACCDE copied locally with data held centrally. I don't get too many support calls from the second set! The database needed to be robust enough to work in both environments.

 

[ChrisO]

DrallocD.

I do not think that is sufficient reason for your original reply.

1. You did not mention it and…
2. If you have users allowed to modify code then they should be qualified to test the code as other users are going to be using it.

And so, back to the database properties…

Database properties are unique to the user simply because the front end should be unique to the user.

I will not bore people with the details here because there is a demo attached.
But the database properties can be mirrored into a Dictionary.

Public Function AddProperty(ByVal Key As String, _
                            ByVal lngType As Long, _
                            ByRef Value As Variant)

    On Error Resume Next
    
        Key = "User_" & Key
        
        [color=green]' Attempt to get the property.[/color]
        AddProperty = CurrentDb.Properties(Key)
        
        If (Err.Number) Then
            Err.Clear
    
            With CurrentDb()
                [color=green]' Attempt to create the property and assign its value.[/color]
                .Properties.Append .CreateProperty(Key, lngType, Value)
                .Properties(Key) = Value
    
                [color=green]' Validate the property creation and assignment.[/color]
                If .Properties(Key) = Value Then
                    [color=green]' If valid, check if it exists in the dictionary.[/color]
                    If Dict.Exists(Key) Then
                        [color=green]' Change the dictionary entry.[/color]
                        Dict(Key) = Value
                    Else
                        [color=green]' Add to the dictionary.[/color]
                        Dict.Add Item:=Value, Key:=Key
                    End If
                End If
            End With
            
        End If
    
    Err.Clear

End Function

The reason for the extra code, in the AddProperty procedure, will not be understood simply by looking at the code.

If people want to do the work for themselves they will find that the reason for the mirroring, of the database properties to a Dictionary, happens to be more than 200 times faster.

But they will have to do the work for themselves before they go running around the web saying “Look what I know”.
Personally, I have had a gut full of those people.

Chris.

 

[Galaxiom]

We can not get to the correct result by jumping to the wrong conclusion.

I disagree in a VERY narrow sense. If we jump to the wrong conclusion BUT as a result of that precipitous leak we decide to "tighten up" the code in a given area of our program, we can get to a correct result anyway.

Yes. An accidental good result. But in general wrong information leads to wrong results. In a forum where we are sharing information, wrong information is just plain wrong information.

Sometimes wrong information leads to people doing a lot of unnecessary work. One good example is the myth that Access will confuse the controls on a form with the fields in its recordset if they have the same names.

The fact is that the controls collection is the default and is always checked first. On this issue we have posters claiming that they have seen this happen and insist that it is important to always rename the controls.

And like the claim that errors cause variables to be reset, nobody can produce the slightest evidence yet they refuse to back down and go on to repeat the myth in subsequent threads despite detailed dmonstrations of the reality.

I have been known to go around three left turns when one right turn might have been easier - had I seen the opportunity at the time. Who among us has not?

No doubt we all carry superstitions from misunderstood experiences. However an intelligent person is prepared to learn when presented with the correct information. Continuing to promulgate a myth contrary to the evidence is a religious attitude.

 

[DrallocD]

Chris,
I am happy to discuss for as long as this thread remains constructive.
>>1. You did not mention it and…<<
You are correct, I did not presume that everyone is delivering a single user compiled front-end. Access is a multiuser database that can be used either compiled or not. I would argue that the onus would be on you to say only use database properties if you can guarantee users will be running standalone otherwise this may have unexpected consequences.

>>2. If you have users allowed to modify code then they should be qualified to test the code as other users are going to be using it.<<
Also correct, I guess someone forgot to tell them! My initial response should be "did you try to debug this yourself?" ...but I don't want to get fired!

I took a look at your code and I had a question - if you are using database properties to replace publics and the Dict to speed up the REALLY SLOW database properties - isn't making the Dict object public kind of self defeating? You may just as well used Publics in the first place.

Where possible I like to make publics into public properties in a module which can self set if they are ever lost. Unfortunately in the case of a SessionID this was not possible since it was a unique GUID which is why I stored it in an unbound textbox in a hidden form.

 

[DrallocD]

Galaxiom, which version of Access are you using?

When I went from 2003 to 2010 I hit lots of issues where I had to change form.Value to form!Value to differentiate between controls and underlying recordset. What used to work in 2003 stopped working in 2010. I never renamed controls but this may have prevented the confusion.

 

[DrallocD]

>>And like the claim that errors cause variables to be reset, nobody can produce the slightest evidence yet they refuse to back down and go on to repeat the myth in subsequent threads despite detailed dmonstrations of the reality.
<<
Galaxiom, did you read my post where I gave 2 examples of how this happens in my environment? I admit it is only where end users have MDB or ACCDB but it does happen - regularly!

 

[ChrisO]

DrallocD.

There are a lot of wrong things said on the web about Access, but giving each user their own front end is not one of them. It should be expected that it is the case and failure to do so is inviting trouble.

Hence, database properties apply only to the single user who is using the database front end at the time.

----------

In the code I wrote the dictionary object is private, not public:-
Private Dict As Object
However, the database properties are Public.

Global variables are not lost on error under any circumstance; however they are Reset on a VBA Quit.

If it is a concern that the dictionary object is Reset on VBA Quit, which it is, then the database property is still available directly with:-
CurrentDb.Properties("User_Name")

Or the:-
Public Function GetProperty(ByVal Key As String) As Variant
which I wrote can be re-written to first check the dictionary object and use the database property if the dictionary object has been Reset.

The Prop, as in:-
Public Prop As New clsDatabaseProperties
will also be Reset on a VBA Quit but it will be re-instantiated if called again after Reset, so that is not a problem.

----------

The only reason to mirror the database properties with a dictionary object is for the large increase in speed. The underlying values are still available if someone wants to use them.

----------

Given time, this sort of thing will become ‘common knowledge’ for people to claim as their own. In the mean time I think they should do some of the actual work.

Chris.

 

[Galaxiom]

When I went from 2003 to 2010 I hit lots of issues where I had to change form.Value to form!Value to differentiate between controls and underlying recordset.

You are using a reserved word (Value) for the names of controls and fields.It is not the least surprising that you experienced issues.

 

[Galaxiom]

>>And like the claim that errors cause variables to be reset, nobody can produce the slightest evidence yet they refuse to back down and go on to repeat the myth in subsequent threads despite detailed dmonstrations of the reality.

<<Galaxiom, did you read my post where I gave 2 examples of how this happens in my environment? I admit it is only where end users have MDB or ACCDB but it does happen - regularly!

Which post? I have checked all your posts on this thread and not seen any examples.

 

[DrallocD]

You are using a reserved word (Value) for the names of controls and fields.It is not the least surprising that you experienced issues.

:banghead: The form isn't called form and the control/field isn't called Value.

 

[DrallocD]

Which post? I have checked all your posts on this thread and not seen any examples.

My first post (#24). For anyone who wants to reproduce this create a module with the following code:

'Public ShowValues As Variant  'uncomment to cause a compile error due to ambiguous name
 
Public gTest As String
Public Sub SetValues(Optional blnError As Boolean = False)
    gTest = "Global Value"
    TempVars.Add "gTest", "TempVar Value"
    ShowValues
    If blnError Then Debug.Print 1 / 0
End Sub
Public Sub ShowValues()
    Debug.Print gTest
    Debug.Print TempVars!gTest
End Sub

Scenario One:

In the immediate window, run

[FONT=Courier New]SetValues True[/FONT]

This will create an unhandled error Dialogue, click on End

Then In the immediate window, run

ShowValues

You will see that the Global has been reset, but the TempVar is still available.

Scenario Two:
In the immediate window, run

[FONT=Courier New]SetValues[/FONT]

Then uncomment the line :

'Public ShowValues As Variant 'uncomment to cause a compile error due to ambiguous name

next, in the immediate window, run

ShowValues

a compile error is displayed, click OK, comment out the line you uncommented earlier and in the immediate window run:

ShowValues

Again, you will see that the Global has been reset, but the TempVar is still available.

 

[DrallocD]

DrallocD.
The only reason to mirror the database properties with a dictionary object is for the large increase in speed. The underlying values are still available if someone wants to use them.

Seems like a reasonable approach provided:

1. One user per front end db

2. You want the data to persist if the db is closed and reopened. I guess you can always create a Class_Terminate to remove the properties if this feature is not wanted.

 

[ChrisO]

DrallocD.

>>This will create an unhandled error Dialogue, click on End<<

I/we have been saying that Globals do not lose their value on error under any circumstance. They get Reset by program operation. It is not the unhandled error which causes the Reset it’s the click on End.

They are two entirely separate events; first the Error then the Reset. It is important to know the difference because in an MDE file the Error can happen but the Reset will not.

This means that MDE files behave differently than do MDB files. It follows that MDE files should be used by the end user not MDB files. It also means that Break in Class Module will not happen in an MDE file. It also means that an MDE file behaves as if it is automatically set to Break on Unhandled Errors when it is converted from an MDB file.

It also means that the MDE files should be tested as well as testing the MDB files.

And all of this stems from knowing the facts, not the fiction.
Global variables do not lose their value on error under any circumstance; they may be Reset under program control. Two entirely separate events.

They are also Reset by a VBA Quit command.

----------

>>1. One user per front end db<<
I would have thought that is mandatory.

>>2. You want the data to persist if the db is closed and reopened.<<
I do not want them to persist if the db is closed and reopened. I want them to survive a VBA Quit command.


Chris.