How to insert fields into an existing table

[timleonard]

I am trying to update some existing database tables by inserting some new fields in a set order within the existing table. The issue I am having is it inserts the new field at the existing position counts and not at the new positions based on accumulative count.

For example field 24 is being inserted at the end of the table which is really field 28 when all four of the new fields are inserted. How can I refresh the table so that the fields are inserted at the right location
(Hope this is clear)

   Dim db As DAO.Database
   Dim Tbl As DAO.TableDef
   Dim fld As DAO.Field
   Dim Prop As DAO.Property
 
   Set db = CurrentDb()
   Set Tbl = db.TableDefs("Data Entry1")
 
'***Add NEW Columns***
 Tbl.Fields.Refresh
 If Tbl.Fields(10).Name <> "Branch/Ticket" Then
   Set fld = Tbl.CreateField("Branch/Ticket", dbText, 10)
   fld.OrdinalPosition = 10
   Tbl.Fields.Append fld
   fld.Properties("Required").Value = False
   fld.Properties("AllowZeroLength").Value = True
 End If
 
 Tbl.Fields.Refresh
 If Tbl.Fields(14).Name <> "Auth/PO Number" Then
   Set fld = Tbl.CreateField("Auth/PO Number", dbText, 255)
   fld.OrdinalPosition = 14
   Tbl.Fields.Append fld
   fld.Properties("Required").Value = False
   fld.Properties("AllowZeroLength").Value = True
 End If
 
 Tbl.Fields.Refresh
 Set Tbl = db.TableDefs("Data Entry1")
 If Tbl.Fields(18).Name <> "Invoiced Amount" Then
   Set fld = Tbl.CreateField("Invoiced Amount", dbCurrency)
   fld.OrdinalPosition = 18
   Tbl.Fields.Append fld
   fld.Properties("Required").Value = False
 End If
 
 Tbl.Fields.Refresh
 If Tbl.Fields(24).Name <> "Amount Paid" Then
   Set fld = Tbl.CreateField("Amount Paid", dbCurrency)
   fld.OrdinalPosition = 24
   Tbl.Fields.Append fld
   fld.Properties("Required").Value = False
 End If
 

[timleonard]

A little help...Anybody???

[mshmyob]

Hello Tim,

I will try and look at this over the weekend. What you are doing appears like it should work but I get the same problem no matter how I reconfigure your code or what M$ says to do.

It works if only one field's ordinal position is changed but any subsequent ordinal changes don't work aas expected.

Like I said I will look over it over the weekend - I just wanted to make sure I subscribed so I don't lose this message.

cheers,

[timleonard]

Thank you soo much...I do hope you find a way to make it work

[mshmyob]

Try this and let me know if it helps. Obviously you will have to change the field names and repeat the code for as many fields as you want.

The trick is that Access allows the same ordinal number for multiple fields. I therefore had to work around that. Read the comments in the code to understand what I did.

' this routine allows you to add new columns to a table and specify their ordinal position
' since Access allows multi columns to have the same ordinal position, this routine will
' ensure no column has the same ordinal position you are trying to add
' The final piece of code can be run to ensure all fields have a unique ordinal
' Note: If columns have the same ordinal then Access will display the columns in alphabetical order
' Note: This may be simplified by creating a public function but I have left it like so to display the logic
 
 
    Dim db As dao.Database
    Dim Tbl As dao.TableDef
    Dim fld As dao.Field2
    'Dim Prop As dao.Property
    Dim intOrd As Integer
    Dim intCounter As Integer
 
 
   Set db = CurrentDb()
   Set Tbl = db.TableDefs("tblOrdinal")
 
 
Tbl.Fields.Refresh
 
'ensure all fields have a unique ordinal value - ie: reset ALL ordinals
For intCounter = 0 To Tbl.Fields.Count - 1
    Tbl.Fields(intCounter).OrdinalPosition = intCounter
Next
 
Tbl.Fields.Refresh
 
'***Add NEW Columns***
 
'---------- 1st field -------------
intOrd = 10 ' position where a new field will be inserted - note: ordinal positions start at 0
' loop thru all fields and ensure no field has the same ordinal that you want to add
For intCounter = 0 To Tbl.Fields.Count - 1
    ' change the ordinal of field if the same as the one you want to add
    If Tbl.Fields(intCounter).OrdinalPosition = intOrd Then
        Tbl.Fields(intCounter).OrdinalPosition = intOrd + 1
    End If
Next
Tbl.Fields.Refresh
 
   Set fld = Tbl.CreateField("Ord10", dbText, 10)
   fld.Properties("Required").Value = False
   fld.Properties("AllowZeroLength").Value = True
   fld.OrdinalPosition = intOrd
   Tbl.Fields.Append fld
Tbl.Fields.Refresh
'------------- end 1st field -------------
 
 
'-------- 2nd field ---------
intOrd = 11 ' position where a new field will be inserted - note: ordinal positions start at 0
' loop thru all fields and ensure no field has the same ordinal that you want to add
For intCounter = 0 To Tbl.Fields.Count - 1
    ' change the ordinal of field if the same as the one you want to add
    If Tbl.Fields(intCounter).OrdinalPosition = intOrd Then
        Tbl.Fields(intCounter).OrdinalPosition = intOrd + 1
    End If
Next
Tbl.Fields.Refresh
   Set fld = Tbl.CreateField("Ord11", dbText, 10)
   fld.Properties("Required").Value = False
   fld.Properties("AllowZeroLength").Value = True
   fld.OrdinalPosition = intOrd
   Tbl.Fields.Append fld
Tbl.Fields.Refresh
'----------- end 2nd field -------------
 
 
'---------- 3rd field ---------
intOrd = 12 ' position where a new field will be inserted - note: ordinal positions start at 0
' loop thru all fields and ensure no field has the same ordinal that you want to add
For intCounter = 0 To Tbl.Fields.Count - 1
    ' change the ordinal of field if the same as the one you want to add
    If Tbl.Fields(intCounter).OrdinalPosition = intOrd Then
        Tbl.Fields(intCounter).OrdinalPosition = intOrd + 1
    End If
Next
Tbl.Fields.Refresh
   Set fld = Tbl.CreateField("Ord12", dbText, 10)
   fld.Properties("Required").Value = False
   fld.Properties("AllowZeroLength").Value = True
   fld.OrdinalPosition = intOrd
   Tbl.Fields.Append fld
Tbl.Fields.Refresh
'--------- end 3rd field --------
 
 
' ----- run this again if you want unique ordinal values for all fields -------
'ensure all fields have a unique ordinal value - ie: reset ALL ordinals
'For intCounter = 0 To Tbl.Fields.Count - 1
'    Tbl.Fields(intCounter).OrdinalPosition = intCounter
'Next
'Tbl.Fields.Refresh
'-------------------------------------------------------------------------------
 
 

Let me know if this works for you.

cheers,

[timleonard]

Ok finally had a chance to test it...

First off, Thank you for your help

It seems that if the fields are inserted one after the other (ie. field 10, 11 12) it works, but if there is existing fields between the fields then it does not work (ie. fields 10, 14, 18). It seems to always count from the existing condition

[mshmyob]

Sorry about that. I have cleaned up the code and put it in a module form.

Code to call function

' here I am adding 3 new fields named (Ord10, Ord13, and Ord16)
' they have ordinal positions of 10, 13,16 respectively
 
Call fSetOrdinal(CurrentDb(), "tblOrdinal", 10, "Ord10")
Call fSetOrdinal(CurrentDb(), "tblOrdinal", 13, "Ord13")
Call fSetOrdinal(CurrentDb(), "tblOrdinal", 16, "Ord16")
 

Standard Module Code

Public Sub fSetOrdinal(db As Database, strTbl As String, intOrdinal As Integer, strNewFieldName As String)
' ---------------------------------------------------------
' Name:     fSetOrdinal
' Purpose:  To add a new field into a table at a specific Ordinal Number
' Inputs:   db - the database to use
'           strTbl - name of the table to add field to
'           intOrdinal - ordinal position for new field
'           strNewFieldName - name for new field to be added
' Returns:  Nothing
' ----------------------------------------------------------
 
 
Dim tbl As TableDef
Dim fld As Field
Dim fldLoop As Field
Dim intLoop As Integer
 
Set tbl = db.TableDefs(strTbl)
 
' check for existence of same column name
For Each fldLoop In tbl.Fields
If fldLoop.Name = strNewFieldName Then
    MsgBox ("This column (field) name [" & strNewFieldName & "] already exists in table: " & strTbl)
    Exit Sub
End If
Next
 
'ensure all fields have a unique ordinal value - ie: reset ALL ordinals
tbl.Fields.Refresh
For intLoop = 0 To tbl.Fields.Count - 1
    tbl.Fields(intLoop).OrdinalPosition = intLoop
Next
tbl.Fields.Refresh
 
 
'***Add NEW Columns***
 
' loop thru all fields and ensure no field has the same ordinal that you want to add
For Each fldLoop In tbl.Fields
    ' if existing field has same ordinal then increase all subsequent field ordinals by 1
    If fldLoop.OrdinalPosition = intOrdinal Then
        For intLoop = intOrdinal To tbl.Fields.Count - 1
            tbl.Fields(intLoop).OrdinalPosition = fldLoop.OrdinalPosition + 1
        Next
    End If
Next
tbl.Fields.Refresh
 
' to make function more robust you could pass these field parameters also
' i did not just for demo purposes
   Set fld = tbl.CreateField(strNewFieldName, dbText, 10)
   fld.Properties("Required").Value = False
   fld.Properties("AllowZeroLength").Value = True
   fld.OrdinalPosition = intOrdinal
   tbl.Fields.Append fld
tbl.Fields.Refresh
 
 
End Sub
 

cheers,

[timleonard]

Thank you again for taking the time to help, I really appreciate it

The revised code above does insert the new table fields in the correct position, however for some reason after the first pass it alphabetizes the table fields. Is there a work around to this issue.

[mshmyob]

Here is the new module code. Try this but make sure you use your original table.

Public Sub fSetOrdinal(db As Database, strTbl As String, intOrdinal As Integer, strNewFieldName As String)
' ---------------------------------------------------------
' Name:     fSetOrdinal
' Purpose:  To add a new field into a table at a specific Ordinal Number
' Inputs:   db - the database to use
'           strTbl - name of the table to add field to
'           intOrdinal - ordinal position for new field
'           strNewFieldName - name for new field to be added
' Returns:  Nothing
' ----------------------------------------------------------
 
 
Dim tbl As TableDef
Dim fld As Field
Dim fldLoop As Field
Dim intLoop As Integer
 
Set tbl = db.TableDefs(strTbl)
 
' check for existence of same column name
For Each fldLoop In tbl.Fields
If fldLoop.Name = strNewFieldName Then
    MsgBox ("This column (field) name [" & strNewFieldName & "] already exists in table: " & strTbl)
    Exit Sub
End If
Next
 
'ensure all fields have a unique ordinal value - ie: reset ALL ordinals
tbl.Fields.Refresh
For intLoop = 0 To tbl.Fields.Count - 1
    tbl.Fields(intLoop).OrdinalPosition = intLoop
Next
tbl.Fields.Refresh
 
 
'***Add NEW Column***
 
' loop thru all fields and ensure no field has the same ordinal that you want to add
For Each fldLoop In tbl.Fields
    ' if existing field has same ordinal then increase all subsequent field ordinals by 1
    If fldLoop.OrdinalPosition = intOrdinal Then
        For intLoop = tbl.Fields.Count - 1 To intOrdinal Step -1
            tbl.Fields(intLoop).OrdinalPosition = intLoop + 1
        Next
    End If
Next
tbl.Fields.Refresh
 
' to make function more robust you could pass these field parameters also
' i did not just for demo purposes
   Set fld = tbl.CreateField(strNewFieldName, dbText, 10)
   fld.Properties("Required").Value = False
   fld.Properties("AllowZeroLength").Value = True
   fld.OrdinalPosition = intOrdinal
   tbl.Fields.Append fld
tbl.Fields.Refresh
 
End Sub
 

cheers,

[timleonard]

This did the trick!!!

Thank You for your help

[mshmyob]

You're welcome. Good luck with the rest of your project.

cheers,