Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 380
  • Last Modified:

Access to Back End Tables in MS Access

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
Nero83
Asked:
Nero83
  • 6
  • 4
  • 3
  • +1
1 Solution
 
mlmccCommented:
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
 
Mike EghtebasDatabase and Application DeveloperCommented:
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
 
Nero83Author Commented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Mike EghtebasDatabase and Application DeveloperCommented:
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
 
mlmccCommented:
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
 
Nero83Author Commented:
@ 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
 
mlmccCommented:
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
 
Mike EghtebasDatabase and Application DeveloperCommented:
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
 
Nero83Author Commented:
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
 
mlmccCommented:
I don't believe you can UNSPLIT the database.

mlmcc
0
 
Gustav BrockCIOCommented:
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
 
Nero83Author Commented:
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
 
Gustav BrockCIOCommented:
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
 
Nero83Author Commented:
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
 
Gustav BrockCIOCommented:
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
 
Nero83Author Commented:
I'll check it out.  Thanks again for the info.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 6
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now