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