Different Users for Access 2000

[Kelvin]

Hi

The database that I have created is mainly for public use.
However there are some tables which contains confidential
information to be accessed by specific users only. How can
I tackle this problem?

I see that there are 3 different functinos under Security.
User and Group Permission, User and Group Accounts and
User-Level Security Wizard. Which one do I use?

All users are connected to the main server and each user
had been assigned to a password. Is there anything I
should look out for?

 

[Joan Wild]

Hi

The database that I have created is mainly for public use.
However there are some tables which contains confidential
information to be accessed by specific users only. How can
I tackle this problem?

I see that there are 3 different functinos under Security.
User and Group Permission, User and Group Accounts and
User-Level Security Wizard. Which one do I use?

All users are connected to the main server and each user
had been assigned to a password. Is there anything I
should look out for?

There is a lot more to security that just using those menu items. Don't use
the wizard in 2000 as it doesn't do the job.

You need to download and study the Security FAQ available at
http://support.microsoft.com/?id=207793

Be sure to follow every step outlined or your database won't be secure.

I've also outlined the detailed steps at www.jmwild.com/AccessSecurity.htm

 

[Kelvin Yew]

Hi Joan

Thanks for the reply.

I wanted to follow the methods as listed from the links.
However I got prompted an error message when I was trying
out the first step of typing "wrkgadm.exe" in the Run.

The error message was "Cannot find the file 'wrkgadm.exe'
(or one of its components). Make sure the path and file
name are correct and that all required libraries are
available"

Please help.

 

[Joan Wild]

Search your computer for wrkgadm.exe (you are running Access 2000, right?).
Also ensure that you have permission to view all folders.

 

[Kelvin]

Hi Joan

Thanks for your reply once again.

I managed to find wrkgadm.exe and proceed onto the steps
as listed in http://www.jmwild.com/security97.htm

However I am currently facing the problem of creating the
desktop shortcut with the following target found in the
above website:

“path to msaccess.exe” “path to mdb” /wrkgrp “path to
secure mdw”

Can you please furthur elaborate on this? How can I create
this successfully? I am seeing messages saying this is an
invalid link.

Thank you and best regards
Kelvin

 

[Lynn Trapp]

Kelvin,
The example you found on Joan's website is merely that, an example. You need
to replace the words "path to msaccess.exe", etc. with the appropriate file
path. In many cases it might end up looking like this.

"C:\Program Files\MicrosoftOffice\Office10\msaccess.exe"
"C:\MyFolder\MyDatabase.mdb" /wrkgrp "C:\MyFolder\MyWorkgroup.mdw"

These are also just examples and you will need to change them to fit your
circumstance.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Hi Joan

Thanks for your reply once again.

I managed to find wrkgadm.exe and proceed onto the steps
as listed in http://www.jmwild.com/security97.htm

However I am currently facing the problem of creating the
desktop shortcut with the following target found in the
above website:

“path to msaccess.exe” “path to mdb” /wrkgrp
“path to
secure mdw”

Can you please furthur elaborate on this? How can I create
this successfully? I am seeing messages saying this is an
invalid link.

Thank you and best regards
Kelvin

 

[Kelvin]

Thanks for the explanation. I have successfully create the
desktop shortcut with the following target:

"C:\Program Files\Microsoft
Office\Office\MSACCESS.EXE" "C:\Kelvin's doc\Access
Testing\db1.mdb" /wrkgrp "C:\Kelvin's doc\Access
Testing\SECURITY.mdw"

According to Joan's website, I should not be able to open
the database at all. But for my case, I am prompted the
user password which I have created in Step 13.

Have I done anything wrong here?

For "path to secure mdw", do we set the target to the one
we have created in Step 2? Or do we use the one that is
first found in the system?

------------------------------------------------------

Looking at the section of "Secure it manually"

Can I skip Steps 14-16 as I already have a database in my
system? Or must I follw these steps and import my existing
database to a new dababase?

Sorry to bother you once again

Kelvin
---Original Message-----

 

[Lynn Trapp]

According to Joan's website, I should not be able to open

the database at all. But for my case, I am prompted the
user password which I have created in Step 13.

Have I done anything wrong here?

Do you mean you can open it by double clicking on the .mdb file in windows
Explorer and you get the logon prompt? That merely means that you are joined
to the security.mdw file.

 

[Kelvin]

Yes. So am I on the right track?

-----Original Message-----

Do you mean you can open it by double clicking on the .mdb file in windows
Explorer and you get the logon prompt? That merely means that you are joined
to the security.mdw file.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



"C:\MyFolder\MyDatabase.mdb" /wrkgrp "C:\MyFolder\MyWorkgr


.

 

[Kelvin]

When I switched back to the original .mdw file (not the
one I created) and tried opening the database, there was
an error message saying I do not have the necessary
permissions. Is Joan referring to this?

Also, I tried to access the database using the shortcut
that I have created, it works!

Is this it?

 

[Lynn Trapp]

It sounds like you have it working correctly. You got the error message you
should have gotten when trying to open the database directly. Way to go.

 

[Kelvin]

Thanks for all the assistance provided.

As there will be a total of 6 users including myself, I
will need to create 6 users and assigned the appropriate
permissions to different users.

After doing that, do I just create the shortcut on every
of the 6 computers in order for this to take place? Or do
I have to repeat all the steps for all 6 PCs?

 

[Lynn Trapp]

You only need to create the users once. Give each of them a copy of the
shortcut for their desktop. Also, make certain that all of them have Read,
Write, and Delete permissions to the folder the database is stored in.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Thanks for all the assistance provided.

As there will be a total of 6 users including myself, I
will need to create 6 users and assigned the appropriate
permissions to different users.

After doing that, do I just create the shortcut on every
of the 6 computers in order for this to take place? Or do
I have to repeat all the steps for all 6 PCs?

 

[Kelvin]

The database that I am going to create is going to be
stored in the main server eventually and all 6 users,
including me will be connected to the main server.

Will the link that I have created earlier still works?

"C:\Program Files\Microsoft
Office\Office\MSACCESS.EXE" "C:\Kelvin's doc\Access
Testing\db1.mdb" /wrkgrp "C:\Kelvin's doc\Access
Testing\SECURITY.mdw"

Or
Must I re-create the workgroup (.mdw) in the server itself
and perform the steps again on the database stored in the
server?

Or
Work on my desktop first, like what I currently did. When
the database is stored to the server, I just need to
change the "path to mdb"?

 

[Lynn Trapp]

The first part should work just fine, but you will need to change the path
to the db and the path the the security file.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


The database that I am going to create is going to be
stored in the main server eventually and all 6 users,
including me will be connected to the main server.

Will the link that I have created earlier still works?

"C:\Program Files\Microsoft
Office\Office\MSACCESS.EXE" "C:\Kelvin's doc\Access
Testing\db1.mdb" /wrkgrp "C:\Kelvin's doc\Access
Testing\SECURITY.mdw"

Or
Must I re-create the workgroup (.mdw) in the server itself
and perform the steps again on the database stored in the
server?

Or
Work on my desktop first, like what I currently did. When
the database is stored to the server, I just need to
change the "path to mdb"?

 

[Joan Wild]

You won't need to recreate the mdw. Just copy it to the server.

You can change your shortcut to point to the new location. Also you can use
UNC pathnames
\\servername\share\etc... rather than relying on mapped drive letters.

 

[Tim]

The shortcut refers to three files:

The mdb file is, well, the database, but it is now aware that it needs
a password and user login before it can open.

The user details are stored in the mdw file (the WorkGroup). When you
type in your login, it is compared with this file to see if you are
allowed access.

Finally, you need good old MSACCESS.EXE to run the thing at all.


The shortcut basically opens Access and passes it the locations of the
database to open and the WorkGroup file. Access takes over from there.

You should move your mdw file to the server. You don't need to
recreate it. So long as you open Access from a shortcut which points
to it, you will still be able to edit the WorkGroup and add/delete
users. But every user will need to use the same WorkGroup file, so it
needs to be somewhere where they can all access it. Typically this
would be the same folder as the database itself.

You should end up with:
One mdw (workgroup) file on the server
One mdb (database) file on the server
Each user has MS Access installed locally on their own PC.
Each user has a shortcut that points to these three files. The
shortcuts are likely to be different because the location of
MSACCESS.EXE may vary from machine to machine. Also, different users
may use different letters for the server drive.



That answers you immediate question. The next bit (below) is looking
forwad a bit further, so don't worry about it until you've got the
shortcuts etc working.

Your next task is to Split the database, which means splitting it into
two separate files. One contains all the data (tables) and stays on
the server. This is the "Back-End Database". The other part contains
all the forms, queries, reports and modules and you can give each user
a copy to save on their own PC. This is the "Front-End". There is a
Splitting Wizard in Access to do this.
The shortcut would then point to the Front-End on the users PC instead
of the database on the server. The Front-End knows where the tables
are and everything works pretty much exactly the same (certainly as
far as the Users are concerned there is no difference), but now you
aren't squabbling over who can use the database; you all can - at the
same time.

 

[Joan Wild]

Also, different users
may use different letters for the server drive.

Which is why it is better to use UNC pathnames rather than drive letters.

Your next task is to Split the database, which means splitting it into
two separate files. One contains all the data (tables) and stays on
the server. This is the "Back-End Database". The other part contains
all the forms, queries, reports and modules and you can give each user
a copy to save on their own PC. This is the "Front-End". There is a
Splitting Wizard in Access to do this.

You shouldn't use the splitter wizard on a secure database - the result will
be a secure frontend, but the backend will be unsecured. It's better to
split it manually.