How do I create the field - ending up with an autonumber/incremented randomly?

[rg]

I am using Set fldTmp = tdfTmp.CreateField(strField, 4,4) to create a field.

I need the field to be an autonumber and incremented randomly.

What do I need to change this to?

TIA,

Roy

 

[Allen Browne]

Roy, I don't have an answer for you.
If you get one, I'm interested.

DAO does not expose a property or attribute for "random", and I don't see
anything in ADOX either.

If you walk the properties of a field set to random autonumber, you see its
DefaultValue is:
GenUniqueID()
But attempting to create a new field with that property fails without error.
Therefore the code below does *NOT* work as expected on an existing table
named tblRandom, with no autoum:

Function RandomKey()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb
Set tdf = db.TableDefs("tblRandom")
Set fld = tdf.CreateField("RandomID", dbLong)
fld.Attributes = dbAutoIncrField + dbFixedField
'The next line does *NOT* work:
fld.DefaultValue = "GenUniqueID()"
tdf.Fields.Append fld
End Function

If you manually create a random autonumber named ID in a table named Table1,
you can walk its properties with:
? ShowProps(Currentdb.TableDefs("Table1").Fields("ID"))

Function ShowProps(obj As Object) As Long
On Error GoTo Err_Handler
Dim prp As DAO.Property
'Dim fld As DAO.Field
'Debug.Print fld.Attributes
For Each prp In obj.Properties
Debug.Print prp.Name,
Debug.Print prp.Value;
Debug.Print
Next
Set prp = Nothing

Exit_Handler:
Exit Function

Err_Handler:
Select Case Err.Number
Case 3219, 3267, 3251
Resume Next
Case Else
MsgBox "Error " & Err.Number & " - " & Err.Description,
vbExclamation, "ShowProps()"
Resume Exit_Handler
End Select
End Function

 

[Jamie Collins]

Roy, I don't have an answer for you.
If you get one, I'm interested.

DAO does not expose a property or attribute for "random", and I don't see
anything in ADOX either.

If you walk the properties of a field set to random autonumber, you see its
DefaultValue is:
GenUniqueID()

You were nearly there, Allen. You stopped short of trying SQL DDL e.g.

Sub AllenRand()
'Kill "C:\DropMe.mdb"
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb"
With .ActiveConnection
.Execute _
"CREATE TABLE test (" & _
"ID INTEGER DEFAULT GenUniqueID() NOT NULL," & _
" data_col INTEGER NOT NULL UNIQUE);"

Dim counter As Long
For counter = 1 To 9
.Execute _
"INSERT INTO test (data_col) VALUES (" & _
CStr(counter) & ");"
Next

Dim rs
Set rs = .Execute( _
"SELECT ID, data_col FROM test;")

MsgBox rs.GetString

End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--

 

[Jamie Collins]

CREATE TABLE test
ID INTEGER DEFAULT GenUniqueID() NOT NULL,
data_col INTEGER NOT NULL UNIQUE);

FWIW the syntax for an autogenerating GUID is e.g.

CREATE TABLE test2 (
ID GUID DEFAULT GenGUID() NOT NULL,
data_col INTEGER NOT NULL UNIQUE);

Jamie.

--

 

[Jamie Collins]

You were nearly there, Allen. You stopped short of trying SQL DDL

The following article suggests it is possible with DAO. See:

How to programmatically create an AutoNumber field and set its "New
Values" property to Random

http://support.microsoft.com/kb/304418

Jamie.

--

 

[Allen Browne]

Jamie, thank you for that. I needed to swap my last 2 lines. Should have
remembered Default Value must be set after the Field is appended to the
TableDef.

Which hints at an interesting possibility. If you try to change an
incremental autonumber to Random (or vice versa) in the table design
interface, Access (appropriately) stops you. However you can do it with:
Currentdb.TableDefs("Table1").Fields("ID").DefaultValue =
"GenUniqueID()"
or
Currentdb.TableDefs("Table1").Fields("ID").DefaultValue = ""

The Seed isn't set, so it would be good to take care of that also.

Thanks again for contributing the solution.

 

[John Vinson]

"CREATE TABLE test (" & _
"ID INTEGER DEFAULT GenUniqueID() NOT NULL," & _
" data_col INTEGER NOT NULL UNIQUE);"

Thanks Jamie. Haven't actually had to use this (I rarely create tables
in code) but it's good to know!

One question I've had about Random autonumbers: does the algorithm
that generates them check first for duplicates, or does it just count
on the low probability that a random 32-bit integer will be a
duplicate? The SQL Upsizing wizard generates the following trigger
(posted verbatim as generated by the wizard):

CREATE TRIGGER T_CONtblPeople_ITrig ON [CONtblPeople] FOR INSERT AS
SET NOCOUNT ON
DECLARE @randc int, @newc int /* FOR AUTONUMBER-EMULATION CODE */
/* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'ContactID' */
SELECT @randc = (SELECT convert(int, rand() * power(2, 30)))
SELECT @newc = (SELECT ContactID FROM inserted)
UPDATE CONtblPeople SET ContactID = @randc WHERE ContactID = @newc

I'm rather rusty at T-SQL but I don't see anything in this code that
either checks for duplicates, or retries with a new random number if a
primary key constraint gets violated. What's the trick?

John W. Vinson[MVP]

 

[David W. Fenton]

One question I've had about Random autonumbers: does the algorithm
that generates them check first for duplicates, or does it just
count on the low probability that a random 32-bit integer will be
a duplicate?

I would assume that it does, but maybe not. It's probably faster to
handle the error on a duplicate generated value than it would be to
check each value against existing values.

In any event, it's not a noticeable performance issue by itself in
replicated apps (the only context where I've used random autonumbers
-- I've contemplated using them for performance reasons to increase
concurrency by spreading the edited records over the whole set of
data pages and index pages storing the table's data), where the
whole package of changes introduced by replication are the source of
the minor performance drain (even that is not enough to matter in
anything but an app that is already so overburdened as to need
redesign).

 

[Jamie Collins]

One question I've had about Random autonumbers: does the algorithm
that generates them check first for duplicates, or does it just count
on the low probability that a random 32-bit integer will be a
duplicate?

John, Good question, one which was asked in one of the Access groups
earlier this week. I do not know the answer for Access/Jet's autonumber
implementation...

The SQL Upsizing wizard generates the following trigger
(posted verbatim as generated by the wizard):

CREATE TRIGGER T_CONtblPeople_ITrig ON [CONtblPeople] FOR INSERT AS
SET NOCOUNT ON
DECLARE @randc int, @newc int /* FOR AUTONUMBER-EMULATION CODE */
/* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'ContactID' */
SELECT @randc = (SELECT convert(int, rand() * power(2, 30)))
SELECT @newc = (SELECT ContactID FROM inserted)
UPDATE CONtblPeople SET ContactID = @randc WHERE ContactID = @newc

I'm rather rusty at T-SQL but I don't see anything in this code that
either checks for duplicates, or retries with a new random number if a
primary key constraint gets violated. What's the trick?

The trick is to store the last generated value and use it as a seed in
an algorithm that will generate numbers that are a) not repeated until
a large number of iterations have completed and b) ensure consecutively
generated numbers are far apart (e.g. good for concurrency). I'm away
from the office so I can't provide a link but a google search that
includes the exact phrase "additive congruential" should turn up some
relevant material e.g. try these for keywords:

random "additive congruential" mod

Merry xmas,
Jamie.

--

 

[Jamie Collins]

Thanks again for contributing the solution.

Cheers Allen, It was you mentioning GenUniqueID() that reminded me of
GenGUID() and the DDL...

Have a good Christmas,
Jamie.

--

 

[John Vinson]

The trick is to store the last generated value and use it as a seed in
an algorithm that will generate numbers that are a) not repeated until
a large number of iterations have completed and b) ensure consecutively
generated numbers are far apart (e.g. good for concurrency). I'm away
from the office so I can't provide a link but a google search that
includes the exact phrase "additive congruential" should turn up some
relevant material e.g. try these for keywords:

random "additive congruential" mod

Ah. So it's a "self avoiding" pseudorandom number. Makes sense!
Thanks!

John W. Vinson[MVP]