how do i setup a field in office access to automatically assign 4.

[Guest]

hi,
I have this quesion.. i have jus started using Access, and im sure a lot
of ppl would know to do this. i want to create a field "employee ID" and i
have to assign 4 digit numeris random numbers. can somebody help me on this
please. Thanks. I greatly appreciate.

 

[Tim Ferguson]

i have jus started using Access, and im sure a lot
of ppl would know to do this. i want to create a field "employee ID"
and i have to assign 4 digit numeris random numbers. can somebody help
me on this please. Thanks. I greatly appreciate.

The easy way: use an autonumber and put up with the fact that it may be
more than four digits. Random autonumbers can be negative too.

The slightly harder way: first make sure that all your users can only use
your forms to create new records. Put code on the form to do this:

' set up a loop
Do
' Create a new random four-digit number e.g.
wNewNum = Int(Rnd() * 10000) ' can only be max 9999

' Try again unless it's already been used
Loop While 0 < DCount("*", "MyTable", "EmployeeID = " & wNewNum )


' Then insert the record into the table
strSQL = "INSERT INTO MyTable (EmployeeID) " & _
"VALUES (" & wNewNum & ") "
db.Execute strSQL, dbFailOnError


That will work until you have 10000 records in the database. It's also
not safe in a multiuser set up; you'll have to use a slightly different
approach if there is any chance of a collision.

HTH

Tim F

 

[Jamie Collins]

The slightly harder way: first make sure that all your users can only use
your forms to create new records.

I'm genuinely curious as to how this could be achieved. Assuming I have
all the necessary (e.g. a userID and password for the workgroup file,
plus Jet, ADO and Excel) and I know how to use them, what steps would
you take to ensure I can't add a row to the table without using your
Form?

Many thanks,
Jamie.

--

 

[Rick Brandt]

I'm genuinely curious as to how this could be achieved. Assuming I
have all the necessary (e.g. a userID and password for the workgroup
file, plus Jet, ADO and Excel) and I know how to use them, what steps
would you take to ensure I can't add a row to the table without using
your Form?

Many thanks,
Jamie.

Deny permissions to the table and use RWOP query in the form.

 

[Tim Ferguson]

I'm genuinely curious as to how this could be achieved.

Well, you can't really. If you have users that will do what they are told
and not explore, then you can hide all the UI elements like the database
window, menus and so on that might make them want to attack the tables
directly.

With knowledgeable users who are willing and able to write their own
queries, use excel and vba to manipulate the data and so on, then you are
pretty much at their mercy. If they are _really_ knowledgeable, then you
can publish the interface so they know how to create new id numbers
correctly.

The Access Security can do it, so that all users are locked out of
everything except what you give permission to (see Rick's post) but
there's a big learning curve for someone who has just started with
Access.

All the best


Tim F

 

[Jamie Collins]

Deny permissions to the table and use RWOP query in the form.

The word RWOP means nothing to me (is that part of the plan <g>?) I
guess you are alluding to WITH OWNER ACCESS, in which case I prefer
Tim's approach e.g. create a stored PROCEDURE that will serve up a
valid ID or, perhaps better, actually create the new entity so that
non-MS Access users have way of using the database as the designer
intended.

Jamie.

--

 

[Tim Ferguson]

The word RWOP means nothing to me (is that part of the plan <g>?) I
guess you are alluding to WITH OWNER ACCESS, in which case I prefer
Tim's approach

RWOP = Run With Owner Permissions.

Actually, that _was_ Tim's approach: it's the one that uses Access tools
in the way they were intended.

e.g. create a stored PROCEDURE that will serve up a

valid ID or, perhaps better, actually create the new entity so that
non-MS Access users have way of using the database as the designer
intended.

A stored procedure is a RDBMS term and is only relevant if you are using
SQL Server or MySQL etc to host the data: in which case nearly everything
upthread of this is irrelevant because the security arrangements in the
database itself will be able to do everything you need without using
Access features at all.

Hope that helps


Tim F

 

[Jamie Collins]

RWOP = Run With Owner Permissions.

Actually, that _was_ Tim's approach

I know it was, otherwise I wouldn't have backed your approach i.e. to
provide a global method to create an entity/ID rather than burying it
in a Form.

it's the one that uses Access tools
in the way they were intended.

A stored procedure is a RDBMS term and is only relevant if you are using
SQL Server or MySQL etc to host the data: in which case nearly everything
upthread of this is irrelevant because the security arrangements in the
database itself will be able to do everything you need without using
Access features at all.

Slow down, please: you covered a lot of ground in 1.5 sentences, there.


When you say 'security arrangements in the database itself' I assume
you mean 'Jet security features' and when you make a distinction about
'Access tools' or 'Access features', I assume you mean 'non-Jet
security features'.

I only really know Jet; I've hardly used MS Access at all. I can do the
following using Jet 4.0 (via the OLE DB provider) alone:

Connected as admin:

CREATE TABLE Pilots (
pilot_ID VARCHAR(10) NOT NULL PRIMARY KEY,
lname VARCHAR(35) NOT NULL,
fname VARCHAR(35) NOT NULL,
mname VARCHAR(35) DEFAULT '{{NA}}' NOT NULL,
CHECK (pilot_ID LIKE
'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
)
;
INSERT INTO Pilots VALUES ('1234567890', 'Norarules', 'a', 'b')
;
REVOKE ALL PRIVILEGES ON TABLE Pilots FROM Developers
;
CREATE PROCEDURE GetPilot (
arg_pilot_id CHAR(10)
) AS
SELECT pilot_ID, lname, fname, mname
FROM Pilots
WHERE pilot_id = arg_pilot_id
WITH OWNERACCESS OPTION
;

Connected as developer:

SELECT * FROM Pilots; -- error

EXECUTE GetPilot '1234567890'; -- no error

The above seems to cover all security features we've mentioned/alluded
to in this thread. So what are the MS Access security features (non-Jet
security features) you are referring to?

And what exactly is your objection to the term 'stored procedure' in
relation to MS Access/Jet? I usually use the term PROCEDURE, in
capitals to reflect the Jet syntax but lowercase shouldn't cause you a
problem, and they are stored in the database, after all. What does an
MS Access user call them? What should a Jet user call them so that a MS
Access user knows what they are (I was using 'stored procedure' because
it is the generally accepted term)?

Thanks again,
Jamie.

--

 

[Tim Ferguson]

When you say 'security arrangements in the database itself' I assume
you mean 'Jet security features'

No: I mean security and authentication within SQL Server etc.

I only really know Jet; I've hardly used MS Access at all. I can do the
following using Jet 4.0 (via the OLE DB provider) alone:

Remember this is an Access list, not a VB one, so it's natural to assume
that people are asking questions about Access. I have to admit that I
don't have a lot of experience with DDL and SDL on ADO since the Access
UI makes both of them _almost_ redundant in this environment.

The above seems to cover all security features we've mentioned/alluded
to in this thread.

Well no: you've missed the bits about defining the users and groups --
jumping straight to "as developer" which misses out all the hard bits. At
the same time, you clearly do understand these issues so we seem to be in
violent agreement about the rest...

And what exactly is your objection to the term 'stored procedure' in
relation to MS Access/Jet? I usually use the term PROCEDURE,

Only that the words Query and Querydef is ubiquitous in Access literature
and procedure doesn't appear anywhere. Its use in the DDL is new, to me
at least, and appears to be an ADO bolt-on. If you mean a spade, using
words like scalpel is likely to throw listeners off.


All the best


Tim F

 

[Jamie Collins]

Remember this is an Access list, not a VB one, so it's natural to assume
that people are asking questions about Access.

Even the people that know better use the term 'Access' when they mean
'Jet', so it's hard for me to make assumptions. And who mentioned VB?

Well no: you've missed the bits about defining the users and groups --
jumping straight to "as developer" which misses out all the hard

bits.

CREATE GROUP Developers
;
CREATE User Jamie hispassword
;
ADD User Jamie TO Developers
;

the words Query and Querydef is ubiquitous in Access literature
and procedure doesn't appear anywhere.

The issue I have with Query/Querydef is there seems to be no
distinction between PROCEDURE and VIEW.

Again, when you say 'Access literature' I'm left wondering if you
actually are blinkered in looking at the MS Access literature, and
perhaps the DAO literature, rather than the Jet literature as would be
reasonable given the subject matter e.g.

http://office.microsoft.com/en-us/assistance/HP010322191033.aspx

Its use in the DDL is new, to me
at least, and appears to be an ADO bolt-on

It was introduced in Jet 4.0, so it's been around for at least five
years and three MS Access versions. AFAIK the intention was to move Jet
syntax closer to entry level SQL-92 compliance or, more cynically,
closer to SQL Server's T-SQL syntax.

And it's not that ADO was enhanced, more that DAO was left behind.

If you mean a spade, using
words like scalpel is likely to throw listeners off.

From where I'm standing, everyone else calls it a spade and MS Access

users call it an EntrenchingDef.

From reading these ngs, it seems many people (not you, Tim) have a chip

on their collective shoulder about 'Access' being look down upon by
users of more capable DBMSs. You and I both know Jet is very capable
and doesn't deserve such poor treatment. I just think that if the
converts started using legitimate and portable terms such as 'stored
procedure' instead of inaccessible proprietary terms such as Querydef,
not to mention alienating themselves with such practices as prefixing
tables with tbl (as only MS Access users do), then Jet and consequently
MS Access would perhaps be taken more seriously.

Jamie.

--

 

[Jamie Collins]

By the way, did you ever get to solve your original problem about
allocating unique four-digit PKs?

Thanks but I'm not the OP! If I wanted to allocate identifiers in a
certain format I'd build a table of appropriate values and create a
procedure (lowercase <g>) for allocating them.

Jamie.

--