Solved

MS Access User Access

Posted on 2011-03-17
11
260 Views
Last Modified: 2013-11-28
Hi,
   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.
0
Comment
Question by:vihaan
[X]
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
11 Comments
 
LVL 9

Expert Comment

by:sshah254
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).

Sandip
0
 

Author Comment

by:vihaan
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  ?
0
 
LVL 7

Expert Comment

by:RemRemRem
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.

-R
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:vihaan
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.
0
 
LVL 7

Expert Comment

by:RemRemRem
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...?

-R
0
 

Author Comment

by:vihaan
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 .
0
 
LVL 7

Accepted Solution

by:
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?
-R
0
 

Author Comment

by:vihaan
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
0
 
LVL 40

Expert Comment

by:als315
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
http://blogs.msdn.com/b/mssmallbiz/archive/2007/08/08/4298215.aspx
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.
0
 

Author Closing Comment

by:vihaan
ID: 35384232
good
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

733 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