Solved

Access to Back End Tables in MS Access

Posted on 2011-09-30
16
343 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 100

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 100

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 100

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 100

Expert Comment

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

mlmcc
0
 
LVL 50

Accepted Solution

by:
Gustav Brock earned 500 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 50

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 50

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…

680 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