Allowing multiple simultaneuos connections to a MS Access .ADE file.
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
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
<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.
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.
ASKER
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.
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.
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.
xcopy "source" "destination"
Build a .bat (i.e. Batch) file that does this, and schedule that file to run whenver you'd like.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
In this case 'good practice' is a side issue.
ADPs are by their nature single user files. They cannot be shared.
ADPs are by their nature single user files. They cannot be shared.
ASKER
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%\DB
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.
\\fileserver\%USERNAME%\DB
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.
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\DBfo
http://support.microsoft.com/kb/41246
\\fileserver\Scotts64\DBfo
http://support.microsoft.com/kb/41246
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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\Datab
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.
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\Datab
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.
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.