Possible to send out a "patch" or better way of updating databases for multiple users?

[Griffin410]

This is my first post here so hope this is the right place! I maintain a simple Access database which about 30 other users have open every day and use it solely as a reference book. It is simply a large reference 'toolbox' with one form and many many sub reports in the form with links to refference guides for my users. Very often I need to add new reports and just new content overall to the database. My current process is to 1) make a copy 2) modify the copy with all my new content 3) Send and email with the file attached telling all 30 users to delete their old database and use this updated version.



Of course we have many issues including:
1) The files are very large in Lotus notes (is there a way to make these files a lot smaller)
2) Seems like a waste to delete and replace an entire database when only 1 or 2 minor links need to be changed every time. Is there a macro or some code I can create that would act as a patch where the 30 users can run the code and it will change hyperlink paths that I needed changed, report content or anything that needs changing.

Looking for any solution you can think would be better that gets around having all my users copy and replacing their database toolbox on their desktop. We all work out of a shared network drive so I can have them use the database together in a shared drive but its litterally impossible to have every hand out of the database all at once so I can go in and update, it just does not happen so if there is a work around for getting all users out of the DB, it could avoid having to do anything else.

[JoeM]

So all users have their own complete copy of the database? They don't need to have access to some data?
Usually, when you have multiple users using the same database, it is recommended that you "Split" the database.
In a Split database, there is a Back-End database on the network with all the data Tables, and then a Front-End database that has all the Form, Queries, Reports, etc that each user is given their own copy of that they run from their computer.

Most of your updates will just be to the Front-End (unless you were adding Tables or changing the structure of existing tables).

What I do to easily distribute updates to the Front-End is the following:
- Put a copy of the current Front-End database out there on the network
- Put a shortcut on user's desktops that copies the Front-End off of the network to their desktop and opens the Access database

So they use this shortcut to open their database. Every time they do that, it copies down a new, fresh copy of the Front-End. So to push out updates, all you have to do is overwrite the Front-End copy you have out on the network that the batch file copies.

[John_G]

First, since it is a shared database, you really should be using a split database, in which the data resides on the shared drive, but each user has a copy of the front end (reports, forms, queries, etc.) on their own hard drive. By doind that, you would only have to send out updates if you made modifications to the FE.

Updating the data should not be an issue either - as long as you are only making updates to the data, and not to the structure, you do not need exclusive access to the database. If you have to make structural changes (new tables, fields, etc), then yes, you do need exclusive access. That is not an unusual occurrance though - there are many times when a database has to be "down for maintenance".

[Griffin410]

So all users have their own complete copy of the database? They don't need to have access to some data?
Usually, when you have multiple users using the same database, it is recommended that you "Split" the database.
In a Split database, there is a Back-End database on the network with all the data Tables, and then a Front-End database that has all the Form, Queries, Reports, etc that each user is given their own copy of that they run from their computer.

Most of your updates will just be to the Front-End (unless you were adding Tables or changing the structure of existing tables).

What I do to easily distribute updates to the Front-End is the following:
- Put a copy of the current Front-End database out there on the network
- Put a shortcut on user's desktops that copies the Front-End off of the network to their desktop and opens the Access database

So they use this shortcut to open their database. Every time they do that, it copies down a new, fresh copy of the Front-End. So to push out updates, all you have to do is overwrite the Front-End copy you have out on the network that the batch file copies.

Hi Joe,

To answer your first question, yes each user has a copy of the database. There is no data entry or logging of any data so I do not need them to remit anything back to me. I honestly believe this database would be much better served as a website as its just a bunch of links and blocks of text for my auditors to reference but for now we are keeping with Access.

Your methodology looks great with the idea that the shortcut creates a copy of the database each time. If I understand correctly, by doing this desktop shortcut, it would be just like going into the shared drive with the front end database and manually copy/paste that DB to their desktop and opening that copy (which in turn allows me to have free access to the database in the shared folder to do my edits at any time). My question would just be how do you make this shortcut given simple desktop shortcuts are just opening the database in the shared drive. Sorry if this is common knowledge but I have never seen shortcuts that actually make copies of the file instead of just opening the file the shortcut links to.

[Griffin410]

Hi John,

Thank you for the reply and advice. Per both of your feedback I will be splitting the two databases FE and BE. Just need to find a better solution for FE maitnence now.

[JoeM]

The batch file would look something like this:

echo off

:: Delete old database
del "C:\Databases\MyDB_FE.accdb" /q

:: Copy Front-End from network to desktop
copy "\\server01\Databases\FrontEnds\MyDB_FE.accdb" "C:\Databases\MyDB_FE.accdb"

:: Open database from desktop
"C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE" "C:\Databases\MyDB_FE.accdb"

EXIT

So, just substitute your file paths and database names and save with a "BAT extension. Then just click on the file to run it.
I usually put the batch file on the network, and just put a shortcut to it on people's desktops.

Regarding the script, note that in copying the Front-End from the network, you can use UNC naming convention like I am showing, or you can use mapped drives (but you will only want to do that if ALL of your users have that exact drive mapping).

Also, notice the step that opens the database. Your path might differ slightly. You need to find the path of where the executable that runs access exists. It should be something similar, like:
"C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE"
"C:\Program Files (x86)\Microsoft Office\Office12\MSACCESS.EXE"
"C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE"
"C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE"
...

[Griffin410]

The batch file worked great. My only question/issue I am having is the command prompt box stays open after the database opens. the command box will close automatically when the database closes or can be manually closed, but is there a way to have it closed automatically or be able to turn invisible so the prompt never shows up as its own window.

[Rawb]

If you add the word "call" to the beginning of the command, the command prompt should close as soon as Access starts

echo off

:: Delete old database
del "C:\Databases\MyDB_FE.accdb" /q

:: Copy Front-End from network to desktop
copy "\\server01\Databases\FrontEnds\MyDB_FE.accdb" "C:\Databases\MyDB_FE.accdb"

:: Open database from desktop
call "C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE" "C:\Databases\MyDB_FE.accdb"

EXIT

[John_G]

Another option would be to have the batch file copy a shortcut icon to the desktop, and have the users open the database with the desktop icon. Copying the database from the network each time isn't necessary - it isn't updated that frequently.

[JoeM]

Copying the database from the network each time isn't necessary - it isn't updated that frequently.

Agreed. But we have found it beneficial, especially if people start messing with the database or on the off chance there is any corruption issue. They get a "fresh" copy each time, without anyone having to do anything special.

[Griffin410]

Thank you for the reply. Tried using "call" and "Start" (Start function was blocked by administrator and cant get around that) but the command prompt still stays open. Here is my code

echo off


:: Delete old database
del "MYdb.accdb" /q


:: Copy Front-End from network to desktop
copy "[FILE PATH]\MYdb.accdb"" "MYdb.accdb"


:: Open database from desktop
call "MYdb.accdb""


exit

The database and batch file are both on the desktop so all the file paths work just fine (I do not want to use the full desktop file path because each user has a unique file path to desktop and the batch file would then not be universal). The command prompt displays

"....users\desktop>echo off
1 file(s) copied

Any other advice to try to get rid of command prompt?

[Perceptus]

you could use %USERPROFILE%\Desktop to get to each users desktop without having to make a custom script for each. If you dont want a window you could try something like this in your batch file.

Also you could use a vbs file instead. and execute the vbs file using cscript /nologo.