MS Access User Access

Posted on 2011-03-17
Last Modified: 2013-11-28
   There are 9 users who access the MS Access database and it has got user access level security applied on it. So everyone has got their user id's and passwords. The problem is now that User's company has decided to give them a remote access connection So they would have Laptops to connect to their server to work on the database.
                                          So If the laptop has got different version of access and Desktops at office would have different versions of access, In such case the code behind the user level security has to work irrespective of the version of access. The user's company IT guy has thought if everyone has to go through the remote access to work on the database irrespective of whether they are in office or at home then we could maintain just one version of access. In this way we do not have to worry about multiple versions of access. please help me whether this is the right way to go or not and let me know if i would face any difficulties in the future , what ever that comes to your mind is helpful. Thank You.
Question by:vihaan
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 35157885
Ideally, one should break the connection between a database and the UI to the database ... i.e. go the web route.

If the client is unwilling to move to this sort of a client server architecture, then ...

To remain consistent, give everyone the same version of access and have them VPN in and work on the database.

Or give them access through a Terminal server (MS Access would be installed on the Terminal Server).


Author Comment

ID: 35158066
Well, THe second solution was the thing me and client company are looking into Having same version of access and making th go through VPN to work on the database. Right way  ?

Expert Comment

ID: 35158555
Down side of the model you're using is that it's easy for them to use a single install of Access if everyone comes in via the same VPN. Unless the company has set it up so that only one user can come in through that machine at a time, Access is going to get insanely slow. Also, it's critical for the stability of Access that you don't have multiple users opening the SAME database, but rather, COPIES of the same front end. That way, if one person's UI crashes or they shut down inappropriately, it doesn't corrupt the central copy over time.

I usually use a model that puts a fresh copy of the front end DB in some user-local drive folder every time they boot up. This means we keep a pristine master copy that no one opens, and if someone corrupts their front end a little bit, it gets replaced with a safe copy on reboot.

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!


Author Comment

ID: 35158652
I am not going through front end and back end approach. Where you would hae go through a sql server or web route where you would have back end at your side and front end would be accessible . But i am not going that way.
                                                             Users have their own copies of the databaseand they put it on their server and all the users would be having shared drive so all of them could access the database.

Expert Comment

ID: 35158720
I'm not sure I understand you - when I say front end, I mean "all the user interface" and back end doesn't have to be a SQL or web server, but can be a single centralized Access database which all the front ends write to. I *think* this is what you're saying you do...?


Author Comment

ID: 35159076
No I do not have a front end or back end . These words comes into the picture only when you split the database. But I did not do that in my case. I have a single database. I gave that to a User where he put it on his server and all the staff would have shared drive that give access to the database to work on.
              I have another copy of the same database on my side and I would files from them every week like excel files with all the data in it. I import the data in the files into my database on a weekly basis. So that I would have all the data that the user has got. This is what i do .

Accepted Solution

RemRemRem earned 500 total points
ID: 35159164
Ok, I think I've got it now.

So. To get back to your initial question - I see a few issues for your IT guys:

As proposed, unless they are not installing Access locally, it means two licenses of Access for each user. One on the local machine and one on the server side. If you leave two versions in place, you may have minor conflicts if you're using outside tools (such as auto emailing through versions of Outlook from the database, in which case the local machine's Outlook settings will cause grief at times if the commands are coming in from a different version of Access).

If you instead have them open up the server side database while remoted in, but from the local machine, it will slow things down enormously. If they remote in and open it on the server side, it will have better response time, but may not APPEAR to, since then you run into refresh rate and bandwidth issues on the connecting machine.

Both server and client side give you issues. I'd go with server side OR, as may be the case, since your databases are stand alone, best of all - Why not copy the entire database to their local machines and let them run locally all the time, only dropping the output Excel files onto the server when connected?

Does that help at all, or am I only confusing matters for you?

Author Comment

ID: 35159457
I do want to copy the database and put it on their local machines because that would cause user level security issues because the database has joined to a specific mdw files which has got the login details for all the users. So this option is eliminated.
                                                I thought all the users should go through the remot access to work on the database irrespective of whether they are in the office or at home so that we could maintain the same version of access across all the users. What do you think about it ?. Let me know
LVL 40

Expert Comment

ID: 35174747
As proposed, unless they are not installing Access locally, it means two licenses of Access for each user. One on the local machine and one on the server side
You will need only one user license, if it is not OEM Access (Office).
Why you need full access for users? Use runtime. From 2007 it is free.
It could be installed on terminal server and it is recommended solution for remote use of Access DBs.

Author Closing Comment

ID: 35384232

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

627 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