Add a field to an existing table using vba

[Sally Green]

In a userform I have used vba to run a make table query. I now need to add
an Autonumber field to this table that restarts at '1' each time the code is
run.

If I use the userform to make the table and then manually add my autonumber
field it always starts at 1, which is why I want to use 'Make Table' each
time rather than 'delete' and 'append' queries.

The only problem is I can't work out what must be a simple line of code, to
add a new field to this table.

i would appreciate any help anyone is kind enough to supply.

Many thanks

 

[Douglas J. Steele]

Why do you feel this is necessary? Autonumber fields exist for one purpose
only: to provide a (practically guaranteed) unique value that's suitable for
use as a primary key. No meaning should be attached to the value of the
autonumber field.

 

[Sally Green]

Thanks for your reply. the table data is sorted in date order and I then
want to use the autonumber values in a qry to extract records 1, 11, 21, 31,
41, 51 etc. To product a random list from the table for quality control
purposes.

Maybe there's an easier way of doing this?

Sally

 

[Sally Green]

I have been working off a response you made to 'traveler' and have tried to
adapt it for my cause but am getting an error on the tdf.Indexes.Append idx
line of code. the error is:
3409 invalid field definition 'Sequence' in defintion of index or
relationship.

If I rem out the lines
tdf.Indexes.Append idx
tdf.Indexes.Refresh

the code runs without error but the 'Sequence' field is not added to the
table.
I don't need to reference this table to other tables so I don't care if it's
not set as a primary key or indexed, I simply reuire sequential numbering.


The code I'm using is:

Dim db As Database
Dim tdf As DAO.TableDef
Dim idx As DAO.Index
Dim idxfld As DAO.Field

'code to add Sequence field
Set db = CurrentDb()
Set tdf = db.TableDefs("tblQuality3_1")

'Create a new primary key
Set idx = tdf.CreateIndex("PrimaryKey")
idx.Primary = True
idx.Required = True
idx.Unique = True

'Create the new index field
Set idxfld = idx.CreateField("Sequence")
idx.Fields.Append idxfld

'Append the index to the indexes collection
tdf.Indexes.Append idx

'Refresh the indexes collection
tdf.Indexes.Refresh

Set idx = Nothing
Set tdf = Nothing
Set db = Nothing

 

[Douglas J. Steele]

You could create a query that dynamically generates a Rank number (using
DCount("*", "MyTable", "MyDateField <=" & Format(MyDateField,
"\#mm\/dd\/yyyy\#")). You can then work with that query.

MakeTable queries aren't all that common in well-designed applications.

 

[Douglas J. Steele]

That code you've posted assumes that the field Sequence already exists in
the table, and simply creates an index based on it. It does not create the
field Sequence.

To add a field to the table, you need to use the CreateField method of the
TableDef object, not of the Index object.

 

[Sally Green]

Thank you. However, some of the dates are identical. so in a dynaset of 7
records I'm getting as their rank number 2,2,3,5,5,6,7

 

[Sally Green]

Sorry Douglas. I can't get this to work either. Is my delcarations section
wrong? I've tried fld as Variant, as Object and as DAO.Field but error out
on this line:
tdf.Fields.Append fld

Dim db As Database
Dim tdf As DAO.TableDef
Dim fld As Object

'code to add Sequence field
Set db = CurrentDb()
Set tdf = db.TableDefs("tblQuality3_1")
Set fld = tdf.CreateField("Sequence")
tdf.Fields.Append fld

What determines the data type on the new field 'Sequence' to be AutoNumber?

Regards
Sally

 

[Sally Green]

OK. This code is finally adding the field called 'Sequence' as a long
integer but not defining it as an AutoNumber.

Any suggestions?

Dim db As Database
Dim tdf As TableDef
Dim fld As Field

'code to add Sequence field
Set db = CurrentDb()
Set tdf = db.TableDefs("tblQuality3_1")
Set fld = tdf.CreateField("Sequence", dbLong)
tdf.Fields.Append fld

Set tdf = Nothing
Set db = Nothing

Thanks
Sally

 

[Douglas J Steele]

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

'code to add Sequence field
Set db = CurrentDb()
Set tdf = db.TableDefs("tblQuality3_1")
Set fld = tdf.CreateField("Sequence", dbLong)
fld.Attributes = fld.Attributes + dbAutoIncrField
tdf.Fields.Append fld

Set tdf = Nothing
Set db = Nothing

I still say it's a mistake, though, but it's your decision...