Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Access to Back End Tables in MS Access

Posted on 2011-09-30
16
Medium Priority
?
365 Views
Last Modified: 2012-05-12
I have looked around but seem to be lost in other questions...

I have a database that contains confidential client information.  It is an Access 2003 database.
I'm wondering if there is any way to restrict access to the back end in case people gain access rights to view the folder containing the back end and look at the data in there, or even worse, modifying it.  The database is also going to be upgraded to 2010.  There is no user-level security on it at this point.  Any use in upgrading the back end to SQL?...I don't know how to do this, but I thought about it as an option.  Ideally it would be inaccessible to someone using Crystal Reports or something like that also, but first I just want to keep people from opening it using Access.  
Any ideas out there?  Thanks Experts!
0
Comment
Question by:Nero83
  • 6
  • 4
  • 3
  • +1
16 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 36893034
You can password protect the database and require all users to have their own login.
You can then restrict access to tables and views through user permissions.

mlmcc
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 36893051
The linked table in the back-end (containing the information) could be linked to the front-end when an authorized user signs in.

If this causes problem with the operation for the other users, a dummy table could be replace the database.

Logic:
1. User starts the database:
2. User checked for validity?
        Yes: Link the table from back-end if it is not
              (after deleting the dummy table).
        No: Create a dummy table, if necessary.
              (after deleting the linked table).

Mike


 
0
 

Author Comment

by:Nero83
ID: 36893155
Thanks for the input.

@ mlmcc:  So, user-level security will work on a linked back-end?  
I heard that security is different for Access 2010...will I be in bad shape when this gets converted in a few months?

@ Mike:  how are you checking for validity on this?  This might be beyond me in setting up...not sure how to link to one and not the other based on login.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 36893262
re:> how are you checking for validity on this?

We need to have the user names in a table (for now, later could be stored in much secure location where others cannot reach).

If in a module you try:

MsgBox CurrentUser()

It will give you the type of user name or ID you need to store in a table.

Then, on the start up, the CurrentUser() will be compared with the list. If they match then the table gets linked.

Mike
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 36893264
I don't know if it works or not but I would think it would or you may be able to protect it in the front end.

mlmcc
0
 

Author Comment

by:Nero83
ID: 36893329
@ mlmcc: I'm looking to protect it from someone getting into it without going through the front end...

@ Mike, same deal...if it's not going through the linking process, but the tables are just getting looked at cold from the outside by someone being sneaky...

Unless I'm not understanding you...

Thanks!
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 36893375
To protect it that way you need to add a password to the database.

Are there any legitimate users who should access it directly rather than through the front end?
If not don't add any users except for the Admin so someone can make changes.

mlmcc
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 36893413
re:> I'm looking to protect it from someone getting into
There are two sets of information we want to safe guard.
1. The list of users. (If someone can access the list of user, s/he can add another name to bypass your security). To do this, we can add the list to "ControlTip Text" property of a control on the form, for example):

- Add a text  box to your form (txtUsers):
width=0
Tab Stop= No

To its “ControlTip Text” property, add:
smith, dking, shunt, admin
(Whatever user names you want to include).

In a code check to see if you can read the user names:

Sub Test()
    MsgBox CurrentUser()
    MsgBox Forms!Switchboard!txtUsers.ControlTipText
End Sub

re:> ...getting into it without going through the front end...

Do you mean to say  ..getting into it by going through the front end...?

If they have access to the back-end then, no one can stop them to have access to it. What we are trying to do is to stop them accessing that linked table from front-end.

Mike
0
 

Author Comment

by:Nero83
ID: 36893681
Mike,
Thanks for the details.
Actually, I meant "without going through the front end".  It seems that anyone with access to the front end would also have access to the back end without going through the front end, correct?  My understanding is that people need read/write access to that folder in order to use the database.
Maybe I should "unsplit" it to force them to go through the front end...?

Thanks.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 36894311
I don't believe you can UNSPLIT the database.

mlmcc
0
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 36897105
With Access 2003 you can use user level security to limit the casual user. But that's about it. If someone can get hold on the database file, tools on the web priced at few dollars can break the security. Access 2010 doesn't have that feature but the backend itself can be encrypted.

Your best, fastest, and simplest method is to move the data to SQL Server 2008 Express where you can set up precise and documentable access security. Both Access and SQL Server feature wizards to make most of the conversion of data and link these to the frontend via ODBC. This method requires minimal adjustment of the frontend.

/gustav
0
 

Author Closing Comment

by:Nero83
ID: 36899562
Gustav,
Sounds good.  I looked at this online and am now slightly confused, but it is good to know that that is the kind of option I will have to go with.  So, it's an ODBC connection to the front end...interesting.  Do you happen to know if it will involve much from my IT department who are generally unsupportive of much that is new or requires work?  Maybe I'll reserve that for a different post...
Thanks.
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 36899776
Not much but something. A running instance of SQL Server Express (free version) or higher.

And at clients, either a DSN entry for the database has to be created, or you can use a DSN-less connection (browse for this, much info is found on the web).

/gustav
0
 

Author Comment

by:Nero83
ID: 36899828
OK.  Thanks.  
Do you happen to know how much change is required on the front end if any?  Can I just keep editing the front end in Access like I always have been, creating queries, forms, reports, etc., but after conversion of the back end just the link will point to a new source?
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 36899845
It's just the links that change.

Further, a few very Access-specific things may need adjustment, mostly for queries. Lots of info can be found on the web.

/gustav
0
 

Author Comment

by:Nero83
ID: 36900038
I'll check it out.  Thanks again for the info.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

926 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