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

Posted on 2013-12-17
Last Modified: 2013-12-18
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!
Question by:Jolio81
LVL 84

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39725306
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

LVL 61

Expert Comment

ID: 39725319
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.
LVL 57
ID: 39725334
<<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.

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.


Author Comment

ID: 39725559
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

Author Closing Comment

ID: 39725811
Solution works great! Thanks!
I am ignorant when it comes to best practices: do I need to put "db.close" after I run this?
LVL 84
ID: 39726260
You didn't "Open" it, so you should not close it. Instead, set it to Nothing:

Set db = Nothing

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question