John Shaw February 2016

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

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

Answers


jhTuppeny February 2016

To add an index to a DAO tabledef object, use the createindex method.

Here's the basic documentation;

https://msdn.microsoft.com/EN-US/library/office/ff196791.aspx


HansUp February 2016

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;"


John Shaw February 2016

Using the Answer that @HansUp provided, I altered it and made it work. Thank You both @jhTyppeny & @Hansup for the quick reply.

DoCmd.RunSQL "CREATE UNIQUE INDEX ID ON Unmatched (ID)  WITH PRIMARY"

Post Status

Asked in February 2016
Viewed 2,171 times
Voted 7
Answered 3 times

Search




Leave an answer