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

[zaid]

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

 

[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

 

[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

 

[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

 

[Tim Ferguson]

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 don't have a chip; but I do have some pretty realistic (sic) ideas
about Jet's and Access's shortcomings in relation to real RDBMS products.
Yes, Access+Jet does do some wonderful things and wipes the floor with
its obvious competitors like 4-D and Paradox etc. But it's not an
industrial strength RDBMS either.

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,

No: a sp is organised and optimised server-side and much more flexible; a
querydef is not the same thing at all. A view is closer to a querydef but
far more limited. I don't think you can translate querydefs into rdbms
structures, and that's why ADO manages them poorly (if at all?).

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.

I'm up for that one. There is a lot of sense talked about variable naming
conventions in VB and VBA, but I lose the plot when it spills over into
database objects. But I also know that I am outnumbered on that one round
here <g>.

Access/Jet is taken seriously by many thousands of people who earn their
living producing commercial and bespoke solutions using them/it. There
will always be big IT department bods who only want big departmental
server solutions -- I'm really chuffed because I've got our hospital to
agree to distribute an Access ADP and they are about as snitty as they
can get. So the walls can be chipped at.

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

All the best


Tim F