Deploying a database file for users

Posted on 2011-10-26
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
    LVL 7

    Expert Comment

    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

    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.
    LVL 7

    Expert Comment

    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
    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

    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

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    If like me you are one who spends a lot of time working and scripting with cmd.exe, sometimes it is handy to be able to quickly view a calendar for a given month and year. This script will quickly do just that!  Save the code posted below to a .bat …
    Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
    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…
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    730 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now