How to change field order in table programatically?

[rich.barry]

Is anyone out there able to tell me how to change the order of fields in a table structure using VBA?

I'm inserting a field into a table, but it inserts at the end, and I then want to move it to being the first field.
I know that I can re-arrange everything with a query, but would prefer to modify the table.

Thanks

Richard

 

[krc777]

You can insert a field directly into the table into the position you want it to appear.

 

[rich.barry]

This is my insert code

With DBEngine(0)(0).TableDefs(sbOutputTable)
  .Fields.Append .CreateField("SubTotals", dbText, 100)
End With

Createfield help files don't imply that you can specify insert position.
Are you able to supply illustrative code that does?

Thanks

 

[Banaticus]

Why do you want to move it to being in a specific place relative to other columns in the table? When looking at the table information in a query, a form, etc., it displays however you tell it to display, without any regard for its "actual" arrangment.

There is no performance loss by taking data stored in a table with the following as columns, like this:
FirstName
TrackingNumber
StreetAddress
LastName
State
MiddleName

and displaying it like this:
TrackingNumber
FirstName
MiddleName
LastName
StreetAddress
State

 

[rich.barry]

The database has a series of reports that simply dump the data to Excel for users to do whatever they want with.
This particular table is one in which the structure is replicated from an existant table, one additional field added, the table populated, then dumped to Excel.
No queries, forms or anything other Access objects use this table, which is the answer to your question.
As I want the data appearing in Excel in a certain order, I need to specify where the additional field is inserted in the table. Of course I can create a query on the table, then dump the query to Excel, but as I have over 100 queries already, I prefer not to create more unless necessary.

Regards

Richard

 

[ghudson]

As I want the data appearing in Excel in a certain order, I need to specify where the additional field is inserted in the table. Of course I can create a query on the table, then dump the query to Excel, but as I have over 100 queries already, I prefer not to create more unless necessary.

Whats the difference if you have 101 queries? Odds are you will have more to come. You could use the SQL of that query and run the SQL in VBA if you wanted to not create one more stored query definition.

 

[rich.barry]

160 queries actually. Of these, probably there will be about 20 that no longer do anything and could be deleted. I find that the more queries there are, the harder it is to manage them and keep the database tidy, hence my reason for trying to avoid creating one in this instance.
I've now got a hunch that I may be able to insert the extra field at the same time I create the table using a DoCmd.RunSQL, but if that fails then I'll try your suggestion of using the query SQL definition later on.

Thanks

Richard