Check for user name already in secure database

[Therese A. Sorna]

Hello all...

I am using Access 2002. I have database set to add users names to the
user level security when they are added to a particular table in a
database, using the Catalog and ActiveConnection commands. I am
getting errors when I try to add a name that is already in the
database. I was wondering if anyone knew of a way to have it check
for the user's name before going to add it? One method would be to
delete everytime before adding, but that seems like it would cause
problems as well.

Thanks in advance,
Therese

[Larry Linson]

Can you not simply field the error using On Error GoTo ... and an
error-handling routine? If not, please be specific about the error, and the
exact circumstances under which you are getting it and perhaps someone will
have suggestions.

It is often much simpler to just deal with an error rather than trying to
avoid it.

Larry Linson
Microsoft Access MVP

"Therese A. Sorna" <ta*****@yahoo.com> wrote in message
news:be**************************@posting.google.c om...

Hello all...

I am using Access 2002. I have database set to add users names to the
user level security when they are added to a particular table in a
database, using the Catalog and ActiveConnection commands. I am
getting errors when I try to add a name that is already in the
database. I was wondering if anyone knew of a way to have it check
for the user's name before going to add it? One method would be to
delete everytime before adding, but that seems like it would cause
problems as well.

Thanks in advance,
Therese

[Dorothy Moody]

"Therese A. Sorna" <ta*****@yahoo.com> wrote in message
news:be**************************@posting.google.c om...

Hello all...

I am using Access 2002. I have database set to add users names to the
user level security when they are added to a particular table in a
database, using the Catalog and ActiveConnection commands. I am
getting errors when I try to add a name that is already in the
database. I was wondering if anyone knew of a way to have it check
for the user's name before going to add it? One method would be to
delete everytime before adding, but that seems like it would cause
problems as well.

Thanks in advance,
Therese

Best place to get answers is at the official MVP site:
http://mvp.org
Dorothy Moody MVP

[Doug M]

"Dorothy Moody" <s2******@aol.com> wrote in message
news:fc******************************@news.teranew s.com...

Best place to get answers is at the official MVP site:
http://mvp.org
Dorothy Moody MVP

NOT!!!

[TC]

"Therese A. Sorna" <ta*****@yahoo.com> wrote in message
news:be**************************@posting.google.c om...

Hello all...

I am using Access 2002. I have database set to add users names to the
user level security when they are added to a particular table in a
database, using the Catalog and ActiveConnection commands. I am
getting errors when I try to add a name that is already in the
database. I was wondering if anyone knew of a way to have it check
for the user's name before going to add it?
No idea with ADO. But with DAO:

dim s as string
on error resume next
s = dbengine(0).users("Fred").name
if err.number <> 0 then s = ""
on error goto 0

if s = "" then
' user not defined.
else
' user is defined.
endif

One method would be to delete everytime before adding,
but that seems like it would cause problems as well.

Gak! Don't do that. It would be doomed to failure unless you used the exact
same PID value that was used when the user was created originally. And you
can not get the PID from Access or Jet (so you'd have to get it by some
other means). I do mean PID, not user-level password.

HTH,
TC

[Therese A. Sorna]

The code worked great with minor changes (DAO vs ADO). Basically just
replaced the dbengine(0) with cat, and off I was! The key was the 'on
error resume next'.

Thanks!
T

"TC" <a@b.c.d> wrote in message news:<1066802883.653492@teuthos>...

"Therese A. Sorna" <ta*****@yahoo.com> wrote in message
news:be**************************@posting.google.c om...

Hello all...

I am using Access 2002. I have database set to add users names to the
user level security when they are added to a particular table in a
database, using the Catalog and ActiveConnection commands. I am
getting errors when I try to add a name that is already in the
database. I was wondering if anyone knew of a way to have it check
for the user's name before going to add it?

No idea with ADO. But with DAO:

dim s as string
on error resume next
s = dbengine(0).users("Fred").name
if err.number <> 0 then s = ""
on error goto 0

if s = "" then
' user not defined.
else
' user is defined.
endif

One method would be to delete everytime before adding,
but that seems like it would cause problems as well.

Gak! Don't do that. It would be doomed to failure unless you used the exact
same PID value that was used when the user was created originally. And you
can not get the PID from Access or Jet (so you'd have to get it by some
other means). I do mean PID, not user-level password.

HTH,
TC

[Bradley]

It would make for better code if you did deal with cause rather than just
trap the error (even if it's not simpler) :)

You can loop through all the users and try to find a match first before
adding one. If you need sample code let me know.
--
Bradley
Software Developer www.hrsystems.com.au
A Christian Response www.pastornet.net.au/response

"Larry Linson" <bo*****@localhost.not> wrote in message
news:RE******************@nwrddc01.gnilink.net...

Can you not simply field the error using On Error GoTo ... and an
error-handling routine? If not, please be specific about the error, and the exact circumstances under which you are getting it and perhaps someone will have suggestions.

It is often much simpler to just deal with an error rather than trying to
avoid it.

Larry Linson
Microsoft Access MVP

"Therese A. Sorna" <ta*****@yahoo.com> wrote in message
news:be**************************@posting.google.c om...

Hello all...

I am using Access 2002. I have database set to add users names to the
user level security when they are added to a particular table in a
database, using the Catalog and ActiveConnection commands. I am
getting errors when I try to add a name that is already in the
database. I was wondering if anyone knew of a way to have it check
for the user's name before going to add it? One method would be to
delete everytime before adding, but that seems like it would cause
problems as well.

Thanks in advance,
Therese

[Therese A. Sorna]

Sure, I would much rather do that than to trap an error. Please
provide sample code.

T

"Bradley" <br*****@REMOVETHIScomcen.com.au> wrote in message news:<3f******@nexus.comcen.com.au>...

It would make for better code if you did deal with cause rather than just
trap the error (even if it's not simpler) :)

You can loop through all the users and try to find a match first before
adding one. If you need sample code let me know.
--
Bradley
Software Developer www.hrsystems.com.au
A Christian Response www.pastornet.net.au/response

"Larry Linson" <bo*****@localhost.not> wrote in message
news:RE******************@nwrddc01.gnilink.net...

Can you not simply field the error using On Error GoTo ... and an
error-handling routine? If not, please be specific about the error, and

the

exact circumstances under which you are getting it and perhaps someone

will

have suggestions.

It is often much simpler to just deal with an error rather than trying to
avoid it.

Larry Linson
Microsoft Access MVP

"Therese A. Sorna" <ta*****@yahoo.com> wrote in message
news:be**************************@posting.google.c om...

Hello all...

I am using Access 2002. I have database set to add users names to the
user level security when they are added to a particular table in a
database, using the Catalog and ActiveConnection commands. I am
getting errors when I try to add a name that is already in the
database. I was wondering if anyone knew of a way to have it check
for the user's name before going to add it? One method would be to
delete everytime before adding, but that seems like it would cause
problems as well.

Thanks in advance,
Therese

[Larry Linson]

"Better" code _how_, Bradley? Esthetically more pleasing? Less work for the
developer? Less work for the user?

Often, when you have the capability of trapping an error and dealing with
it, that is the most direct, simplest, and easiest approach, and the user
never knows that you didn't "avoid" it... it's not like you actually add a
duplicate and the user stumbles over it later.

It'd be something like testing for the error number in your error handling,
giving the user a MsgBox (same as you would in an avoidance situation)
saying "duplicate name, please change", and returning to the input screen
(same as you would in an avoidance situation).

What you _don't_ have to do is have code to run through a hierarchy of
containers, collections, and properties to be able to determine that you
need to give the user that very same MsgBox and return to that very same
form.

I really do not understand how you define "more work", "more code", and
"more resources used" to accomplish exactly the same result as "better
code".

Larry Linson
Microsoft Access MVP

"Bradley" <br*****@REMOVETHIScomcen.com.au> wrote in message
news:3f******@nexus.comcen.com.au...

It would make for better code if you did deal with cause rather than just
trap the error (even if it's not simpler) :)

You can loop through all the users and try to find a match first before
adding one. If you need sample code let me know.
--
Bradley
Software Developer www.hrsystems.com.au
A Christian Response www.pastornet.net.au/response

"Larry Linson" <bo*****@localhost.not> wrote in message
news:RE******************@nwrddc01.gnilink.net...

Can you not simply field the error using On Error GoTo ... and an
error-handling routine? If not, please be specific about the error, and

the

exact circumstances under which you are getting it and perhaps someone

will

have suggestions.

It is often much simpler to just deal with an error rather than trying to avoid it.

Larry Linson
Microsoft Access MVP

"Therese A. Sorna" <ta*****@yahoo.com> wrote in message
news:be**************************@posting.google.c om...

Hello all...

I am using Access 2002. I have database set to add users names to the
user level security when they are added to a particular table in a
database, using the Catalog and ActiveConnection commands. I am
getting errors when I try to add a name that is already in the
database. I was wondering if anyone knew of a way to have it check
for the user's name before going to add it? One method would be to
delete everytime before adding, but that seems like it would cause
problems as well.

Thanks in advance,
Therese

[TC]

"Bradley" <br*****@REMOVETHIScomcen.com.au> wrote in message
news:3f******@nexus.comcen.com.au...

It would make for better code if you did deal with cause rather than just
trap the error (even if it's not simpler) :)

You can loop through all the users and try to find a match first before
adding one. If you need sample code let me know.

Nonsense. Why loop through 'x' number of users, when a single statement
(preceded by an On Error) will do exactly what he wants?

If you need to see whether a certain value exists for an indexed field in a
table, do you loop through every record in the table, looking for that
value? Nope. You try to find the value in question, & trap the error if it
aint there.

Same case here.

TC