Create autoincrement field

[David Mack]

I just wrote some code which removes exact duplicate records from an imported table. In order for it to work properly, I need to add an autonumber field manually to the table, remove the duplicate records, then delete the autonumber field.

Removing the field is easy via code. I need to know how to add it via code though. I have tried ALTER TABLE via sql and creating the field via the attached code. Unfortunately, I can create a field of type long integer, but can't make it autonumber/autoincrement (not a listed TYPE).

I noticed via the attached code (modified from the help facility) that Autonumber fields have an attribute of 17 set on them. I tried to alter this (see the ----> in the code to no avail.

Any assistance would be greatly appreciated. I would rather not import into a blank structure, becase the structure can vary.

Sub CreateField()

Dim db As Database
Dim tdfNew As TableDef
Dim fldLoop As field
Dim prpLoop As Property

Set db = CurrentDb() 'OpenDatabase("Northwind.mdb")

'Set tdfNew = db.CreateTableDef("Table1")
Set tdfNew = db.TableDefs("Table1")
' Create and append new Field objects for the new
' TableDef object.
With tdfNew

.Fields.Append .CreateField("IndexField1", dbLong)

End With

'db.TableDefs.Append tdfNew

Debug.Print "Properties of new Fields in " & tdfNew.Name

' Enumerate Fields collection to show the properties of
' the new Field objects.
For Each fldLoop In tdfNew.Fields
Debug.Print " " & fldLoop.Name



For Each prpLoop In fldLoop.Properties
If fldLoop.Name = "IndexField1" And prpLoop.Name = "Attributes" Then
'---> prpLoop.Value = 17
End If

' Properties that are invalid in the context of
' TableDefs will trigger an error if an attempt
' is made to read their values.
On Error Resume Next
Debug.Print " " & prpLoop.Name & " - " & _
IIf(prpLoop = "", "[empty]", prpLoop)
On Error GoTo 0
Next prpLoop

Next fldLoop

db.Close

End Sub



[This message has been edited by David Mack (edited 08-19-2001).]

 

[David Mack]

Maybe this might be of use to someone out there...

I was able to add the column via ALTER TABLE with the following, though I didn't see it printed in any of my resources:


Private sub AddColumn(tablename as String)
Dim db as Database()
Set db = CurrentDB

db.Execute "ALTER TABLE " & tablename & " ADD COLUMN Index2 Autoincrement;"

Set db = Nothing

Would still like to figure out how to do it with the CREATE command though. There is no dbAutoincrement Type Property available.

If you have any ideas, please post them.

Cheers!

Dave Mack
Saratoga, NY



[This message has been edited by David Mack (edited 08-19-2001).]