Mutilple Users

[J. McGonigal]

My job is at a 24/7 facility that is partnered with the local police
department. My Access knowledge grows while messing around with the program,
but I have had no formal training. We currently use an old DOS based program
that runs many of our applications. Because of it being based in DOS we have
run into many problems through the years caused by its old roots. I somehow
have been assigned to try and reproduce this program by using Access (for
some reason I’ve become the Tech guy, even though it’s a hobby).

My questions:

I’m pretty sure its possible to have different logins for different users
and the set permissions for each of them within Access. Is this correct?

When entering a report is there a way to automatically enter the name of the
user that is logged in? (I’ve read that this may be done by currentuser() ).

How stable is Access? The file will would be stored on a network drive and
have 4-6 people accessing it at once. Is this even possible? It is not very
often that two reports will be written at the same time, but it can happen.
Would this cause a problem? Would every individual be able to have a separate
login and access the database at the same time?


I know there are a lot of questions and I greatly appreciate any assistance
given.


Justin

 

[Allen Browne]

Answers in-line

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

My job is at a 24/7 facility that is partnered with the local police
department. My Access knowledge grows while messing around with the
program,
but I have had no formal training. We currently use an old DOS based
program
that runs many of our applications. Because of it being based in DOS we
have
run into many problems through the years caused by its old roots. I
somehow
have been assigned to try and reproduce this program by using Access (for
some reason I've become the Tech guy, even though it's a hobby).

Not an uncommon story of how you end up in Access.

My questions:

I'm pretty sure its possible to have different logins for different users
and the set permissions for each of them within Access. Is this correct?

Yes. Read anything on "security" in Access, or system.mdw.

When entering a report is there a way to automatically enter the name of
the
user that is logged in? (I've read that this may be done by
currentuser() ).

Yes. You can put a text box on your report, and set its Control Source to:
=CurrentUser()
If you have no security set up, that will show Admin.
If you user names and passwords, it will show the user name.
If you wanted to use the user's Windows logon name, see:
http://www.mvps.org/access/api/api0008.htm

How stable is Access? The file will would be stored on a network drive and
have 4-6 people accessing it at once. Is this even possible? It is not
very
often that two reports will be written at the same time, but it can
happen.
Would this cause a problem? Would every individual be able to have a
separate
login and access the database at the same time?

No problem. Make sure you split the database into 2:
- the back end file, stored in a shared location, containing just the
tables;
- the front end application, installed onto each user's machine, containing
linked tables and the rest of the application. More info:
http://allenbrowne.com/ser-01.html

I know there are a lot of questions and I greatly appreciate any
assistance
given.

Yes, you are very welcome to ask clear, specific questions like these.

 

[DataMouse.biz]

As usual, Allen has it bang on (again)
and the set permissions for each of them within Access. Is this correct?

Yes it is. Access a User-Level Security built in.
It is a little fiddly to get to grips with at first - so practice on a copy.
It creates a workgroup file with a default name of "system.mdw". Change this
when you create yours, incase it gets overridden.
And, as always, practice on a copy of your database!.

user that is logged in? (I’ve read that this may be done by currentuser() ).

You're correct - or you can use Allens API call and use =fosUserName()

It can be very stable. Database design is more an art than a science. The
better your db is built, the more stable it will be.
have 4-6 people accessing it at once. Is this even possible?

Again, yes. Access has a theoritcal user limit of 255 - though you'll
usually start to see performance issues around a 10th of that figure.
For 4-6 users, it'll be a walk in the park.

Not necessarily. But if you want to apply user level authorisation, then
each user would be "logged in" seperately. This doesn't have to require the
user to phyically login with username and password.
It can be done in the background too.

Danny

 

[Douglas J. Steele]

They forgot to tell you that the best place to start learning about applying
Access User-Level Security is at
http://support.microsoft.com/support/access/content/secfaq.asp

Read it carefully several times before starting. Work on a copy of your
database (just in case). Make sure you don't skip any steps. There's a
newsgroup related specifically to security
(microsoft.public.access.security) if you have any questions.

 

[J. McGonigal]

Thanks everyone for your responses. I'm still in the design phase of my
database and plan on finishing that first. Is finishing this first before
working on security/ splitting the database a good idea?

Also I'm having real trouble making a form/user interface for the main
input. Does anyone have any suggestions? I've tried the wizards but things
just don't come out right. I've also attempted to "freehand" them, but with
little success. Would anyone be willing to take a look at the database and
giving advice on the one form?

Thanks again,
Justin

 

[TC]

You shouldn't be worried about the forms, unless & until you are
certain that the database structure is correct. Often it isn't - which
makes the rest of the process hard to complete.

I suggest you post the definition of each table, so we can verify them.
For each table, show the name, and the name of the main fields. (You
need not show the types & lengths.) Be sure to clearly identify the
primary key field(s) of each table.

Believe me: if the table structure is correct, everything else will
fall out in the wash. But if the table structure is /not/ correct,
everything else will be difficult or impossible to get working
properly. Database design must start with the tables - not with the
forms!

HTH,
TC

 

[J. McGonigal]

TC,
I just emailed you, if you don't mind taking a look at that and advising it
would be greatly appreciated.

Justin

 

[TC]

Sorry Justin, no can do, for two reasons.

First, the purpose of the newgoups is for everyone to benefit from the
answers given. For that to work, all the relevant infomnation must be
in the group, for all to see. Creating a "back channel" of infomation,
via email, subverts this aspect of how it all works.

Second, as unpaid volunteers, we do not have any obligations. Any of us
can answer, or not answer, whenever we want, at our discretion. But as
soon as we start accepting files by email, this establishes an
obligation on us to reply. That is not appropriate, when we are working
for free. I believe that some posters do offer an individual support
service of some kind, but they charge for that service.

Not trying to be nasty; them's just the rules of the game. I think
you'll find that the other regular posters would all agree with this.

So, as I suggested before, try describing your table structure (in a
newgroup post!) & we can take a look at it.

Sorry I took a while to reply. I hit my goog posting limit last night,
and forgot the password to the other account that I have for that
purpose. And the passwor reset didn't work!

HTH,
TC

 

[J. McGonigal]

No problem.

Basically I have a bunch of tables to provide information for lookups. I
have 11 tables that are setup like this. They only have one or two columns a
piece.

My main table is titled report. It contains 45 inputs. Most of the inputs
are coming from the 11 other tables that are setup to lookup information.

I'm not sure what other information you guys need. I also aplogize for not
using proper terms etc.

Thanks

 

[TC]

Ok, from that description, your table structure is almost certainly not
correct.

In a relational database like Access, you do not populate a main table
with inputs from other tables. Instead, each logically seperate set of
data, should have its own table. None of the data in any table, should
be duplicated, or "input to", any other table, except for special
values called primary keys. Then you use queries to pull the data out,
from the various seperate tables, for use on forms & reports.

The key thing to understand is, that you /do not/ design your tables to
reflect the visual appearance of the screens and reports. That sounds
logical, but in fact, it is doing things the wrong way around.

For example: an Invoice is a single thing from an end-user viewpoint;
but Invoices typically require /at least 4 tables/ to store them with
the proper structure - not one table.

Tell us something about the data that you need to store. /Do not/ use
the word "table" in that. For example: "I need to store customer
invoices. Each invoice shows ... etc." Not: "I have data in one table
X, then I have another table Y that ... etc.".

HTH,
TC

 

[J. McGonigal]

I need to store the following information:

case number
date
time
camera number
camera location
location of incident
vehicle information (color, state, # occupants, make model)
Yes/no questions (arrest, referral, etc.)
and then a large text area w/ a synopsis

 

[TC]

Ok. That makes a bit of a dope of me, because all those fields look
quite ok, to have in a single table! All of them store data that is
specific to the Case in question. So it's fine to have them all in the
same table. Sorry, I expected something different.

So, getting back to your original question: "Also I'm having real
trouble making a form/user interface for the main input." I assume you
mean, a form for the table described above? If so, I would try a main
form with a tab control that had two pages. I'd put the case number
field outside the tab control; perhaps in the form header section, or
at the top of the form detail setion. I'd put all of the remaining
fields /except/ the notes, on the first page of the tab control. I'd
put the notes field as a big box covering the seond page of the tab
control.

Then, the user could click the first page to view or enter all the
fields except for the notes, and/or click the second page to view or
enter the notes.

Is that what you're looking for? I feel that I am missing something ...

HTH,
TC

 

[TC]

PS. Perhaps what I'm missing, is how the lookup tables that you
menioned before, fit into the scheme.

Take a lookup table for Locations. That table would look something like
this:

tblLoction
LocationID autonumber primary key
LocationName text
(maybe other fields)

Then, in you Case table, you'd have a field for the LocationID of the
location for that case:

tblCase
CaseNumber primary key
LocationID long integer

Then, in the form, the location field would be a combo box. So, when
the user selected a location from the combo list, this would store the
relvant LocationID value into the Case record.

In the Relationships window, you should create a "ne o many" enforced
relationship from LocationID in tblLocation, to LocationID in the Case
table.

Does that help?

HTH,
TC

 

[J. McGonigal]

Sorry I took so long to respond, I was given several other non-computer
related projects to work on. I'm not sure I've explained myself to well. You
can find a screenshot of what I'm attempting to replace at:
http://home.comcast.net/~jmcdougal02/SOC.JPG

This is basically it, except the page goes down two more times for the
synopsis part.

Basically what I did was create several tables. They include:
Area of campus, Camera #, Incident Code, Race, Report, Sex, States, Vehicle
Color, Vehicle Male, and Vehicle Type. Within the table of report I basically
have all the tables come togeather. I used the option in the design mode for
"lookup wizard" to create many of the other tables.

An example is for Vehicle Make, I have all the makes of cars etc.

Please let me know what needs to be redone or if this is ok.