Automatic Front End Database Updating

[ghudson]

HTH = Hope This Helps

 

[Keith Nichols]

If you read my posts Keith you should get the basic idea of how to accomplish this. Use notepad to create the .bat file.... when you go to save the file go to save as: and save it like such: Update.bat or whatever you want the file to be named.

Gold07eye,

Thanks, I think that is the last peice of the jigsaw. I now have to rummage about and put it all together but I can see that I should be able to get it together.

Many thanks one and all - A very useful thread.

GHudson,

Thanks for the heads up re "HTH".

 

[AndersBager]

Hi all! I solved this by having a small VB application installed as a basic application for all users. This application opens a form with up to 20 programs or access applications. It presents every application with a label and a button. If you press the button it checks if there is a file on the server (=the new front end) and then checks if it can be found on the local PC (in a special folder that I named "Local documents". If it is not found or if the time stamp of the local file is not the same as the server version I download the server version of the file and then I start it. On the server I have a small access database containing the information that I need for the form with the buttons. This db-file is situated in a server folder. In this server folder I have three sub folders: database (to keep the backends), development (where I keep all the versions of the front ends that I work with and a production folder where I keep the version that I want to have distributed.

I have been using this for an association with about 300 PCs and it has been working well for more than 5 years now! Whenever I have a new verison I only move a copy from the development folder to the production folder and then I ask the user to exit from the application and restart it so they get the new version!

The reason I didn't make this "switchboard" application in Access was that I don't want to make new versions for every upgrade of the office package! The program is only about 120 lines of VB code so it is very simple to write.

Hope this can be of some help

Anders

 

[gold007eye]

Hi all! I solved this by having a small VB application installed as a basic application for all users.

Anders do you have a version that you could share with me so that I could take a look at it? It might make my life a lot easier. Thanks for the info.

 

[Keith Nichols]

Batch file problems - would .cmd be better?

Hi Guys,

I have this all nicely pieced together apart from the last little step. I cannot get the batch file to copy the current version from the network to the user PC or start Access with the WIF & User settings of the shortcut.

Messing around with this, I created some test text files to delete and copy on the network and PC. The delete works fine at both ends, so the network addressing is right. However, the copy doesn't happen and I was getting a syntax error when it ran. The DOS Help wasn't much use to me I'm afraid.

The final problem I had was starting Access again. When I tried to use the details from the shortcut, including the WIF & User information, Access would not start but when I just used the "MSAccess.EXE" it started fine.

@Echo on

C:
CD\
CD EDT DB\
DEL TESTa.TXT
@Echo Test file 'a' has been deleted from PC
Pause

T:
CD\
CD Master_FE\
DEL TESTb.TXT
@Echo Test file 'b' has been deleted from network
Pause

COPY TESTc.TXT C:\EDT DB\
@Echo Test file 'c' has not been copied
Pause

C:
cd\
Start "C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" /wrkgrp "C:\EDT DB\ED.00.23.001_WIF.mdw" "C:\EDT DB\ED0023001FE.mdb"  /user Custodian1
@Echo Access does not start
Pause

Can anybody see the syntax error(s)?

Bob L stated that he used a .cmd file as he was using Windows XP. We have Windows XP here and so I was wondering if there is any advantage to doing the same? Is there any significant difference in the approaches?

Finally, as I have been documenting my application, I have prepared a Viso flow chart showing the various utilities running with my database, namely:

Check version is the current version, replace with new if old
Log out all users from the back end
Prevent front end openning if log out all users is checked
Close database when idle for 15 minutes

This may be useful in explaining the process and concepts of these functions. Also, if anybody thinks the organization of my database may be improved upon, I would be grateful for the heads up.

I have attached a .pdf of the diagram but will happily hand over a native format copy for any who may want it.

Regards,

 

[Keith Nichols]

Still struggling here

@Echo on

C:
CD\
CD EDT DB\
DEL TESTa.TXT
@Echo Test file 'a' has been deleted from PC
Pause

T:
CD\
CD Master_FE\
DEL TESTb.TXT
@Echo Test file 'b' has been deleted from network
Pause

COPY TESTc.TXT C:\EDT DB\
@Echo Test file 'c' has not been copied
Pause

C:
cd\
Start "C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" /wrkgrp "C:\EDT DB\ED.00.23.001_WIF.mdw" "C:\EDT DB\ED0023001FE.mdb"  /user Custodian1
@Echo Access does not start
Pause

Hi Guys,

I have messed around with this off and on over the past couple of weeks and cannot get any of the versions offered by BL, KEK or GoldEye007 to work over my network. Maybe there is something to do with my network?

Anyway, I get syntax errors on the copy commands. Anybody any ideas?

I managed to get Access to start my application ok with one version of the batch file, but I could not get it to use the command line switches for the WIF & setting the user. My workaround here is to close off the batch file with a "You may now restart the database" message. It would be nice to get the database to open as it would normally.

Just thinking about this last point, maybe I can direct the start command to the shortcut rather than Access. Might work.

I'd appreciate any help given.

Regards

 

[Keith Nichols]

Partial success using mdb

I would advise not using batch files for you can do what you want with an mdb file. But the choice is yours. The batch file will not work if the user has the db that you want to replace already opened.

FileCopy ("\\Server\Partition\Directory\File.lnk"), ("C:\Documents and Settings\All Users\YourDB\YourShortcut.lnk")

.

Hi Folks,
As I was getting nowhere fast with batch files, I have created a little utilities mdb as suggested by GHudson. This is working very well for the copying etc. Many thanks.

I cannot work out how to call or run the Utilities.mdb from the VBA code. Can anyone give me a pointer?

Regards,

 

[gold007eye]

I think the problem in your bat file is the following line:

Start "C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" /wrkgrp "C:\EDT DB\ED.00.23.001_WIF.mdw" "C:\EDT DB\ED0023001FE.mdb"  /user Custodian1

Try using that line without the

/wrkgrp "C:\EDT DB\ED.00.23.001_WIF.mdw"
/user Custodian1

That should give you a general idea as to if that is the problem.

To call up another database within Access I used a Macro, but there may be a command such as "RunApp" within VB that you can use to do the same thing. I couldn't figure it out so I used the Macro function.

Here is the way my macro is set up.

Action:

RunApp
(Command Line) C:\Program Files\Microsoft Office\Office11\MSACCESS.EXE "Location of Update MDB Goes Here"

 

[Keith Nichols]

Thanks Gold007eye

Gold007eye,

I had the macro set up for running the batch file so I can edit that easily to run the utilities database.

Before I abandoned it, I ended up closing the batch file with a message to tell the user that they can restart the application now. This philosophy has been caried over into the utilities DB created in place of the batch file.

An added advantage of this method is that I do not have to get the command line right for the different versions of Access running in my user group.

Regards,

 

[workmad3]

I use a simple batch file for updating my db over the network. I just tell the office to run the update when I update. One thing though, I don't see why everyone deletes the DB first. Why not just use copy /Y which does an automatic overwrite without user input?

 

[Keith Nichols]

I use a simple batch file for updating my db over the network. I just tell the office to run the update when I update. One thing though, I don't see why everyone deletes the DB first. Why not just use copy /Y which does an automatic overwrite without user input?

Hi Workmad,

The advantage of the fully automated process is that whenever a user opens the front end databse on their PC, the version check is performed. Out of date front ends then delete and update.

This works well for multi user environments as various people have their offices locked when they are out of office for whatever reason. 3 weeks later when they return from leave, the front end updates - no problems - no call to me with "The database won't run" and my attempting to establish a version number etc.

The overwrite verses delete and save is a fair question. But it is not a whole hill of beans more work so why not break it down? Also, depending on what is in the batch file (or utility mdb) there is no additional input from the user

As previously mentioned in earlier posts, I couldn't get the batch file to do the copy over the network (it worked fine on my dedicated drives). With GHudson's suggestion and Gold007eye's help, the Utilities mdb works a treat. Respect & thanks gents.

Regards,

Keith.

 

[gold007eye]

I am glad this has helped you. This method has definitely made it easier for me to complete updates without having to worry about people still in the database locking it from being modified, etc.

The only thing left would be if I could figure out how to have the update utility look at the newest version in the "Version" table so I could keep a list of Version numbers and the date and time they were created. Right now I have to rename the old version number to the new version number for the update method to work correctly. I tried to keep all of the versions numbers in the table, but it kept looking at the 1st record instead of the last record for it's information.

Anyone have some ideas on how to work around this? Being able to keep the logs of the versions would be great for Audit purposes.

 

[Keith Nichols]

One minor niggle: macro v code

To get the Utility mdb working, I use a macro to call it from the database requiring an update before it shuts itself down. This works well on my single user (me) test.

However, the larger user group have different versions of Access running and these different versions of access use a different file location. So, to cater to all needs, I want to determine the version of access running and then call the Utilities database using the relevant path.

I suppose I could do this by having 2 macros with an If/Then check to call the right one, but it should be able to be done in the VBA without using any macros at all? Any ideas?

I would use this to determne the Access version running:

AccessVer = SysCmd(acSysCmdAccessVer)

Regards,

Keith.

 

[pbaldy]

My utility does exactly that (it was originally an mde, but now I've written in in VB so it's an exe). I have a version table in the front end, and my utility opens a recordset on both the local version and the server version, using an SQL statement that grabs the Max version number. I compare the 2; if different, copy the version from the server, then either way it opens the local copy. Requires nothing from the user; they don't even know they actually started a program different from the one they intended.

 

[Keith Nichols]

I am glad this has helped you. This method has definitely made it easier for me to complete updates without having to worry about people still in the database locking it from being modified, etc.

The only thing left would be if I could figure out how to have the update utility look at the newest version in the "Version" table so I could keep a list of Version numbers and the date and time they were created. Right now I have to rename the old version number to the new version number for the update method to work correctly. I tried to keep all of the versions numbers in the table, but it kept looking at the 1st record instead of the last record for it's information.

Anyone have some ideas on how to work around this? Being able to keep the logs of the versions would be great for Audit purposes.

Hi Gold007eye,

A couple of points:

1) I have a "kick out all users function" that I could initiate if I needed to do something on a fairly urgent basis. 15 minutes after going "thermonuclear" I can change the version number in the back end and the "old" front end won't work. If a user tries to run the ap, it won't operate until I have saved the new front end to the network location so that their old front end and utility DB can do the update.

2) I think it was Bob Larson who suggested that the version table should have only 1 record and this is what I do. If this is what you mean by "I have to rename the old version number to the new version number" then I have to do that too.

3) If you want to record the version history of the Front End, why not create a separate History table. The version number could be updated on a form, and a subform could take the new version number to the History table which would have fields for comments & update dates etc. which had to be completed before the record would save. Without having thought about this much (If at all!), it doesn't sound too complicated. It might be easiest if the History table had the version number manually input and then there are no problems with the re-numbered version number.

This sounds a little clunky, but only administrators would be doing it and so they should know their way around the db?

Regards,

Keith.

 

[Keith Nichols]

Shell starts Access ok but I can't get it to run my mdb

Hi Guys,

The latest version of this has done away with the macros.

On detecting an out of date FE, a syscmd is used to determine the Access version and a form displays this data along with 2 "update" buttons. 1 for A2k and the other for A2003.

The on click of these buttons runs code with a shell comand. I can get this to open/run Access ok but I am having difficulty getting it to run the utilities mdb. If I use the quotation marks to enclose the path of the target I get an error and if I don't, I get a cannot find file error showing only the first part of the path to the target with ".mdb" tacked on the end.

I'm sure it is a simple syntax thing but it has me flumoxed.

Private Sub cmd_Update_A2003_Click()
On Error GoTo Err_Handler

' call the Utility database and close the Project Tracking database

Dim Updatemdb As String
Updatemdb = Shell("C:\Program Files\Microsoft Office\Office11\msaccess.exe C:\EDT DB\FE_Update.mdb", 1)     ' Run Access

DoCmd.Quit

Exit_Here:
    Exit Sub

Err_Handler:
    MsgBox "Error detected"
    Resume Exit_Here
End Sub

 

[MaliciousMike]

Just wanna say thanks for the help with this.

I've set it up and it's working fine if the user has Office 2003 (Office11), as the database uses Excel for data exports, Word for mailmerging and Outlook for emailing.

If the user doesn't have the same version, the VB references complain bitterly and make my administration life hell.

So now i've got my program to automatically update itself.
All i need now is for it to either update the references or simply get rid of them.

We have a call centre full of people who aren't very computer literate, so teaching them to change the references is a complete lost cause. I'm a busy person and can't do it myself so it needs to be automatic.
Any ideas?

 

[ebodin]

For a different approach which is easier and works well in the right situation:

I put my FE on the network and users access it through a shortcut that I send to them. When I update the FE I give it the same name as the old one so that their shortcut is still valid. There is also a link from our intranet webpage that launches the FE that is on the network drive.

This works well for me, BUT, the DB I've created is accessed read only by the users and there are not more than 30 people who would use it at a time. Otherwise the DB would probably get corrupt.

 

[MaliciousMike]

I put my FE on the network and users access it through a shortcut that I send to them.

Although it's a read only database for your users, this can still create a masive amount of server load with roughly 25 users sending queries back and forth. It can also corrupt the database (although i'm not sure if it would as Read-only).

I strongly suggest reading through this thread and creating an auto updating system.

 

[cdoyle]

Hi,
does anyone have a working example that we could take a look at. I've been reading through this thread, and see code revisions but not sure how it all fits together.

Would it be possible for someone to post a sample DB?