How can I save a global variable value after dynamically loading a reference?

Question

I have a Access 2003 database that will dynamically load MDB databases as a library reference. The reason for this is this database is a menu front-end for 60+ application databases. Rather than deal with permanently referencing all these databases, the menu front-end will dynamically reference what is needed when the user makes a selection. I was working on moving this database to Access 2010 and creating a custom ribbon. I started using the technique from here to capture the ribbon object in a global variable when the ribbon loads. I then ran into the problem where I could verify the code was running and the global variable was correctly being assigned the ribbon reference but after the database would run through it's startup routine, that global variable would get reset to Nothing.

To verify what was going on, I created a simple database for testing. In this database, I had a module with a global variable:

Public obj as Object

I then had a function like this:

Public Function SetObj()

Set obj = Application

Debug.Print "IsNothing=" & (obj Is Nothing)

References.AddFromFile "Test.mdb"

Debug.Print "IsNothing=" & (obj Is Nothing)

End Function

Obviously, in my code, "Test.mdb" refers to an actual file. If I run this code, Debug.Print gives me "IsNothing=False" for both instances, but after the function finishes and if I wait a couple seconds, Debug.Print will give me "IsNothing=True". If I comment out References.AddFromFile, Debug.Print gives me "IsNothing=False" no matter how long I wait.

It makes sense to me that since Access has to re-compile the VBA code after loading the library that all global variables are reset. I've experimented with moving the global variable into a class, but since I then need a global variable for the class, the class variable then gets reset instead. I tried using a local variable in the function to save the value of the global variable, but it looks like Access waits a couple seconds after the code is finished running to do the re-compile, so that doesn't work either. Does anyone have any other ideas to accomplish this?

I believe it's more user friendly and makes for a "seamless" experience. It also makes it easier to share information between databases. For example, I have a single Reports form that can run any report from any database. If the databases were in a separate instance, they couldn't see the selections from the Reports form. My goal right now is to handle a custom ribbon in Access 2010. Since it's no longer possible to use CommandBars (Thanks, Microsoft!) and there's no programmatic way to get a Ribbon reference, I need to save the Ribbon object in a global variable. — TheOtherTimDuncan
– TheOtherTimDuncan, Commented Aug 17, 2011 at 17:38

David-W-Fenton · Accepted Answer · 2011-08-17 20:38:42Z

1

I don't really know if this will solve the problem for this kind of reference, but in general, I don't use public variables for this kind of thing, but instead use a STATIC variable inside your function. It would be something like this:

  Public Function SetObj() As Object
    Static obj As Object

    If (obj Is Nothing) Then
       Set obj = Application
    End If
    Set SetObj = obj
  End Function

Then you'd just use SetObj as an object for using your application. In a production app, you'd need tear-down code, too, but I've omitted that here.

I doubt this helps, but your code struck me as rather inefficient and incomplete.

answered Aug 17, 2011 at 20:38

David-W-Fenton

23.2k4 gold badges48 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TheOtherTimDuncan Over a year ago

I did try using a static variable, but that gets reset as well. Also, I do use your approach for other scenarios, but for my end goal of saving the Ribbon reference, I only have one chance to capture it and no way to re-capture it if the value is lost.

TheOtherTimDuncan · Accepted Answer · 2011-08-18 11:50:57Z

I figured out a solution to my problem, and thanks @David-W-Fenton, as your answer gave me the idea. I use your approach in a library database for caching frequently-accessed values that are stored in a table but don't change after the initial startup. Those values aren't lost every time the references change, and that's when the light bulb lit up.

The solution is to put the global variable in a library database. Access looks to be only resetting global variables in the database that the reference is being loaded into - which makes sense after thinking about it. So since the library database isn't the one being re-compiled, it doesn't get it's global (or private or static) variables reset.

What I ended up doing was creating a new module in an existing library database. It has a private variable and two methods - one to set the variable, one to retrieve the variable value. In my menu front-end database, when the ribbon loads and calls my callback function, rather than saving the ribbon object in the front-end database, I pass it to this module for saving. I now no longer lose that ribbon reference whenever new databases are added to the library references on the fly.

Collectives™ on Stack Overflow

How can I save a global variable value after dynamically loading a reference?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related