Solved

MS Access User Access

Posted on 2011-03-17
11
256 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
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
 

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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

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 39

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

705 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now