Access 2007 Database Issues - Very Complicated

[Novalith]

Sorry I could not enter a more descriptive title, I am not sure if what I
want to do is even possible but I know this is probably the best place to
come for help. I have designed and built a ver complicated database with
multiple users having multiple "Frontends" and accessing multiple base
databases.

The problem is when I update the format of a form I have to update it in 30
different frontends. What I want is for those frontends to update
themselves. I figured out that if I use an autoexec macro to download
another macro called "Update" and run it then I can use that "Update" Macro
to delete the old form or report and grab a new copy from the master file.

This is not a problem if I am only ever updating a few forms, the problem is
I tend to update multiple queries, forms and reports which equates to a lot
of downloading. Also I update them at different times so the update one week
might be a few forms, then the next week it may be a few reports. Then the
problem becomes that if user A does not run his database for 2 weeks he will
only get the latest update and not get the updated forms from the week before
as I have overwrittend the "Update" macro that replaced the forms with the
update macro that replaced the reports.

So end the end I decieded to make 2 macros. The first is called "Update"
and contains the most recent changes. It is designed to only modify a few
files. I then created another macro called "Full Update" which will replace
every Query//Form/Report in the database with the most current ones.

The frontends will download both files by default, but here is where I run
into a problem. I want to be able to use version numbers somewhere in both
the frontend and the Master so that if the numbers are only 1 step different
it runs the small updated (i.e if the FE is on 1.0 and the current update is
1.1). If the version numbers are greater then .1 apart it will run the full
update macro.

At this point I am stuck. Where can I put the versions numbers and how can
I get the 2 db's to compare them? I have not used VBA because I have less
than 2 weeks to get this figured out and I am not sure I can learn enough in
that time to do this. If it can be done in VBA though I am willing to try if
someone can point me in the right direction. I am desperate because I may
have to scrap this and I have been desperatly trying to learn Access fast
enough to get this done before my deadline. I will be monitoring this all
weekend for help so if you have questions please ask me.

 

[Gina Whipp]

Novalith,

Have a look at...

http://www.autofeupdater.com/

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Novalith]

Gina,
Thank you for taking the time to reply. While that sounds like
exactly what I need, unfortunatly I am working on a government network. One
that has just recently adopted Windows Vista, so you can imagine how long it
would take me to get them to install software such as that provided by your
link.

The good news is I have almost solved my problem, now the only thing
I have to figure out is why a macro NOT tied to a specific form requires the
form it is referencing to be open in order to run. That does not seem
logical to me as all the information needed to find the form is right there
in the macro. Yet it will tell me the form does not exist unless open.

I am still looking for a solution to that new issue. Once I have
that I believe if I work 12-14 hours I can redo the databases and distribute
them before work on Monday.

 

[Gina Whipp]

Novalith,

Missed that you were on a government network... I haven't used a macro in
years so I'm crossing my fingers someone comes in and can come up with a
solution to your macro issue.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Novalith]

I have it figured out! I don't quite have the time to explain in detail
right now, but I will come back and update the thread once I do get the time.
Basically it was solved using a form and a single record table that holds
the current and update version numbers. The update version number get
changed when the from is downloaded and opened and the current version gets
changed to the update version once that form is closed at the end of the
update process. The autoexec macro uses the dlookup command to compare the
2 values before deciding which update macro to run.

Thanks again for trying to help, I am getting really good with macros now
but as you said you don't use them, I assume your using VBA instead. Someday
I will learn that when I get the time.

 

[Gina Whipp]

Novalith,

Thanks for the update and glad you got it working...

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Dale Fye]

I'm working in a government environment as well.

I use another access application, similar to Tony's Front End Updater, but
as an mdb/accdb it doesn't need approval to install it.

I put a copy of this app on each users desktop and label it as if it is the
application.

It looks in a predetermined folder on the users computer to see if the
application is there. If not, it copies the application (the whole thing)
from the network to that predetermined location. It then kicks off the
application using the Application.FollowHyperlink method.

If the file exists in that location, then it queries the Versions table in
that application for the largest Version number. It then compares this value
to the largest Version number in the master file that sits on the network.
If the version number of the app sitting on the network is larger than the
one on the local pc, then it deletes the version on the local machine, copies
the version from the network, and kicks it off.

After kicking off the actual application, my FrontEndUpdater quits, so I
only have one version of Access running.