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
537 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 84

Accepted Solution

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

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:Jolio81
Comment Utility
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
Comment Utility
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
Comment Utility
You didn't "Open" it, so you should not close it. Instead, set it to Nothing:

Set db = Nothing
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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 …

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now