Need to store a number in ACCDE file A that can be accessed by ACCDE file B without opening A

I'm trying to find a way to store a number (just an integer) in ACCDE file A in such a way that ACCDE file B can check the number in A without opening A.  I've been trying to do this using custom file properties, but I've been unsuccessful so far.  Please take it for granted that I can't use tables.

The bigger picture: I split my MS Access programs into frontends and backends, and I've read that it's best for each user to have their own copy of the frontend to minimize risks of corruption.  To make it easier to distribute the frontend when I make changes, I am writing a module that, when run in the user copies of the frontends, will check the frontend to see if it is the current version by comparing its version number (stored as a file property or whatever) to the version number of the master copy of the frontend (which will reside in a known location.

I hope I explained well enough for a start. Thanks in advance for the help!
Who is Participating?
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
Why can't you use tables?

You can use a custom Database Property. To add one:

Dim db As DAO.Database
Dim prp as DAO.Property

Set db = Currentdb
Set prp = db.CreateProperty("DBVersion", DB_TEXT, "1")

Open in new window

You can then check that value:

If db.Properties("DBVersion") = "SomeValue" Then
  ' do something here
End If

You can also check this in a remote database:

Dim db As DAO.Database
Set db = OpenDatabase("Full path to the database"

If db.Properties("DBVersion") = "SomeValue" Then
  ' do something here
End If

Open in new window

So what exactly are FileA and FileB?  are they the local (desktop) copy of the Front End and a new updated version?

Curious why you "can't use tables"?

A very common approach is to have the following tables:

tblVersionLocal  --- resides in the front end, unlinked
tblVersion -- resides in the shared back-end

When a new version of the FE is ready for release, the developer increments the version number in the newly updated FE and in the BE.  

The code in, for example a startup form in the front end compares the version number in tblVersionLocal (front end) with tblVersion (back end).  When a mis-match is detected, an update is run to copy a new version of the front end to the user's computer.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<I hope I explained well enough for a start. Thanks in advance for the help! >>

 Besides what's been offered, you can also:

1. Write a text file to disk with the version info in it.
2. Rely on the file attributes them selves, which is what this does:

  There are tons of "FE updaters" out there and this is one of the better ones.  Used to be free, but now is reasonably priced.  Depending on your needs, it might be worth the $$ rather then trying to do it on your own.

 For example, one of the problems you'll run into is how to do the update if the FE is already open.  That leads you into making it a "launcher" type app (runs before your DB does).  Then you need to worry about how to make sure someone doesn't open the DB without using the launcher.

Then there's different versions of Access, terminal services, trusted locations, etc.

I'm not saying you can't do it on your own (I used the local/master copy table setup and a simple batch file for years), but if you look over the features of that FE Updater, you'll see some of the things you might be in for.

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Jolio81Author Commented:
I suppose I should have looked into pre-made FE updaters... but this is more fun.

My reason for not wanting to use tables is I wanted to make a module that would work with zero setup; just import the module and call the updater on startup.

Mbizup: yes, A and B are the local and master/updated copies of the frontend.

Currently, my updater uses a table in the shared backend (tblVersion), and stores the "local" version as a property of the database.

LSMConsulting: Using my feeble skills, I tried using custom properties: the user ACCDE file would check for a version number in the master/new copy of the ACCDE file; the problem I was having is it causes the master copy to open and run all its code, which was conflicting with the way my updater works.
      Set db = OpenDatabase("Full path to the database")
cause the remote database to be opened?

Here's how my updater currently works:

if I'm not a compiled ACCDE file then
    if there is an ACCDE file that has been created or modified in the last 10 seconds then
        if the user confirms that a new ACCDE has just been made then
            increment the version number in the table in the backend
            create/set dbProperty local version number (+1 so that the next ACCDE is correct)
        end if
    end if
    create/set dbProperty MasterCopyLocation = (replace accdb with accde)
    if I am in MasterCopyLocation then
        if a commandLine switch was used when I was opened
            delete file at Command
            copy myself to Command
            open the copy of myself at Command
            messageBox "you can't run me from here!"
        end if
        close myself
        if dbProperty.Version < Version in backend table then
            open the file at MasterCopyLocation using as command line switch
            close myself
        end if
    end if
end if
Jolio81Author Commented:
Solution works great! Thanks!
I am ignorant when it comes to best practices: do I need to put "db.close" after I run this?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You didn't "Open" it, so you should not close it. Instead, set it to Nothing:

Set db = Nothing
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.