Deploying a database file for users

Posted on 2011-10-26
Medium Priority
Last Modified: 2012-05-12
Here's the situation,
There is a database (Microsoft Access) file that users use to access a SQL back end.

The users generally have this file on their desktops of their computers.  Some users use a RDP (Terminal) server.  

Unfortunately, each user has to have his own file (we can't link all users to the same file), so the same file resides on 100 users desktops, terminal sessions, etc.

Periodically, we need to update the file when a new database version is released.  When this happens, we have a couple of batch scripts to copy the new file and overwrite the existing one.

Was wondering if anyone had any suggestions as far as ways to improve this.  Right now we have to create a new batch file for every user (and then have a master batch file that calls all the sub bat files).
Question by:Mystical_Ice
  • 2
  • 2

Expert Comment

ID: 37035611
Well to be able to recommend a better way of doing it, let's see where your current pain points are.  It seems like your environment already has this automated.  Does it fail?  Does it sometimes not update some but update others?  Is it still a manual process?  Do users need to 'merge' data?  

We know what you want to improve, but based on 'how' you want to improve it, better suggestions could be given.  For example, SSIS should be able to be used and you only need 1 SQL Server Standard edition to do it, or you could use PowerShell perhaps.  It depends on the pain point.  Let us know, thanks!

Author Comment

ID: 37037608
Well terminal server users we require to log off before we run a script that copies the file to their desktops (overwriting the existing one).

Other 'desktop' users we require to run a script that's on their desktop.  The script is supposed to pull the new version of the file, overwriting the other.

What's happening is sometimes they're not running it, or somehow the file isn't being copied.

Expert Comment

ID: 37039322
Typically in cases like this, making a MSI file and pushing it out from M.O.M or M.O.S.S is a great way to make sure A.D. takes care of it for you as part of group policies, this is a much more concrete way of doing things.  The problem with using a tool like SSIS and such is that you can't guarantee the end user will pull it down when they log on, with a MSI file being pushed out via AD policy, the domain takes care of it for you as part of it's log on policies.  PowerShell might give you some functionality too but I'm not sure what the limitations are of PS yet.  

Pushing MSI packages with AD Group Policies is probably the most concrete way of doing it in a Windows Domain for this scenario
LVL 43

Expert Comment

by:Steve Knight
ID: 37087144
Got a suggestion for you.... can you amend the Access db, or is it's content fixed by a vendor?

In the access VBA code on startup... lets put some code in that checks a network location for a version number file.  If it is found to be later than this version then it tells the person to say "yes" to update, closes itself, sets off copy to get new version and re-opens?

LVL 43

Accepted Solution

Steve Knight earned 2000 total points
ID: 37087148
Another alternative....

Have your access code query your SQL tables for a "required version number".  If it is lower than needed then again close and refresh the access db from network drive.

Maybe here it would be Ok for V1.5 but there is a V2 available but V1.0 must not use, in which case the logic would be:

Get "Must have at least" version number
Get "Latest" version number
if my version number < "latest" version number then
   if version number > "must have" version number
     Tell user there is new version but what they have is OK for now but please upgrade... maybe offer on exit?
     Say "you must update now.... ", close and trigger an update

Another option would be to do this in a wrapper, i.e. they click on an icon which runs a VBScript or batch file which then checks a network file for version number and either launches Access or sets off the copy...

Can soon look at your access db shell if needed.


Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You may have already been in the need to update a whole folder stucture using a script. Robocopy does it well and even provides a list of non-updated files in a log (if asked to). Generally those files that were locked by a user or a process by the …
Recently, an awarded photographer, Selina De Maeyer (http://www.selinademaeyer.com/), completed a photo shoot of a beautiful event (http://www.sintjacobantwerpen.be/verslag-en-fotoreportage-van-de-sacramentsprocessie-door-antwerpen#thumbnails) in An…
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Suggested Courses
Course of the Month15 days, 12 hours left to enroll

850 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