1

I need to modify the schema of an MS Access database via code – but I want to be able to direct the order or placement of the fields within the tables.

For example - if TableOne has 3 fields – Field10, Field20 and Field30. I would like to be able to add say Field15 between Field10 and Field20 – not just append it to the end of existing fields.

Perhaps I am missing something but it seems ADO, ADOX ADOD, etc. all simply append new fields to the end. I know it should be possible as you can do it within MS Access and it works.

JimH
  • 13
  • 3
  • Have you considered simply not worrying about the placement of fields in the table and using a query to return the data when a specific order is required? There is not much to gain by getting the order of fields 'right' in a table, unless it is performance order, which seems unlikely in MS Access. You may wish to look at http://stackoverflow.com/questions/894522/is-there-any-reason-to-worry-about-the-column-order-in-a-table – Fionnuala Feb 06 '13 at 11:16
  • Thank you for your response and link. After going back and looking at my lower code - which is machine generated from a database that defines all my databases, tables, fields, edits - I realized 99% of the time - I was controlling the field order via SQL. I did have one function that used Select * which I am in the process of changing. – JimH Feb 06 '13 at 20:57

1 Answers1

0

You can use VBA to modify the DAO.TableDef, and specify the new field's OrdinalPosition property before you append it. See whether you can build on this example session from the Immediate window:

set db = currentdb
for each f in db.tabledefs("DiscardMe").fields : _
? f.name, f.ordinalposition : _
next
ID             0 
empid          1 
dtpunched      2 
fake_yesno     3 

set newfield = db.tabledefs("DiscardMe").createfield("fld2", dbtext)
newfield.ordinalposition = 1
db.tabledefs("DiscardMe").fields.append newfield

Note however that table now includes 2 fields with OrdinalPosition = 1. 

for each f in db.tabledefs("DiscardMe").fields : _
? f.name, f.ordinalposition : _
next
ID             0 
empid          1 
dtpunched      2 
fake_yesno     3 
fld2           1

The Access help topic explains "Two or more Field objects in the same collection can have the same OrdinalPosition property value, in which case they will be ordered alphabetically." So the query, SELECT TOP 1 DiscardMe.* FROM DiscardMe;, gives me this:

ID empid fld2 dtpunched fake_yesno
 1   100       4/3/2012          0

With some refinement that approach may get you what you want. However, I avoid depending on column's ordinal positions. If they should ever change, I don't want my code to break.

HansUp
  • 95,961
  • 11
  • 77
  • 135
  • Thank you - and I have come to agree it is better to not rely on the ordinal positions as you suggest. But rather control the order via Sql in my code. Thank for your response. – JimH Feb 06 '13 at 21:00