How to set a VBA global variable in one project & use in another project?

Question

Background

A version control database called VCS (project name = VCS) is used for a variety of version control tasks.

Various other application databases have VCS set up as a reference.

In these app databases a global variable is set to identify which tables within them to backup:

Global Const APP_TABLES_TO_BACKUP = "tbl1, tbl2, tbl5"

Problem

The question is, how to reference this APP_TABLES_TO_BACKUPconstant from the VCS project?

Because each app has its own setting for this value, it makes sense to set the value in a module of the app and call that value from the VCS database project.

Any ideas on how to do that?

So far...

I've searched and found Chip Pearson's Understanding Scope, but not quite gotten how to set and reference the global variable.

In the immediate window of the app I can type ?APP_TABLES_TO_BACKUP and retrieve the tables, but I can't seem to see how to reference that same variable from the VCS database so it can be used in a subroutine there.

Any ideas?

Answer 1

Since my earlier post apparently didn't come through a couple days ago...

After searching a bit further I came across Paul Murray's custom database properties which work nicely.

With some tweaks I created a module that includes generic property add, remove methods.

' ---------------------------------
' FUNCTION:     AddDbProperty
' Description:  add custom properties to a database application
' ---------------------------------
Public Function AddDbProperty(DbProperty As String, _
                    DbPropertyValue As String, _
                    Optional DbPropertyType As Long = DB_TEXT, _
                    Optional DbFilename As String = "Current")
On Error GoTo Err_Handler

    Dim db As DAO.Database
    Dim prop As Property

    If DbFilename = "Current" Then
        Set db = DBEngine(0)(0)
    Else
        Set db = OpenDatabase(DbFilename)
    End If

    'add the property
    Set prop = db.CreateProperty(DbProperty, DbPropertyType, DbPropertyValue)
    db.Properties.Append prop

Exit_Handler:
    db.Close
    Set db = Nothing
    Exit Function

Err_Handler:
    Select Case Err.Number
      Case Else
        MsgBox "Error #" & Err.Number & ": " & Err.description, vbCritical, _
            "Error encountered (#" & Err.Number & " - AddDbProperty[mod_Dev_Properties])"
    End Select
    Resume Exit_Handler
End Function

' ---------------------------------
' FUNCTION:     RemoveDbProperty
' Description:  remove custom properties from a database applciation
' ---------------------------------
Public Function RemoveDbProperty(DbProperty As String, _
                    Optional DbFilename As String = "Current")
On Error GoTo Err_Handler

    Dim db As DAO.Database

    If DbFilename = "Current" Then
        Set db = DBEngine(0)(0)
    Else
        Set db = OpenDatabase(DbFilename)
    End If

    'remove the property
    db.Properties.Delete DbProperty

Exit_Handler:
    db.Close
    Set db = Nothing
    Exit Function

Err_Handler:
    Select Case Err.Number
      Case Else
        MsgBox "Error #" & Err.Number & ": " & Err.description, vbCritical, _
            "Error encountered (#" & Err.Number & " - RemoveDbProperty[mod_Dev_Properties])"
    End Select
    Resume Exit_Handler
End Function

' ---------------------------------
' FUNCTION:     UpdateDbProperty
' Description:  add custom properties to a database application
' ---------------------------------
Public Function UpdateDbProperty(DbProperty As String, _
                    DbPropertyValue As String, _
                    Optional DbFilename As String = "Current")
On Error GoTo Err_Handler

    Dim db As DAO.Database

    If DbFilename = "Current" Then
        Set db = DBEngine(0)(0)
    Else
        Set db = OpenDatabase(DbFilename)
    End If

    'add the property
    db.Properties(DbProperty) = DbPropertyValue

Exit_Handler:
    db.Close
    Set db = Nothing
    Exit Function

Err_Handler:
    Select Case Err.Number
      Case Else
        MsgBox "Error #" & Err.Number & ": " & Err.description, vbCritical, _
            "Error encountered (#" & Err.Number & " - UpdateDbProperty[mod_Dev_Properties])"
    End Select
    Resume Exit_Handler
End Function

Using the IDE immediate window properties can be added/updated/removed.

For example:

?AddDbProperty("My Property Name","My Property value")

Retrieving the property is readily done via vba

CurrentDb.Properties("My Property Name")

Thanks all for the also viable inputs!

Answer 2

You can add a reference to an external database in your VCS database.

How? See this answer.

Make sure your module and function names between the two databases don't conflict. Then you can just reference your const by using MyModule.APP_TABLES_TO_BACKUP, or if you have multiple projects with the same module and constant name, MyProject.MyModule.APP_TABLES_TO_BACKUP