Access 2002 Help required please......

  • Thread starter Thread starter Simonglencross
  • Start date Start date
S

Simonglencross

I am currently in the process of designing a database whic is going to be
used to record sales, I need some advice on the following.

a) each user (Sales Person) will have there own Customers, Orders, Tasks.
Appointments and Sales Takgets. I have setup the individual tables but I am
unsure what I should do with regards to the individual users being able to
only see there data (Customers, Sales,Appointments ) etc. Can anyone suggest
any ideas maybe point me in the right direction or have any example dbs?

b) I spoke to someone who advised that I should use the Currentuser()
function as criteria on the owned by field where is the 'owned by field'?

C)I understand that I need to use Access security in order to secure the
database, but I am usure about a certain aspect I know you can restrict
users with regards to forms which is fine but what I need is once the user
logs on and they start using the database I need it to the identify in
access whiuch user it is and then display the currebt users data
accordingly. any advise suggestions or examples?

I know I have lotes of question which I appologise for I learning fast but
know very little if anything about vba....

Thanks in advance


Simon
 
Simonglencross said:
a) each user (Sales Person) will have there own Customers, Orders,
Tasks. Appointments and Sales Takgets. I have setup the individual
tables but I am unsure what I should do with regards to the
individual users being able to only see there data (Customers,
Sales,Appointments ) etc. Can anyone suggest any ideas maybe point me
in the right direction or have any example dbs?

b) I spoke to someone who advised that I should use the Currentuser()
function as criteria on the owned by field where is the 'owned by
field'?
Click to expand...

You would need to add this field to your tables - call it OwnedBy or
anything you want.

Use forms for all data entering/updating. You would base your forms on
queries rather than on the tables. You would have a control on your form
(hidden if you like) that is bound to the OwnedBy field. Set the control's
default value to =CurrentUser() so that any new records they create will
automatically store their Access username. If you have existing records,
you'll need to enter the Access username in this field for all current
records.

The queries that are recordsources for your forms/reports would be SELECT *
FROM Customers WHERE OwnedBy = CurrentUser()

Only records where the OwnedBy field is populated with the user's Access
login name will be displayed.
 
Joan,


Thank you for that information it makes things much more clearer now!



Regards


Simon G
 
Joan is there any way I can implement this without have to scrap the forms
which I have done and starting again?
 
Simonglencross said:
Joan is there any way I can implement this without have to scrap the
forms which I have done and starting again?
Click to expand...

There is no need to delete your existing forms and start over.

I'll assume your forms are based on tables. Create a RWOP query for each of
your tables. You can set the run permissions property of the query in
design view to Owners. This will give you a SQL statement like:

SELECT * FROM Orders WITH OWNERACCESS OPTION;

Change the recordsource of your form to the query. In design view of your
form, show the field list and drag the new OwnedBy field from the list to
your form. Set this control's default property to =CurrentUser() and hide
it if you wish.
 
Joan,

I had an unbound list box which displayed my active customers, you could
click once on one of the active customers and there details would populate
my center form but since making the currentuser() changes the list box is
now bound and has loat this functionality, how could I correct this?

Many Thanks

Simon
 
Simonglencross said:
Joan,

Whats a RWOP query? you've lost me a little here.
Click to expand...

Sorry Simon, that means 'run with owner permissions'. If you implement
security, you can change the 'run permissions' property of the query in
design view to 'Owner'. Doing so means that you can deny users all
permissions on the underlying tables, and give them permission on the query
instead. When they run the query, they are 'running it with the query
owner's permissions', meaning that they will have the permissions on the
tables that the owner has.

However, they are still restricted by the permissions they have on the
query.

For example, the query owner may have read/update/insert permission on the
table (not delete). They create a RWOP query based on this table, and give
users permission to read data on the query (note not update or insert or
delete). Users do not have permission on the underlying table. When they
run the query, they can read the data in the table (because they are running
the query as though the owner is, and the owner has read/update/insert
permission on the table). They are not able to update/insert/delete data in
the table, because they are still restricted based on the permission they
have on the query. Make sense?

Note that if the query owner gave delete permission on the RWOP query, users
still couldn't delete data, because the query owner doesn't have delete
permission on the table.

You can get more information on RWOP in the security FAQ and whitepaper.
Security FAQ
http://support.microsoft.com/?id=207793

Security Whitepaper
http://support.microsoft.com/?id=148555
 
Thanks a million Joan think I get where you are comming from I have created
RWOP's for all or the table and done as you explained I have not implemented
the sercurity yet but will be shortly or would you recommend doing that now?

I noticed that you have recently released a new book what sort of user is it
aimed at?

Regards

Simon
 
Simonglencross said:
Joan,

I had an unbound list box which displayed my active customers, you
could click once on one of the active customers and there details
would populate my center form but since making the currentuser()
changes the list box is now bound and has loat this functionality,
how could I correct this?
Click to expand...

Making the currentuser() changes should not have done this. You can just
delete the control source for the list box to make it unbound again.

There is something more happening here, though. What code is attached to
the unbound list box?
 
Simonglencross said:
Thanks a million Joan think I get where you are comming from I have
created RWOP's for all or the table and done as you explained I have
not implemented the sercurity yet but will be shortly or would you
recommend doing that now?
Click to expand...

Well the RWOP setting is pointless in an unsecured database. In this
setting you have been logged in as 'Admin', so that user is the owner of the
query.

When you implement security, you'll remove all permissions/ownership for the
Admin user, and you'll create a new user to be the owner. You'll want to
change the setting to owner while logged in as that user, and save the
queries.
I noticed that you have recently released a new book what sort of
user is it aimed at?
Click to expand...

I did? No wonder I'm so tired! <g> No I haven't written a book - what did
you see?
 
Back
Top