Ordinalposition not working correctly

[gdev]

I have the following code:
With tblXL

.Fields("0").OrdinalPosition = 0
.Fields("1").OrdinalPosition = 1
.Fields("2").OrdinalPosition = 2
.Fields("3").OrdinalPosition = 3
.Fields("4").OrdinalPosition = 4
.Fields("5").OrdinalPosition = 5
.Fields("6").OrdinalPosition = 6
.Fields("7").OrdinalPosition = 7
.Fields("8").OrdinalPosition = 8
.Fields.Refresh
End With

There are 12 fields in the table, 3 of which are dynamic because the date changes.

After running the code, field 8 is in position 9 and dynamic field 9 is in position 8. Sometimes the code runs perfectly, most of the time the field positions are in the wrong order. Table is automatically exported to Excel.

Table design after code runs.

table field ordinal position
field 0 0
field 1 1
field 2 2
field 3 3
field 4 4
field 5 5
field 6 6
field 7 7
field 8 9
dynamic field 9 8
dynamic field 10 10
dynamic field 11 11

Is there something I overlooked or how can I make this work?

 

[Remou]

Is there any need for this? Won't a query arrange the fields in the required order?

 

[gdev]

Query?
How can I use a query when I don't know what the 3 dates fields will be.
Everything is done programmatically. The user clicks a button and the table results are exported to Excel.
Please assist.

Thanks

 

[Remou]

It seems that you know the order. If that is so, it should be possible to create the query dynamically.

 

[JerryKlmns]

gdev

If you open an empty recordset, you could loop its fields collection and extract the names ot the fields. Build on the fly the SQL statement for a query and you 're done.

Post if you need more guidance.

 

[JoeAtWork]

Use a sorted query to create a recordset with the first 9 fields. Disconnect the recordset. Now add your dynamic fields.

 

[gdev]

Thank you, Thank you, for your responses.
I am going to need some guidance on looping through the fields collection to build the sql statement.
Please assist.

Thanks

 

[JerryKlmns]

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs as DAO.Recorset
Dim fld As DAO.Field
Dim strSQL AS String
Dim myFields() As String
Dim iCount As Long

Set db = CurrentDB
'Get an empty recordset on your table
strSQL= "SELECT * FROM yourTableNameHere WHERE 1=0;"
'Open the recordset
Set rst = db.OpenRecordset(strSQL)
'Resize the array that holds the field 's names
ReDim myFields(rst.Fields.Count - 1)
'Loop the fields
For Each fld In rst.Fields
'Store them in the array
   myFields(iCount)= fld.Name
'Increase array pointer
   iCount = iCount + 1
'Loop
Next
rst.Close

'Create the SQL statement of the query on the fly
Set qdf = db.CreateQueryDef ("OnTheFlyQuery", strSQL)
strSQL = "SELECT "
strSQL =  strSQL & myFields(0) & ", "
strSQL =  strSQL & myFields(1) & ", "
strSQL =  strSQL & myFields(2) & ", "
strSQL =  strSQL & myFields(3) & ", "
...

---------------------------
You have to put here your logic of ordering the fields
strSQL =  strSQL & myFields(8) & ", "
strSQL =  strSQL & myFields(9) & ", "
strSQL =  strSQL & myFields(10) & ", "
strSQL =  strSQL & myFields(11) & ", "
---------------------------
strSQL =  strSQL & " FROM yourTableNameHere;"
'Pass the ordered fields of SQL statement to the query
qdf.SQL = strSQL
'Refresh the collection of queries
db.QueryDefs.Refresh
'And now you are ready to go.
DoCmd.TransferSpreadsheet acExport, 9,  "OnTheFlyQuery", "C:\OnTheFlyQuery.xls", True

Code not tested.