How to INDEX a new DAO CreateField on the Fly - MS Access

Question

I have a problem. I need to take the newly created table column (Field) I had just name "ID" and INDEX it (Unique). I have tried several ways with no success. All I'm asking is for someone to offer a fresh perspective. ~ Shaw

Public Sub OrcleJEtoUnmatched()
Dim db As Database
Dim tdf As DAO.TableDef
Dim fld1 As DAO.Field, fld2 As DAO.Field
Dim idx As Index
Dim rst As DAO.Recordset
Dim hertz As String

Set db = CurrentDb()

'Copies table data from ORACLE JE Table; Creates / overwrites existing data to UNMATCHED Table (Working Table)
DoCmd.RunSQL "SELECT [Oracle JE].* INTO Unmatched FROM [Oracle JE];"

Set tdf = db.TableDefs("Unmatched")
Set fld1 = tdf.CreateField("ID", dbText, 255)
Set fld2 = tdf.CreateField("BatchCalc", dbText, 255)
With tdf
  .Fields.Append fld1
  .Fields.Append fld2
End With

Set rst = db.OpenRecordset("Unmatched", dbOpenTable)
Do Until rst.EOF
  hertz = rst![Accounting Document Item] & Mid(rst![JE Line Description], 20, 2) & Round(Abs(rst![Transaction Amount]), 0)
  rst.Edit
  rst!ID = Replace(hertz, " ", "")
  rst!BatchCalc = Mid(rst![JE Line Description], 8, 8)
  rst.Update
  rst.MoveNext
Loop
rst.Close
Application.RefreshDatabaseWindow

Set fld1 = Nothing
Set fld2 = Nothing
Set tdf = Nothing
Set db = Nothing
End Sub

Answer 1

If you want to stick with the DAO object model to create your field and index it, look at the Index.CreateField Method in Access' help system.

But I think it's easier to do both by executing an ALTER TABLE statement ...

CurrentDb.Execute "ALTER TABLE Unmatched ADD COLUMN ID TEXT(255) UNIQUE;"