How to INDEX a new DAO CreateField on the Fly (1 Viewer)

shiznaw

Registered User.
Local time
Yesterday, 23:25
Joined
Feb 3, 2016
Messages
18
I have a problem. I need to take the newly created table column (Field) I had just name "ID" and PRIMARY / INDEX it. I have tried several ways with no success. All I'm asking is for someone to offer a fresh perspective. ~ Shaw

Code:
Public Sub OrcleJEtoUnmatched() '---DONE---
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
 

shiznaw

Registered User.
Local time
Yesterday, 23:25
Joined
Feb 3, 2016
Messages
18
Found the Answer - Thanxs

Code:
Public Sub OrcleJEtoUnmatched() '---DONE---
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

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

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

Users who are viewing this thread

Top Bottom