By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,118 Members | 1,134 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,118 IT Pros & Developers. It's quick & easy.

How to insert fields into an existing table

P: 52
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)

Expand|Select|Wrap|Line Numbers
  1.    Dim db As DAO.Database
  2.    Dim Tbl As DAO.TableDef
  3.    Dim fld As DAO.Field
  4.    Dim Prop As DAO.Property
  5.  
  6.    Set db = CurrentDb()
  7.    Set Tbl = db.TableDefs("Data Entry1")
  8.  
  9. '***Add NEW Columns***
  10.  Tbl.Fields.Refresh
  11.  If Tbl.Fields(10).Name <> "Branch/Ticket" Then
  12.    Set fld = Tbl.CreateField("Branch/Ticket", dbText, 10)
  13.    fld.OrdinalPosition = 10
  14.    Tbl.Fields.Append fld
  15.    fld.Properties("Required").Value = False
  16.    fld.Properties("AllowZeroLength").Value = True
  17.  End If
  18.  
  19.  Tbl.Fields.Refresh
  20.  If Tbl.Fields(14).Name <> "Auth/PO Number" Then
  21.    Set fld = Tbl.CreateField("Auth/PO Number", dbText, 255)
  22.    fld.OrdinalPosition = 14
  23.    Tbl.Fields.Append fld
  24.    fld.Properties("Required").Value = False
  25.    fld.Properties("AllowZeroLength").Value = True
  26.  End If
  27.  
  28.  Tbl.Fields.Refresh
  29.  Set Tbl = db.TableDefs("Data Entry1")
  30.  If Tbl.Fields(18).Name <> "Invoiced Amount" Then
  31.    Set fld = Tbl.CreateField("Invoiced Amount", dbCurrency)
  32.    fld.OrdinalPosition = 18
  33.    Tbl.Fields.Append fld
  34.    fld.Properties("Required").Value = False
  35.  End If
  36.  
  37.  Tbl.Fields.Refresh
  38.  If Tbl.Fields(24).Name <> "Amount Paid" Then
  39.    Set fld = Tbl.CreateField("Amount Paid", dbCurrency)
  40.    fld.OrdinalPosition = 24
  41.    Tbl.Fields.Append fld
  42.    fld.Properties("Required").Value = False
  43.  End If
  44.  
Oct 25 '10 #1

✓ answered by mshmyob

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

Expand|Select|Wrap|Line Numbers
  1. Public Sub fSetOrdinal(db As Database, strTbl As String, intOrdinal As Integer, strNewFieldName As String)
  2. ' ---------------------------------------------------------
  3. ' Name:     fSetOrdinal
  4. ' Purpose:  To add a new field into a table at a specific Ordinal Number
  5. ' Inputs:   db - the database to use
  6. '           strTbl - name of the table to add field to
  7. '           intOrdinal - ordinal position for new field
  8. '           strNewFieldName - name for new field to be added
  9. ' Returns:  Nothing
  10. ' ----------------------------------------------------------
  11.  
  12.  
  13. Dim tbl As TableDef
  14. Dim fld As Field
  15. Dim fldLoop As Field
  16. Dim intLoop As Integer
  17.  
  18. Set tbl = db.TableDefs(strTbl)
  19.  
  20. ' check for existence of same column name
  21. For Each fldLoop In tbl.Fields
  22. If fldLoop.Name = strNewFieldName Then
  23.     MsgBox ("This column (field) name [" & strNewFieldName & "] already exists in table: " & strTbl)
  24.     Exit Sub
  25. End If
  26. Next
  27.  
  28. 'ensure all fields have a unique ordinal value - ie: reset ALL ordinals
  29. tbl.Fields.Refresh
  30. For intLoop = 0 To tbl.Fields.Count - 1
  31.     tbl.Fields(intLoop).OrdinalPosition = intLoop
  32. Next
  33. tbl.Fields.Refresh
  34.  
  35.  
  36. '***Add NEW Column***
  37.  
  38. ' loop thru all fields and ensure no field has the same ordinal that you want to add
  39. For Each fldLoop In tbl.Fields
  40.     ' if existing field has same ordinal then increase all subsequent field ordinals by 1
  41.     If fldLoop.OrdinalPosition = intOrdinal Then
  42.         For intLoop = tbl.Fields.Count - 1 To intOrdinal Step -1
  43.             tbl.Fields(intLoop).OrdinalPosition = intLoop + 1
  44.         Next
  45.     End If
  46. Next
  47. tbl.Fields.Refresh
  48.  
  49. ' to make function more robust you could pass these field parameters also
  50. ' i did not just for demo purposes
  51.    Set fld = tbl.CreateField(strNewFieldName, dbText, 10)
  52.    fld.Properties("Required").Value = False
  53.    fld.Properties("AllowZeroLength").Value = True
  54.    fld.OrdinalPosition = intOrdinal
  55.    tbl.Fields.Append fld
  56. tbl.Fields.Refresh
  57.  
  58. End Sub
  59.  
cheers,

Share this Question
Share on Google+
10 Replies


P: 52
A little help...Anybody???
Oct 29 '10 #2

mshmyob
Expert 100+
P: 903
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,
Oct 29 '10 #3

P: 52
Thank you soo much...I do hope you find a way to make it work
Oct 29 '10 #4

mshmyob
Expert 100+
P: 903
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.

Expand|Select|Wrap|Line Numbers
  1. ' this routine allows you to add new columns to a table and specify their ordinal position
  2. ' since Access allows multi columns to have the same ordinal position, this routine will
  3. ' ensure no column has the same ordinal position you are trying to add
  4. ' The final piece of code can be run to ensure all fields have a unique ordinal
  5. ' Note: If columns have the same ordinal then Access will display the columns in alphabetical order
  6. ' Note: This may be simplified by creating a public function but I have left it like so to display the logic
  7.  
  8.  
  9.     Dim db As dao.Database
  10.     Dim Tbl As dao.TableDef
  11.     Dim fld As dao.Field2
  12.     'Dim Prop As dao.Property
  13.     Dim intOrd As Integer
  14.     Dim intCounter As Integer
  15.  
  16.  
  17.    Set db = CurrentDb()
  18.    Set Tbl = db.TableDefs("tblOrdinal")
  19.  
  20.  
  21. Tbl.Fields.Refresh
  22.  
  23. 'ensure all fields have a unique ordinal value - ie: reset ALL ordinals
  24. For intCounter = 0 To Tbl.Fields.Count - 1
  25.     Tbl.Fields(intCounter).OrdinalPosition = intCounter
  26. Next
  27.  
  28. Tbl.Fields.Refresh
  29.  
  30. '***Add NEW Columns***
  31.  
  32. '---------- 1st field -------------
  33. intOrd = 10 ' position where a new field will be inserted - note: ordinal positions start at 0
  34. ' loop thru all fields and ensure no field has the same ordinal that you want to add
  35. For intCounter = 0 To Tbl.Fields.Count - 1
  36.     ' change the ordinal of field if the same as the one you want to add
  37.     If Tbl.Fields(intCounter).OrdinalPosition = intOrd Then
  38.         Tbl.Fields(intCounter).OrdinalPosition = intOrd + 1
  39.     End If
  40. Next
  41. Tbl.Fields.Refresh
  42.  
  43.    Set fld = Tbl.CreateField("Ord10", dbText, 10)
  44.    fld.Properties("Required").Value = False
  45.    fld.Properties("AllowZeroLength").Value = True
  46.    fld.OrdinalPosition = intOrd
  47.    Tbl.Fields.Append fld
  48. Tbl.Fields.Refresh
  49. '------------- end 1st field -------------
  50.  
  51.  
  52. '-------- 2nd field ---------
  53. intOrd = 11 ' position where a new field will be inserted - note: ordinal positions start at 0
  54. ' loop thru all fields and ensure no field has the same ordinal that you want to add
  55. For intCounter = 0 To Tbl.Fields.Count - 1
  56.     ' change the ordinal of field if the same as the one you want to add
  57.     If Tbl.Fields(intCounter).OrdinalPosition = intOrd Then
  58.         Tbl.Fields(intCounter).OrdinalPosition = intOrd + 1
  59.     End If
  60. Next
  61. Tbl.Fields.Refresh
  62.    Set fld = Tbl.CreateField("Ord11", dbText, 10)
  63.    fld.Properties("Required").Value = False
  64.    fld.Properties("AllowZeroLength").Value = True
  65.    fld.OrdinalPosition = intOrd
  66.    Tbl.Fields.Append fld
  67. Tbl.Fields.Refresh
  68. '----------- end 2nd field -------------
  69.  
  70.  
  71. '---------- 3rd field ---------
  72. intOrd = 12 ' position where a new field will be inserted - note: ordinal positions start at 0
  73. ' loop thru all fields and ensure no field has the same ordinal that you want to add
  74. For intCounter = 0 To Tbl.Fields.Count - 1
  75.     ' change the ordinal of field if the same as the one you want to add
  76.     If Tbl.Fields(intCounter).OrdinalPosition = intOrd Then
  77.         Tbl.Fields(intCounter).OrdinalPosition = intOrd + 1
  78.     End If
  79. Next
  80. Tbl.Fields.Refresh
  81.    Set fld = Tbl.CreateField("Ord12", dbText, 10)
  82.    fld.Properties("Required").Value = False
  83.    fld.Properties("AllowZeroLength").Value = True
  84.    fld.OrdinalPosition = intOrd
  85.    Tbl.Fields.Append fld
  86. Tbl.Fields.Refresh
  87. '--------- end 3rd field --------
  88.  
  89.  
  90. ' ----- run this again if you want unique ordinal values for all fields -------
  91. 'ensure all fields have a unique ordinal value - ie: reset ALL ordinals
  92. 'For intCounter = 0 To Tbl.Fields.Count - 1
  93. '    Tbl.Fields(intCounter).OrdinalPosition = intCounter
  94. 'Next
  95. 'Tbl.Fields.Refresh
  96. '-------------------------------------------------------------------------------
  97.  
  98.  
Let me know if this works for you.

cheers,
Oct 31 '10 #5

P: 52
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
Nov 6 '10 #6

mshmyob
Expert 100+
P: 903
Sorry about that. I have cleaned up the code and put it in a module form.

Code to call function

Expand|Select|Wrap|Line Numbers
  1. ' here I am adding 3 new fields named (Ord10, Ord13, and Ord16)
  2. ' they have ordinal positions of 10, 13,16 respectively
  3.  
  4. Call fSetOrdinal(CurrentDb(), "tblOrdinal", 10, "Ord10")
  5. Call fSetOrdinal(CurrentDb(), "tblOrdinal", 13, "Ord13")
  6. Call fSetOrdinal(CurrentDb(), "tblOrdinal", 16, "Ord16")
  7.  
Standard Module Code

Expand|Select|Wrap|Line Numbers
  1. Public Sub fSetOrdinal(db As Database, strTbl As String, intOrdinal As Integer, strNewFieldName As String)
  2. ' ---------------------------------------------------------
  3. ' Name:     fSetOrdinal
  4. ' Purpose:  To add a new field into a table at a specific Ordinal Number
  5. ' Inputs:   db - the database to use
  6. '           strTbl - name of the table to add field to
  7. '           intOrdinal - ordinal position for new field
  8. '           strNewFieldName - name for new field to be added
  9. ' Returns:  Nothing
  10. ' ----------------------------------------------------------
  11.  
  12.  
  13. Dim tbl As TableDef
  14. Dim fld As Field
  15. Dim fldLoop As Field
  16. Dim intLoop As Integer
  17.  
  18. Set tbl = db.TableDefs(strTbl)
  19.  
  20. ' check for existence of same column name
  21. For Each fldLoop In tbl.Fields
  22. If fldLoop.Name = strNewFieldName Then
  23.     MsgBox ("This column (field) name [" & strNewFieldName & "] already exists in table: " & strTbl)
  24.     Exit Sub
  25. End If
  26. Next
  27.  
  28. 'ensure all fields have a unique ordinal value - ie: reset ALL ordinals
  29. tbl.Fields.Refresh
  30. For intLoop = 0 To tbl.Fields.Count - 1
  31.     tbl.Fields(intLoop).OrdinalPosition = intLoop
  32. Next
  33. tbl.Fields.Refresh
  34.  
  35.  
  36. '***Add NEW Columns***
  37.  
  38. ' loop thru all fields and ensure no field has the same ordinal that you want to add
  39. For Each fldLoop In tbl.Fields
  40.     ' if existing field has same ordinal then increase all subsequent field ordinals by 1
  41.     If fldLoop.OrdinalPosition = intOrdinal Then
  42.         For intLoop = intOrdinal To tbl.Fields.Count - 1
  43.             tbl.Fields(intLoop).OrdinalPosition = fldLoop.OrdinalPosition + 1
  44.         Next
  45.     End If
  46. Next
  47. tbl.Fields.Refresh
  48.  
  49. ' to make function more robust you could pass these field parameters also
  50. ' i did not just for demo purposes
  51.    Set fld = tbl.CreateField(strNewFieldName, dbText, 10)
  52.    fld.Properties("Required").Value = False
  53.    fld.Properties("AllowZeroLength").Value = True
  54.    fld.OrdinalPosition = intOrdinal
  55.    tbl.Fields.Append fld
  56. tbl.Fields.Refresh
  57.  
  58.  
  59. End Sub
  60.  
cheers,
Nov 7 '10 #7

P: 52
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.
Nov 9 '10 #8

mshmyob
Expert 100+
P: 903
Here is the new module code. Try this but make sure you use your original table.

Expand|Select|Wrap|Line Numbers
  1. Public Sub fSetOrdinal(db As Database, strTbl As String, intOrdinal As Integer, strNewFieldName As String)
  2. ' ---------------------------------------------------------
  3. ' Name:     fSetOrdinal
  4. ' Purpose:  To add a new field into a table at a specific Ordinal Number
  5. ' Inputs:   db - the database to use
  6. '           strTbl - name of the table to add field to
  7. '           intOrdinal - ordinal position for new field
  8. '           strNewFieldName - name for new field to be added
  9. ' Returns:  Nothing
  10. ' ----------------------------------------------------------
  11.  
  12.  
  13. Dim tbl As TableDef
  14. Dim fld As Field
  15. Dim fldLoop As Field
  16. Dim intLoop As Integer
  17.  
  18. Set tbl = db.TableDefs(strTbl)
  19.  
  20. ' check for existence of same column name
  21. For Each fldLoop In tbl.Fields
  22. If fldLoop.Name = strNewFieldName Then
  23.     MsgBox ("This column (field) name [" & strNewFieldName & "] already exists in table: " & strTbl)
  24.     Exit Sub
  25. End If
  26. Next
  27.  
  28. 'ensure all fields have a unique ordinal value - ie: reset ALL ordinals
  29. tbl.Fields.Refresh
  30. For intLoop = 0 To tbl.Fields.Count - 1
  31.     tbl.Fields(intLoop).OrdinalPosition = intLoop
  32. Next
  33. tbl.Fields.Refresh
  34.  
  35.  
  36. '***Add NEW Column***
  37.  
  38. ' loop thru all fields and ensure no field has the same ordinal that you want to add
  39. For Each fldLoop In tbl.Fields
  40.     ' if existing field has same ordinal then increase all subsequent field ordinals by 1
  41.     If fldLoop.OrdinalPosition = intOrdinal Then
  42.         For intLoop = tbl.Fields.Count - 1 To intOrdinal Step -1
  43.             tbl.Fields(intLoop).OrdinalPosition = intLoop + 1
  44.         Next
  45.     End If
  46. Next
  47. tbl.Fields.Refresh
  48.  
  49. ' to make function more robust you could pass these field parameters also
  50. ' i did not just for demo purposes
  51.    Set fld = tbl.CreateField(strNewFieldName, dbText, 10)
  52.    fld.Properties("Required").Value = False
  53.    fld.Properties("AllowZeroLength").Value = True
  54.    fld.OrdinalPosition = intOrdinal
  55.    tbl.Fields.Append fld
  56. tbl.Fields.Refresh
  57.  
  58. End Sub
  59.  
cheers,
Nov 9 '10 #9

P: 52
This did the trick!!!

Thank You for your help
Nov 9 '10 #10

mshmyob
Expert 100+
P: 903
You're welcome. Good luck with the rest of your project.

cheers,
Nov 10 '10 #11

Post your reply

Sign in to post your reply or Sign up for a free account.