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
584 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 84

Accepted Solution

by:
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

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

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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

Set db = Nothing
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

734 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