Shared Drive, Security

[Pixie78]

I need serious help with this. I have read through the instructions over and
over. I'm not dumb and this isn't working how it says it should.
I created a database. I need to put it on our shared drive (S:\) so other
people can start using it. I need 3 people to have permissions to do
whatever they want to it and everyone else only needs to be able to print
reports and look at forms but not make any changes to anything what so ever.
Currently the database is on my desktop. First I need to know if I leave it
there to start all this security wizard stuff or do I place it in the shared
drive first?
I created a new workgroup that goes to the shared drive training database
folder.
I opened my database and went through the security wizard and set up names
and passwords for 6 people.
I want to go in and set up user groups.
I can't open the database under the workgroup I created and when I open the
database under the default workgroup, no security level exists for the
database....
Can somebody PLEASE point me to clearer instructions on how to set up a
database on a shared drive?????? I was supposed to have this running a while
ago and no matter what I do, it's not working correctly.
Thank you so much for any help.
~Stressed~

 

[Joan Wild]

I need serious help with this. I have read through the instructions over and
over. I'm not dumb and this isn't working how it says it should.

What instructions are you following?
Security FAQ
http://support.microsoft.com/?id=207793

Security Whitepaper
http://support.microsoft.com/?id=148555

I've also outlined the detailed steps at
www.jmwild.com/AccessSecurity.htm
and you'll find other links there.

I created a database. I need to put it on our shared drive (S:\) so other
people can start using it.

You need to split the database and put just the backend (and the mdw) on the 'S' drive. Are you certain that everyone will have 'S:' mapped to the same location?

I need 3 people to have permissions to do
whatever they want to it and everyone else only needs to be able to print
reports and look at forms but not make any changes to anything what so ever.

You can secure it so that 'everyone else' uses their default system.mdw and therefore don't need to login. The 3 users would use the secure mdw and login . You'll find instructions on my site to do this - 'securing without needing to login'

Currently the database is on my desktop. First I need to know if I leave it
there to start all this security wizard stuff or do I place it in the shared
drive first?

You can leave it there for now. Get it secured and working properly. Then split it manually (you'll find the step by step for this on my site as well). You'll put the backend on the server along with the secure mdw file.

For most users, just copy the frontend to their computers (they'll use their default system.mdw and don't need a shortcut). For the three users that need full access, you'll give them a copy of the frontend, and a desktop shortcut to start the database. The shortcut target would look like:
"path to msaccess.exe" "path to frontend on their computer" /wrkgrp "path to the secure mdw on the s drive"

 

[Pixie78]

Joan,

Your instructions are the ones I found and followed. I feel I am going in
circles with this. Obviously I'm misunderstanding a crucial part of the
instructions. I don't seem to be the only one because every post I have
looked up here and other forums seem to be having the same issues. Though I
am not familar with setting up securities and shared drives, I am not dumb by
any means and feel like this shouldn't be that difficult to master.
Especially considering how far I've come in learning Access and VBA. I
realize it is tricky, as Access is very picky but it is my job to administer
databases so I have to learn it. =)
I have printed out the whitepaper instructions and am reading them over. So
far I have managed to create a new workgroup different than my default. I
have set up passwords for users, though I do not know where they exist
anymore. I think they are in the path to the other workgroup I have set up,
but the database is not in that workgroup. I have also managed to set up
passwords on my Access (not on purpose) so no matter what I open in Access I
am asked for a user name and password. Which is not at all what I wanted,
though many others seemed to have done the same.
I created this other workgroup and the path I used was in the shared drive,
so basically it created a copy of Access there with that workgroup, correct??
I can't open anything there because all my databases are connected to my
default workgroup. I'm a little confused on that.
I am also confused about the splitting of the database. I had tried that
before, per your instructions also and it split.... but I don't understand
the point of it. I was still able to go into the front end copy and go into
the linked tables and change information.
Also, I'm trying to understand your last comment:

For most users, just copy the frontend to their computers (they'll use their default system.mdw and don't need a shortcut). For the three users that need full access, you'll give them a copy of the frontend, and a desktop shortcut to start the database. The shortcut target would look like:
"path to msaccess.exe" "path to frontend on their computer" /wrkgrp "path to the secure mdw on the s drive"

How do I copy the front end to their computers? I don't know if there is a
right and wrong way. I mean, email it to them, put it on a disk, put it on
the shared drive and pick it up from there..... does it matter?? And if they
just have a front end copy, how will they be getting the updated information?
This database will be updated constantly, maybe not daily but it tracks our
employees trainings, which are pretty frequent. The "others" (about 8) will
just need to print reports and that's it. The 3 main are myself as the
adminstrator and my 2 bosses. We will be the only ones who can update
employee information, though I'm not so sure I want them fooling around
looking at code and erasing something, lol. But it is what it is.
What I would love is if I could just put out access to this one report, our
sign off sheet, for anyone to be able to use but not the ability to see or do
anything else. Is that at all possible???? lol. I would still need to set
up the whole database as already said but that would alieviate a lot of my
pains.
Thanks for any further help. I know it's a lot of questions but if I don't
get it exactly right, it doesn't work at all.

 

[Joan Wild]

Joan,

I have printed out the whitepaper instructions and am reading them over. So
far I have managed to create a new workgroup different than my default.

Are you joined by default to this new workgroup file? You can check this by running the workgroup administrator.

I

have set up passwords for users, though I do not know where they exist
anymore.

How did you set up passwords for each user? did you login as each and then set a password? Or are you (mistakenly) thinking that the PID you entered when you created the user is a password; it's not.

I think they are in the path to the other workgroup I have set up,

How/why do you think this?

but the database is not in that workgroup. I have also managed to set up
passwords on my Access (not on purpose) so no matter what I open in Access I
am asked for a user name and password. Which is not at all what I wanted,
though many others seemed to have done the same.

Access uses a workgroup file in every session; it can't work without one. It uses system.mdw out of the box and silently logs you in as a user named 'Admin'. If you are getting a login prompt for every session, then you are joined by default to a workgroup file that has a password set for the 'Admin' user - that is what triggers the login dialog to appear.

I created this other workgroup and the path I used was in the shared drive,
so basically it created a copy of Access there with that workgroup, correct??

No it created a workgroup file (mdw extension) in that location. You may or may not be joined to it as the default (you can check what is set as the default by looking in the workgroup administrator). I always leave the default set to the system.mdw that ships with Access. When I need to use a different workgroup file for a session, I use a desktop shortcut with the following in the target:
"path to msaccess.exe" /wrkgrp "path to secure mdw"

The /wrkgrp switch overrides the default and uses the specified mdw for that session of Access. It doesn't matter what or many mdb files I open during that session. A mdb isn't tied to a mdw file, or vice versa. The mdw file controls the Access session.

I can't open anything there because all my databases are connected to my
default workgroup. I'm a little confused on that.
I am also confused about the splitting of the database. I had tried that
before, per your instructions also and it split.... but I don't understand
the point of it. I was still able to go into the front end copy and go into
the linked tables and change information.

You want to split a mdb, not for security reasons, but to avoid corruption, among other things.

Also, I'm trying to understand your last comment:

How do I copy the front end to their computers? I don't know if there is a
right and wrong way. I mean, email it to them, put it on a disk, put it on
the shared drive and pick it up from there..... does it matter??

No it doesn't matter, as long as they get it.

And if they

just have a front end copy, how will they be getting the updated information?

The frontend contains linked tables - linked to the tables in the single backend file on the server that contains the actual tables. Everyone's frontend is linked to this single file.

This database will be updated constantly, maybe not daily but it tracks our
employees trainings, which are pretty frequent. The "others" (about 8) will
just need to print reports and that's it. The 3 main are myself as the
adminstrator and my 2 bosses. We will be the only ones who can update
employee information, though I'm not so sure I want them fooling around
looking at code and erasing something, lol. But it is what it is.

I would suggest that you get the security implemented for these three users. Then work on the security for the remaining users. Basically you'll be assigning the permissions to the Users Group for these remaining users.

What I would love is if I could just put out access to this one report, our
sign off sheet, for anyone to be able to use but not the ability to see or do
anything else. Is that at all possible???? lol.

Yes it is.
I would still need to set

up the whole database as already said but that would alieviate a lot of my
pains.
Thanks for any further help. I know it's a lot of questions but if I don't
get it exactly right, it doesn't work at all.

That is exactly right. I have a feeling that you are going too fast, and therefore are missing critical steps. Every step must be done in order.

 

[Pixie78]

I am still with my default workgroup. That is, the default that was here
when I got here (to this job), is still my default.
The location of the workgroup file I created is in a folder in the shared
drive. Is that where I should have put it? Or it should stay with my other
Access Files just with a different end name other than Secured.mdw? In that
workgroup file it has a query of all the user names I created with the
security wizard in my database. So, I think I'm on the right track so far??
I think I will read all your comments over about 3 more times because the
more I read it, the more it makes sense. I will fool with my home copy this
weekend and see what I can do. I may need to undo all the security and
workgroup files I created here and start over. =)
Thanks for all your help so far. I'm sure you are right in that I was going
too fast just to get it set up.
As far as being able to make one form available for anybody. How would I
start going about that? Assuming I did everything else I needed to do with
the securities and the shared drive correctly.
It would be a filter form that is acutally connected to 2 queries and 2
reports. A main report that has a static heading and the subreport that
contains all the chosen names from the form. If it matters, the form has 2
list boxes, a list of departments and a list of employees. Both multi
select. Choose the departments and the name list fills with all the
employees in those departments. I would love to have that available for all
my managers so they could print out these forms themselves but they don't
need to have anything else to do with the database or its information.
Access was just an easy way to automate this form since I already had all the
employees and their departments in it. They were just using an excel sheet
and manually typing all the info in it each time they needed one which is
tedious.

Again thanks so much for all your info. I hope this weekend proves
productive! =)

 

[Joan Wild]

I am still with my default workgroup. That is, the default that was here
when I got here (to this job), is still my default.
The location of the workgroup file I created is in a folder in the shared
drive. Is that where I should have put it?

Sounds fine.

In that
workgroup file it has a query of all the user names I created with the
security wizard in my database.

First you shouldn't be opening the mdw file directly; I take it you created this query?

So, I think I'm on the right track so far??
I think I will read all your comments over about 3 more times because the
more I read it, the more it makes sense. I will fool with my home copy this
weekend and see what I can do. I may need to undo all the security and
workgroup files I created here and start over. =)
Thanks for all your help so far. I'm sure you are right in that I was going
too fast just to get it set up.

I remember the first time I tried to implement security. I was *very* frustrated as it did not work. I got tired of being told I was missing a step, when I was certain that I wasn't. I finally got angry and slowed down, following every *phrase*. And it worked; I had skipped over some minor thing that, to me, did not seem important.

As far as being able to make one form available for anybody. How would I
start going about that? Assuming I did everything else I needed to do with
the securities and the shared drive correctly.

I would recommend that you get everything working properly for the three users, shared/split, etc. Once you're at this point, you then would assign permissions to the Users Group for 'everybody'. Since the Users Group is common to all workgroup files, and they'll be using their standard system.mdw, they'll be able to use only the objects to give permissions on to the Users Group .

It would be a filter form that is acutally connected to 2 queries and 2
reports. A main report that has a static heading and the subreport that
contains all the chosen names from the form. If it matters, the form has 2
list boxes, a list of departments and a list of employees. Both multi
select. Choose the departments and the name list fills with all the
employees in those departments. I would love to have that available for all
my managers so they could print out these forms themselves but they don't
need to have anything else to do with the database or its information.

You'll need to give the Users Group open permission on the database object, and then read data permission on the queries (I suggest you make these queries RWOP - run with owner permissions so that you don't have to give any permissions on the tables - see www.jmwild.com/rwop.htm for more information). They will also need open permission on the reports, and on the form. You'll also need to grant read permission on the rowsources of the listboxes.

Once you think you've granted the necessary permissions, just open the mdb while joined to the standard system.mdw workgroup file and test it out, and you'll see if you've missed anything.

 

[Pixie78]

Alrighty, I undid all previous attempts at security and went through it as
slow as possible. I got it all working on my computer but it was all pathed
through my computer which no one else in the company has access to. After
some work with my IT guy, I realized the tables were linked to the back end
on my desktop, not to the one I put in the shared drive. So it seems all is
fine and dandy for now... at least for mine and my boss' computer.
I was trying to set up the Users permissions for the one form and I only got
it so far. They can open the db, access the form through the main menu,
which is great that if they try to click any other buttons it tells them they
don't have permission to do so. So the report form opens up and nothing, my
list boxes are empty. I put Owners on all my queries Run With property and
gave the Users group Read Data permission on those queries pertaining to the
report and form. The first list box "Departments" is based on my departments
table though. I granted read permission and the list box populates however
when I select one, I get an error that says:
"You do not have the necessary permissions to use the 'MSysTables' object.
I'm not sure I know what that means. Also I wasn't sure how to grant read
permission on the rowsources of the listboxes. I tried looking around Access
and also searched the forum and didn't see what I was looking for.

You'll need to give the Users Group open permission on the database object, and then read data permission on the queries (I suggest you make these queries RWOP - run with owner permissions so that you don't have to give any permissions on the tables - see www.jmwild.com/rwop.htm for more information). They will also need open permission on the reports, and on the form. You'll also need to grant read permission on the rowsources of the listboxes.

I followed the above instructions except for the last part of the rowsource
permission. I appreciate all your help so far and patience. =)

 

[Joan Wild]

Alrighty, I undid all previous attempts at security and went through it as
slow as possible. I got it all working on my computer but it was all pathed
through my computer which no one else in the company has access to. After
some work with my IT guy, I realized the tables were linked to the back end
on my desktop, not to the one I put in the shared drive. So it seems all is
fine and dandy for now... at least for mine and my boss' computer.

You just need to use Tools, Database Utilities, and relink to the location of the backend on the server. If you put a check at the bottom of the dialog, and navigate via My Network Places, it'll use UNC pathnames to relink to the backend - then you don't need to be concerned about the various drive mappings that users might have.

I was trying to set up the Users permissions for the one form and I only got
it so far. They can open the db, access the form through the main menu,
which is great that if they try to click any other buttons it tells them they
don't have permission to do so. So the report form opens up and nothing, my
list boxes are empty. I put Owners on all my queries Run With property and
gave the Users group Read Data permission on those queries pertaining to the
report and form. The first list box "Departments" is based on my departments
table though. I granted read permission and the list box populates however
when I select one, I get an error that says:
"You do not have the necessary permissions to use the 'MSysTables' object.

See www.jmwild.com/Msystables.htm

If you don't want to assign read permission on the Departments table, you can create a RWOP query based on it and base the listbox on this query.

I'm not sure I know what that means. Also I wasn't sure how to grant read
permission on the rowsources of the listboxes. I tried looking around Access
and also searched the forum and didn't see what I was looking for.

Change the rowsources to saved RWOP queries, and grant read permissions on these queries.

 

[Pixie78]

Question regarding the RWOP queries. I made RWOP queries for each of my
tables. I based the list box row sources on those queries. So the list box
of departments now populates. However... when it is pulling the info needed
to populate the report, those queries are still based on my tables. So it's
still giving me an error saying I don't have permissions on MSysTables. Do I
have to make my all queries based on the RWOP queries that I want group Users
to be able to see? When I went in under the Users group and opened the query
to the report, I could read it. I couldn't go to design view because it said
I didn't have the read permissions on one of the tables.
I wanted to get clarification on that before I tried to undergo changing all
my queries to be based on the RWOP table queries I created.
Thanks for all your help

 

[Joan Wild]

'No permissions on the MSysTables' message suggests to me that you are modifying the SQL property of a saved query in code.

If that is the case, the users will need modify permission on this query.

 

[Pixie78]

I'm still having issues with the User permissions. I made RWOP queries for
each of my tables. I set the rowsource of the list box to the rwop dept
query and granted permission to the query. The list box fills with the
dept's but when I try to select one, it tells me that I don't have permission
to MSysTables. I went back and granted read permission to all queries, all
forms and then even the tables from the front and back end, and it still
gives me the same error. It's highlighting my strSQL = in my code. Once you
select a department(s) the next list box should fill with the employee names
from that department. That's where it's stopping and I can't figure out why.
It's doing the same for my Read Only users. However everything seems to be
working for me as the owner and my Full Data Users. =/
_____________________________________________________________
strSQL = "SELECT EmpName FROM qrySignOffInfo " & _
"WHERE qrySignOffInfo.Dept IN(" & strCriteria & ") ORDER BY
EmpName;"

qdf.SQL = strSQL
_______________________________________________________

That's the part of the code it's stopping on to fill the Employee list. I
even changed the Sign Off query to use the RWOP queries instead of the
tables. What am I doing wrong and/or forgetting?????

 

[Joan Wild]

Users need modify permission on the query referenced by qdf

 

[Pixie78]

It must be Monday. I thought I canceled the last post before lunch, guess I
sent it. haahahaha. I'll try the suggestion. Just ignore this post =))))

 

[Joan Wild]

Users need modify design permission on the query referenced by qdf.

 

[Pixie78]

Ok, Got it!!! lol. I was giving update thinking that would do it. Gave them
Read and Modify and now it's working!! Hooray!! I think that's the final
kink in this database! Sorry for the double post. I was trying to write a
couple of times and got caught up in other stuff and thought I canceled the
message.
I'm so glad it's working now! Thank you so much Ms Joan!! =)

 

[Joan Wild]

You're welcome, glad you got it sorted.