Securing Tables in MS-Access

Dear,

I have a problem in securing Microsoft Access tables from users. I want
users of the database to access data only from forms and reports. How can I
do it.

Thank you

Hi Alemseged,

The easiest way, which doesn't involve using Access security, is to use a
custom switchboard form and disable various startup options.

Start by creating a switchboard form if you have not already done this. You
can either use the Switchboard wizard or, better yet, create your own
switchboard form. Set the appropriate properties for this form, including
Auto Center and allow form view as the only available view.

Set the appropriate options under Tools Startup... Deselect "Display
Database Window", "Allow Full Menus", "Allow Special Access Keys", etc. Pick
your switchboard form for the Display Form/Page option.

To view the database window later, you can hold down the shift key while
starting your database. You can even disable the shift key if you want, using
custom code. Post back if you want further details on how to do this. (To
fully secure the disable shift key trick, you'll need to use Access security,
but even that isn't really all that secure). Finally, I recommend converting
your database to a .MDE file, which will prevent your users (and you, so you
need to retain the original .MDB file) from making any design changes to
forms, reports, macros or modules.

If this is a multi-user database, then by all means split the database into
a front-end and back-end. The back-end is put on a file server. Each user
gets a copy of the front-end installed on their local hard drive. There are
actually a lot of things that need to be done if this is a multi-user
database, post back if you need additional details.

Tom
_________________________________________

"Alemseged Berhe" wrote:

Dear,

I have a problem in securing Microsoft Access tables from users. I want
users of the database to access data only from forms and reports. How can I
do it.

Thank you

In your application, hide the database window.

Make sure you set one of your forms to auto open when the
app is started.

Look up Access Security in the help, I don't use it so
won't comment further.

Make your app an mde.

If you feel really adventurous you could write a substitute
database window in vba/DAO which hides those things you want
hidden using code. There are chunks of code which do this
in the Developers Handbook and I think on the mvps site.

www.developershandbook.com

www.mvps.org/access

--
Nick Coe (UK)
http://www.alphacos.co.uk/ AccHelp + pAnimal
http://www.pjandcoe.co.uk/ UK TShirts with Bark Factor!

In ,
Alemseged Berhe typed:
Dear,

I have a problem in securing Microsoft Access tables from
users. I want users of the database to access data only
from
forms and reports. How can I do it.

Thank you

Alemseged Berhe wrote:
Dear,

I have a problem in securing Microsoft Access tables from users. I
want users of the database to access data only from forms and
reports. How can I do it.

Thank you

There are a number of options. I use user level security.

I suggest you start by reading
http://support.microsoft.com/default.aspx?scid=kb;[LN];207793

Access security is a great feature, but it is, by nature a complex product
with a very steep learning curve. Properly used it offers very safe
versatile protection and control. However a simple mistake can easily lock
you out of your database, which might require the paid services of a
professional to help you get back in.

Practice on some copies to make sure you know what you are doing.

--
Joseph Meehan

Dia's Muire duit

Dear Tom,

Thanks to your constructive reply.

Your clarification to answer my questions will lead me to a solution to my
problems. My database is a multiuser database with a front end and backend,
which reside on a file server. My database has a user level security and a
custom security, which I use it to secure table fields on forms. My database
has a central switchboard with a splash creen. I could secure, by giving a
proper permission, everything on my forms from users. I could even secure
my forms, reports, macros and modules by changing the mdb file to mde file
and/or using the user-level security. My problem arises, when my users
entered to the shared folder, which contains the backend mdb file, on my
file server by pressing the SHIFT key while MS Access starts. This time,
the users can access some fields, which I don't want them to be accessed
through tables or queries. Therefore, the custom code to secure the tables
and queries will help me to secure my tables and queries.

Alemseged.

"Tom Wickerath" AOS168 AT @comcast DOT net wrote in message
...
Hi Alemseged,

The easiest way, which doesn't involve using Access security, is to use
a
custom switchboard form and disable various startup options.

Start by creating a switchboard form if you have not already done this.
You
can either use the Switchboard wizard or, better yet, create your own
switchboard form. Set the appropriate properties for this form,
including
Auto Center and allow form view as the only available view.

Set the appropriate options under Tools Startup... Deselect "Display
Database Window", "Allow Full Menus", "Allow Special Access Keys", etc.
Pick
your switchboard form for the Display Form/Page option.

To view the database window later, you can hold down the shift key while
starting your database. You can even disable the shift key if you want,
using
custom code. Post back if you want further details on how to do this.
(To
fully secure the disable shift key trick, you'll need to use Access
security,
but even that isn't really all that secure). Finally, I recommend
converting
your database to a .MDE file, which will prevent your users (and you, so
you
need to retain the original .MDB file) from making any design changes to
forms, reports, macros or modules.

If this is a multi-user database, then by all means split the database
into
a front-end and back-end. The back-end is put on a file server. Each
user
gets a copy of the front-end installed on their local hard drive. There
are
actually a lot of things that need to be done if this is a multi-user
database, post back if you need additional details.

Tom
_________________________________________

"Alemseged Berhe" wrote:

Dear,

I have a problem in securing Microsoft Access tables from users. I want
users of the database to access data only from forms and reports. How
can I
do it.

Thank you

Hi Alemseged,

My database is a multiuser database with a front end and
backend, which reside on a file server.

Each user should have a copy of the front end installed on their own hard
drive.

My database has a user level security and a custom security,...

I could even secure my forms, reports, macros and modules by changing
the mdb file to mde file and/or using the user-level security.

I'm a little confused. Your first sentence indicates the you have user level
security. The second sentence indicates the opposite (I could even...using
the user-level security). So, I'm not sure if you have implemented Access
security or not. I do recommend that you convert your front end .mdb file
into a .mde prior to distribution. Your code will stay compiled, and you'll
likely have less headaches with missing references. Here is a checklist to
follow, in case you have trouble converting to the .mde:

http://www.access.qbuilt.com/html/co...#CannotMakeMDE


My problem arises, when my users entered to the shared folder,
which contains the backend mdb file, on my file server by pressing
the SHIFT key while MS Access starts.

There's really no reason that your users should even *know* where to look
for the back-end file. If you install a local copy of the front-end file on
each user's hard drive, they only need to worry about opening the front-end.
You can even build them a shortcut, and place a copy in their Start
Programs menu and on their desktop to make it easier for them.

To keep users from opening your back-end file independently, you can add a
startup form to the back-end. Place the following code in this startup form's
code module:

Option Compare Database
Option Explicit


Private Sub Form_Open(Cancel As Integer)
On Error GoTo ProcError

MsgBox "You are not authorized to open this database." & _
vbCrLf & "Please close it immediately.", vbCritical, _
"Your Application Title Here"

DoCmd.Quit

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_Load..."
Resume ExitProc


End Sub

Set the startup options for the back-end similar to the front-end, so that
you are not displaying the database window, you are disabling special keys,
setting this form as startup form, etc. Then use Albert Kallal's nifty Shift
Key utility to disable the shift key trick. You can download a copy he

http://www.members.shaw.ca/AlbertKal.../msaccess.html

However, as explained in this article, to prevent someone else from
re-enabling the shift key with similar code, you will need to implement
Access security:

Securing AllowBypassKey
http://www.mvps.org/access/general/gen0040.htm

You probably don't need to go to this extra bother, since most people will
not know how to re-enable the shift key. And, as I implied in my earlier
message, Access security only raises the bar. A determined and knowledgeable
hacker can still get in, if they are given access to the file.


Tom
______________________________________

"Alemseged Berhe" wrote:

Dear Tom,

Thanks to your constructive reply.

Your clarification to answer my questions will lead me to a solution to my
problems. My database is a multiuser database with a front end and backend,
which reside on a file server. My database has a user level security and a
custom security, which I use it to secure table fields on forms. My database
has a central switchboard with a splash creen. I could secure, by giving a
proper permission, everything on my forms from users. I could even secure
my forms, reports, macros and modules by changing the mdb file to mde file
and/or using the user-level security. My problem arises, when my users
entered to the shared folder, which contains the backend mdb file, on my
file server by pressing the SHIFT key while MS Access starts. This time,
the users can access some fields, which I don't want them to be accessed
through tables or queries. Therefore, the custom code to secure the tables
and queries will help me to secure my tables and queries.

Alemseged.
______________________________________

"Tom Wickerath" AOS168 AT @comcast DOT net wrote in message
...

Hi Alemseged,

The easiest way, which doesn't involve using Access security, is to use a
custom switchboard form and disable various startup options.

Start by creating a switchboard form if you have not already done this. You
can either use the Switchboard wizard or, better yet, create your own
switchboard form. Set the appropriate properties for this form, including
Auto Center and allow form view as the only available view.

Set the appropriate options under Tools Startup... Deselect "Display
Database Window", "Allow Full Menus", "Allow Special Access Keys", etc. Pick
your switchboard form for the Display Form/Page option.

To view the database window later, you can hold down the shift key while
starting your database. You can even disable the shift key if you want, using
custom code. Post back if you want further details on how to do this. (To
fully secure the disable shift key trick, you'll need to use Access security,
but even that isn't really all that secure). Finally, I recommend converting
your database to a .MDE file, which will prevent your users (and you, so you
need to retain the original .MDB file) from making any design changes to
forms, reports, macros or modules.

If this is a multi-user database, then by all means split the database into
a front-end and back-end. The back-end is put on a file server. Each user
gets a copy of the front-end installed on their local hard drive. There are
actually a lot of things that need to be done if this is a multi-user
database, post back if you need additional details.

Tom
_________________________________________

"Alemseged Berhe" wrote:

Dear,

I have a problem in securing Microsoft Access tables from users. I want
users of the database to access data only from forms and reports. How can I
do it.

Thank you