Remote Desktop setup (Access 2003)

Topic

New Reply

I have a client who is using remote desktop for workers from three different sites to all access the same database. There will ultimately be about 5-8 users of this database. We did a test setup for me where a shortcut for the the front end of the database is in C:Documents and SettingsAll UsersStart MenuProgramsStartupABC Applications where ABC is the name of the agency.

The front end itself resides at C:ABC DatabaseABC_FE.mdb

The backend is on a network drive that is mapped to W:ABC DataABC_BE.mdb

If I log on with my own identity, support1, I have absolutely no trouble with the database. Other users were getting the following error messages:
1) The typical security/unsafe macro warning. – I have solved this by lowering the security setting to low. Something in the back of my mind tells me there is a better way to do this.

2) “ABC_FE.mdb is Read Only”. I checked into folder permissions and it seems the users didn’t have the same settings as me so I have asked the network admin to make sure both frontend and backend folders have full permissions for all users

3) “Database is open exclusively by another user” or “database locked” My guess is that this is happening because everyone’s shortcut is pointing to the same copy of the front end C:ABC DatabaseABC_FE.mdb

Could someone confirm this and give me a better idea of how this stuff should be set up? The network admin knows her stuff but is as green with Remote Desktop as I am. After our conversation and testing today, I’m quite certain that the problems are on her side, not the database itself. I know what to do over a LAN, I just haven’t got a clue with this stuff and neither does she.

Reply | Quote

Replies

Even with remote access, you should give each user an individual copy of the frontend. This will reduce the probability of problems.

Reply | Quote

Even with remote access, you should give each user an individual copy of the frontend. This will reduce the probability of problems.

I understand that part. That is what I suspected and tried to convey to the network admin.

Can you give me a hypothetical setup for what that might look like? For example, if I had a hidden folder for the frontend on each user’s startup desktop and put ABC_FE in that folder and had the shortcut point to it, would that solve the problem? Or can you suggest another scenario?

Reply | Quote

I have no experience with this kind of setup myself; I hope that WendellB will jump in – he uses remote access frequently so he should be able to give you relevant suggestions.

Reply | Quote

… Can you give me a hypothetical setup for what that might look like? For example, if I had a hidden folder for the frontend on each user’s startup desktop and put ABC_FE in that folder and had the shortcut point to it, would that solve the problem? Or can you suggest another scenario?

Sorry to be late to the party – got tied up in a UNION query with 25 queries as it’s data source – UGLY! Anyhow, I presume you are running this on a Terminal Services or Citrix server where all three users can be connected to the database at the same time, and all are being authenticated as a unique user rather than using a common login. If that’s not the case we need to talk.

Also you didn’t indicate what version of Access you are using. 2000/2/3 are more forgiving in that environment than 2007 – with it you have to make sure each user has explicit Read/Write permissions to the front-end database as well as the folder where the back-end resides, and in 2007 you need to set the locations as Trusted. Also, if you are running Windows 2008 Server, it seems to be more picky about the user permissions than 2003 or earlier versions. But Hans is correct – each user should have their own copy of the front-end, stored either on their desktop of the server account, or somewhere in their documents folder on the server. That does create a maintenance headache for you if you have to make changes, as you will need to be able to update their folders. And it really doesn’t matter where you put the front-end, as long as the user has full permissions to that folder and the file. Hope this helps – if you have more questions post back.

Reply | Quote

Sorry to be late to the party – got tied up in a UNION query with 25 queries as it’s data source – UGLY! Anyhow, I presume you are running this on a Terminal Services or Citrix server where all three users can be connected to the database at the same time, and all are being authenticated as a unique user rather than using a common login. If that’s not the case we need to talk.

Also you didn’t indicate what version of Access you are using. 2000/2/3 are more forgiving in that environment than 2007 – with it you have to make sure each user has explicit Read/Write permissions to the front-end database as well as the folder where the back-end resides, and in 2007 you need to set the locations as Trusted. Also, if you are running Windows 2008 Server, it seems to be more picky about the user permissions than 2003 or earlier versions. But Hans is correct – each user should have their own copy of the front-end, stored either on their desktop of the server account, or somewhere in their documents folder on the server. That does create a maintenance headache for you if you have to make changes, as you will need to be able to update their folders. And it really doesn’t matter where you put the front-end, as long as the user has full permissions to that folder and the file. Hope this helps – if you have more questions post back.

Hi Wendell

Thanks for joining in. I was hoping you would since I know (as does Hans!) that you do most of your work this way. I’m 90% sure it is Remote Desktop via Terminal Services. I remember some sort of message on logout that mentions terminal services. I know everyone has their own login ID. I believe the big problem, #3 on my list, was caused by everyone trying to access the same copy which was an error in understanding Remote Desktop on my part. I have already asked the Network Admin to change that so everyone has their copy in their own My Documents folder.

It is Access 2003 (which I indicated in the title!). I don’t offhand know whether the server is 2008 or 2003. The interface looks more old-fashioned. I suspect 2003, but it could even be 2000.

I already spent an hour on the phone yesterday working on the permissions issue so hopefully that is resolved.

Do you have any comment on my question 1 about the security warning?

I will pass the link to this thread to the network admin so she can read it directly.

Reply | Quote

… It is Access 2003 (which I indicated in the title!). I don’t offhand know whether the server is 2008 or 2003. The interface looks more old-fashioned. I suspect 2003, but it could even be 2000. …

Oops – I should read the title! In any event, the key to understanding Terminal Services and Remote Desktop is that each user gets their own workspace and their own installation of Office Apps. So the first time a user starts Access, they get a message that says Access is being configured – so each user gets the default settings that Microsoft uses, so the security file is pointing to a System.MDW file in the users private Documents path, and the behavior of a database in terms of the options will all be the same. One thing to note – hopefully you have the full version of Terminal Services. If you don’t you may be using the standard 2 user Admin feature that comes with Win 2003 – that is limited to two simultaneous users, and users have the option of shutting down the server!

Do you have any comment on my question 1 about the security warning?

The only way I’ve had any success in dealing with that issue is to set the Macro security warning to Low as you did. It is possible to set up a digital certificate and jump through a number of hoops to avoid that warning each time, but our users in general only use the database(s) we provide them, and we are pretty careful to make sure there are no macros in what we deploy (other than the two or three we routinely use for keystroke trapping). So we set all users to Low when we configure their workstations. (Office 2007 presents some other challenges with regard to the trust center – but that doesn’t affect you.)

Hope this helps.

Reply | Quote

In any event, the key to understanding Terminal Services and Remote Desktop is that each user gets their own workspace and their own installation of Office Apps. So the first time a user starts Access, they get a message that says Access is being configured – so each user gets the default settings that Microsoft uses, so the security file is pointing to a System.MDW file in the users private Documents path, and the behavior of a database in terms of the options will all be the same. One thing to note – hopefully you have the full version of Terminal Services. If you don’t you may be using the standard 2 user Admin feature that comes with Win 2003 – that is limited to two simultaneous users, and users have the option of shutting down the server!

Thanks Wendell. Could you expand on what you mean that each user gets their own installation of Office Apps? Does this mean that each user should be running their own copy of Access as well as their own copy of the front end of the database? I think this is something I have misunderstood about how it works. I was under the impression that there was only one copy of Office/Access and that everyone ran their own front end through that one copy of Access.

I’m not too concerned over the System.mdw versus Access security since they are using a custom login rather than Access security. It seems sufficient for their needs and was something I inherited from the original designer.

Reply | Quote

Thanks Wendell. Could you expand on what you mean that each user gets their own installation of Office Apps? Does this mean that each user should be running their own copy of Access as well as their own copy of the front end of the database? I think this is something I have misunderstood about how it works. I was under the impression that there was only one copy of Office/Access and that everyone ran their own front end through that one copy of Access. ….

What I meant was that each user gets their unique user configuration data and setup information. You are correct about there only being one msaccess.exe file on the server, however each user runs their own workspace and will have an Access application running if you look in Task Manager in the Applications tab. Hope this helps.

Reply | Quote