?
Solved

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
6
Medium Priority
?
607 Views
Last Modified: 2013-12-18
Hi,
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!
0
Comment
Question by:Jolio81
6 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 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

0
 
LVL 61

Expert Comment

by:mbizup
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.
0
 
LVL 58
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:

http://autofeupdater.com/

  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.

Jim.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Jolio81
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.
Doesn't
      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 = currentdb.name (replace accdb with accde)
else
    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
        else
            messageBox "you can't run me from here!"
        end if
        close myself
    else
        if dbProperty.Version < Version in backend table then
            open the file at MasterCopyLocation using currentdb.name as command line switch
            close myself
        end if
    end if
end if
0
 

Author Closing Comment

by:Jolio81
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?
0
 
LVL 85
ID: 39726260
You didn't "Open" it, so you should not close it. Instead, set it to Nothing:

Set db = Nothing
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

809 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