Changing column order after adding new field 1

[1DMF]

Hi,

I have created a table via a CSV import, however , this doesn't create an ID for the records.

So I then issue an ALTER TABLE command to add an ID field and loop the records inserting an incremented number.

This works fine.

However when the table is viewed via a subform contol the rec ID column is on the end, I need it shown at the begining.

Is there a way to do this?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[lespaul]

why not just use an auto-incrementing field?

Leslie

 

[dhookom]

If you want the column to show at the left, you can create a query that uses

SELECT ID, Field1, Field2, Field3,...
FROM ...

Duane
Hook'D on Access
MS Access MVP

 

[1DMF]

why not just use an auto-incrementing field?

how do you tell Access to include one when importing a CSV file...

    DoCmd.TransferText acImportDelim, , "Provider_Import", Me!XLS_File, True

The only option is to set True for the CSV having the first row as column names, how do you also tell it to create an autonumber field and place it at the beggining of the table?

you can create a query that uses

This sort of works...

i can create a query and use the following statement

SELECT Provider_Import.Rec_ID AS ID, Provider_Import.*
FROM Provider_Import
ORDER BY Provider_Import.Rec_ID;

and attach it to the subform like so...

    Me.Import_Subform.SourceObject = "Query.sel_Provider_Import"

however I end up with two fields one at the begining and one at the end!!!

I don't supose there is a way to fix this?



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[PHV]

Have you tried to play with the OrdinalPosition property of the Field ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

You could write some code that builds a SQL statement based on the table fields. Then apply the SQL to a saved query in your mdb. Assuming a table named "tblImport" and a query named "qmyquery", you would call the below sub like:

BuildSQLFromTable "tblImport","qmyQuery"

This would create a query with the last field first followed by the first fields.

Public Sub BuildSQLFromTable(strTableName As String, strQueryName As String)
    Dim td As DAO.TableDef
    Dim db As DAO.Database
    Dim strSQL As String
    Dim intFld As Integer
    Dim intFldCount As Integer
    Set db = CurrentDb
    Set td = db.TableDefs(strTableName)
    intFldCount = td.Fields.Count
    strSQL = "SELECT [" & td(intFldCount - 1).Name & "]"
    For intFld = 0 To intFldCount - 2
        strSQL = strSQL & ", [" & td(intFld).Name & "]"
    Next
    strSQL = strSQL & " FROM [" & strTableName & "];"
    Set td = Nothing
    db.QueryDefs(strQueryName).SQL = strSQL
    Set db = Nothing
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[1DMF]

You're a diamond Duane!

It's all a royal pain in the butt just to do something so simple, but at least it's a solution.

I just had to add

Public Sub BuildSQLFromTable(strTableName As String, strQueryName As String[b], strOrderBy As String[/b])

and

    strSQL = strSQL & " FROM [" & strTableName & "] [b]ORDER BY " & strOrderBy & [/b]";"

So I could pass it the Rec_ID and have it sort ASC.

It's working great

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!