Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2005
    Location
    North Carolina, USA
    Posts
    11

    Question Unanswered: Automatic Updating?

    Ladies and gents,

    I have author'd an MS-Access database that is used by about 10 other folks. It's probably set up in a pretty typical way. It has a front end and a back end file. I "compiled" the front end into an MDE file and that's what each user has on their workstations. The MDE file references the back end MDB that sits on the server.

    This database is a "work in progress". As new things come up, I add new functionality to it. I keep a copy of the front end MDB file on my computer and on the server. I edit the front end MDB file, make the changes to the forms, queries, reports, etc, then create the MDE, and post that MDE to the server. I have a batch file on the server that copies the MDE file to each user's local machine, and then each user runs their local copy of the MDE file to access the data. Whenever there is a change, I email all the users and ask them to run the batch file to update their machine.

    It's pretty straight-forward, but I'd like to build into the database a way to update the file. The problem I'm finding as I scour the Internet for how to do this is the file that is over-written on the workstation cannot be opened at the time. In other words, the MDE file cannot copy "over itself".

    What do you fine folks suggest I do? My other option is writing a small batch file that goes on each user's machine that they run whenever they want to use the database. That batch file would make a copy of the MDE file and then open that newly copied version. I'd rather not do that, because then all my users will have to copy a new shortcut, etc. I'd rather have it as transparent as possible. Any ideas?

    Thanks!

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    One method you could use is to have each of your users run your app thru a "stub" app where the stub would check for version compatibility (and update the main app prior to running if needed) and launch the app whlie closing itself ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Sep 2005
    Location
    North Carolina, USA
    Posts
    11
    Thanks for the reply.

    What I ended up doing is creating a batch file that I placed on everyone's machine along with the MDE front end file. I re-mapped their desktop shortcut to that batch file instead of to the MDE file directly.

    The batch file copies the latest MDE file from the server to the user's local machine and then opens it. This way, the user always has the latest copy of the front end on their machine.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    That is "crude but effective". It will be slower to start for the user, since it copies the MDE every time, and will obviously cause more network traffic. I use the method mentioned by M Owen.
    Paul

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by pbaldy
    That is "crude but effective". It will be slower to start for the user, since it copies the MDE every time, and will obviously cause more network traffic. I use the method mentioned by M Owen.
    Paul,

    And just WHERE do you think I GOT the idea from???

    Although, I think is was a bit roundabout thru Izy and Pootle ... Who floated it to someone else and I just happen to remember the mentioning ...
    Back to Access ... ADO is not the way to go for speed ...

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Quote Originally Posted by M Owen
    And just WHERE do you think I GOT the idea from???
    Little old me? I actually got it from somebody else years ago. I guess we both owe them:
    Paul

  7. #7
    Join Date
    Sep 2005
    Location
    North Carolina, USA
    Posts
    11
    I think my batch file acts as the "stub app", no? The only thing my batch file does not do is check for file version. If I added that functionality to the batch file, it would essentially be what you guys suggested right?

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by jadcock
    I think my batch file acts as the "stub app", no? The only thing my batch file does not do is check for file version. If I added that functionality to the batch file, it would essentially be what you guys suggested right?
    And how are you going to check for the version? You can't use timestamp ... It would need to be something internal to Access ...
    Back to Access ... ADO is not the way to go for speed ...

  9. #9
    Join Date
    Feb 2004
    Posts
    90
    He could set up the batch file on the server, so that everyone opens clicks the one batch file (on the server) to open their local front end.

    The batch file could test for a a text file on the local PCs C drive. I.E. look for a file called 2.txt. If it doesn't exist it copies the new version of the front end and renames the existing 1.txt held locally to 2.txt

    Code:
    If 2.txt exists then
    open database frontend
    else
    copy new frontend to local PC
    rename local 1.txt to 2.txt
    open database frontend
    end if
    I'm not sure of the batch file code that would be needed, but I know it's possible.

    Then if there is a new version all you need to do is edit the batch file to look for 3.txt.

    The way I do it is my front end databses check their version number (held in a local table) on start up and compare it the the latest version number (held in a linked table to the backend db). If there is a new version then the database exits, and opens a VB exe file (I used to use a batch file) held on the server that copies the new version to the local PC.

  10. #10
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Goldy
    He could set up the batch file on the server, so that everyone opens clicks the one batch file (on the server) to open their local front end.

    The batch file could test for a a text file on the local PCs C drive. I.E. look for a file called 2.txt. If it doesn't exist it copies the new version of the front end and renames the existing 1.txt held locally to 2.txt

    Code:
    If 2.txt exists then
    open database frontend
    else
    copy new frontend to local PC
    rename local 1.txt to 2.txt
    open database frontend
    end if
    I'm not sure of the batch file code that would be needed, but I know it's possible.

    Then if there is a new version all you need to do is edit the batch file to look for 3.txt.

    The way I do it is my front end databses check their version number (held in a local table) on start up and compare it the the latest version number (held in a linked table to the backend db). If there is a new version then the database exits, and opens a VB exe file (I used to use a batch file) held on the server that copies the new version to the local PC.
    Well here's an idea that Izy floated a while back ... Make a stub FE Access DB that does the version check (contained in the Master FE on the network compared to the FE on the client) and updates/replaces the FE as required then launches it and kills itself ... The user clicks on the same shortcut everytime and does not worry about if the version is up to date ...
    Back to Access ... ADO is not the way to go for speed ...

  11. #11
    Join Date
    Feb 2004
    Posts
    90
    Yeah, that's probably the best method (I think you already mentioned it), but I was just showing a solution that incorporated using a batch file.

  12. #12
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Goldy
    Yeah, that's probably the best method (I think you already mentioned it), but I was just showing a solution that incorporated using a batch file.
    My problem/the problem with batch is that there's no direct correlation of the file to the DB ... Yes, yes ... You can SAY there is one because everyone has the best intentions but, at some point someone will forget to update that flat version file and then things will get gummed up ...
    Back to Access ... ADO is not the way to go for speed ...

  13. #13
    Join Date
    Feb 2004
    Posts
    90
    I agree with you 100%.

    Using a batch file for this sort of thing is far from ideal.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •