We help IT Professionals succeed at work.

Allowing multiple simultaneuos connections to a MS Access .ADE file.

Medium Priority
706 Views
Last Modified: 2012-05-14
We have many in-house databases that have have an SQL 2008 back end, and a .ADE MS Access front end.  Users have been using these for years on their desktop PCs, where the ADE file is copied to their hard drives.

We have users now moving over to a Citrix XenApp 6 thin client environment, where their user folders are redirected to their personal network share.  Although these ADE files were originally created on Access 2003, it is Access Runtime 2010 that I have installed on the XenApp servers, so the ADE files have been modified to work on Access Runtime 2010.

Rather than keeping with the current method, we don't want these ADE files to be copied to the users personal network shares - because this would mean dozens of copies of the same file filling up our file servers.  So I set up a system where the ADE files are all kept in one central network share, and then group policy drops a shortcut to these files on the users' desktops.  This works well, and is quick, if there is only one user using the ADE file.  But from my tests it doesn't look like multiple users can connect to them simultaneuosly.  As far as I can see, the reason for this limitation comes from the fact that the ADE file is opened by Access in "writable" mode.  Access does this just to update the timestamp on the file.

Is there a way that the ADE file can be modified to allow multiple users to connect at the same time?  I guess one way would be to try to stop Access from wanting to update the timestamp of the file, but I don't know if this is possible.

Any suggestions would be welcome.

Thanks
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
If I'm reading this right, what you are trying to do is effectively moving away from what is commonly considered 'best practice' with each user having his/her own copy of the front end to all users sharing a single front end (whic is generally not recommended for a variety of reasons).

If you have to get rid of users' individual front-ends for whatever reason (I'm not a networking Expert, so don't fully understand the reason for this), an alternate approach would be to make the front-end web-based - possibly in .Net.
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
<no points wanted>

I agree with mbizup - your old method of copying those files to the user's desktop should still be used in the Citrix environment. Citrix allows you to run scripts on startup which would effectively eliminate any sort of hands-on work involved in this - just run a batch file that copies over a new .ade file each time the user fires up that .ADE file.
meirionwylltSenior Desktop Engineer

Author

Commented:
Thanks for your answers.  Not what I wanted to hear, but there's no point in contravening microsoft's best practices just to save a couple of GBs on the file server.

On the desktop PCs we currently have a VBS script that runs on every logon, checks the user's group membership, and checks if there are any new versions of these files (they get updated regularly), and then downloads the relevant ADE files.

As for thin clients, I want to move away from a logon-based approach to save on logon processing.  Rather, I was thinking of some sort of batch/script file that runs during the night, and copies the ADE from the source, and overwrites it on the personal network share of every user in a certain group.  I would have a similar job running for every such database (there are about 30 of them).

Can you suggest anything (tools or scripts) that would help me accomplish this?

Thanks.
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
If you can use standard Batch files, just do this:

xcopy "source" "destination"

Build a .bat (i.e. Batch) file that does this, and schedule that file to run whenver you'd like.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013
Commented:
Another approach to automatic updating can be done within the database itself...

Have a tblVersion on the server in the shared Back-End, and a tblVersionLocal as a local table in the users' front ends.

tblVersion (the shared one) holds the version number of the latest version of the Front End.

tblVersionLocal (in each front end) holds the version of that particular front-end.

VBA in the open event of a Startup form (or some similar event that always runs when the database opens can be used to cross-check the local version number with the 'latest' version number as seen in the shared back-end.  A mis-match would trigger an automatic update, which would overwrite the user's local front-end with the newest one, containing the latest version number.

If that sounds reasonable, there are solutions based on this approach available for download if you search for "Access automatic front end updater" or something along those lines.
CERTIFIED EXPERT

Commented:
In this case 'good practice' is a side issue.

ADPs are by their nature single user files.  They cannot be shared.
meirionwylltSenior Desktop Engineer

Author

Commented:
LSMConsulting - a standard batch file approach would be good for me, but the problem here is the "destination" part.  This is not just one destination - there is a "destination" for everyone that uses each database.  OK, so as not to confuse matters, I will concentrate on just one database for now.  This database has a group (called CTX DB1) containing all the thin client users who have access to this database.  So, the "destination" in your command above is actually...

\\fileserver\%USERNAME%\DBfolder

Where the USERNAME variable is populated by membership of the group.  Any idea how I would do this?

mbizup - Great idea.  I will pass this info on to our Access developers to see if this is something they can incorporate.  Thanks.

peter57r - We are actually using ADE files not ADP.  I don't know if this fact changes your opinion on the matter.
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Batchfiles can interpret Windows variables, using the syntax you are using now (the % sign denotes the variable). So if you run that on my machine, you'd return a path of

\\fileserver\Scotts64\DBfolder

http://support.microsoft.com/kb/41246
meirionwylltSenior Desktop Engineer

Author

Commented:
I realise this, but I was wanting a similar solution that, instead of checking Windows variables, would check Active Directory and the contents of a specific group, and then run the command for every username in that group.

I can not use Windows variables here because I will be running all the commands from one server at a specific time, and not with a login script.
Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
You can query AD with VB Script, or you could write a small program which will do that. There are also some 3rd party components that work with Citrix and provide you with this capability.

This post from SO might help: http://stackoverflow.com/questions/1090336/querying-active-directory-using-vbscript

I was suggesting you approach this from the other end - when the USER logs in, you'd run the batch file and copy the new .ADE file down to their Citrix desktop.
meirionwylltSenior Desktop Engineer

Author

Commented:
Thanks for that link - it has loads of VB examples!

But I like your idea of the user opening a batch file which copies the ADE file down.  For each DB, I could have a batch file sitting on a network share in the same folder as the ADE file, the first line being

if not exist K:\Databases\DB1.ade copy \\domain.local\share\Databases\db1.ade K:\Databases

But I will use xcopy instead of the normal copy command so that it can check the date modified.  This is what I will do.  Thanks for your help.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.