ADO DataGrid order columns

[korna]

I am changing a VB6 program from DAO to ADO and bump into some problems. Before I go into the multi table problems I will start with a single table :-/ I have an Access 97 database with some tables. The tables have no relations among each other.

I want to populate a DataGrid (or any kind of Grid) with a the data from a table. However I do not want to see all table columns and I want to change the order of the columns. I know I can do this in design time but I do not have the time to design all the grids by hand. So I want to do this in code.

I created a ADODB.Recordset and started it with a query. This automatically populates the DataGrid and I can manipulate the information in the fields.

Private data_1 As ADODB.Recordset

Private Sub Form_Load()
query = "SELECT * FROM ABC_DEF"
Set data_1 = New Recordset
data_1.CursorLocation = adUseClient
data_1.Open query, gDB, adOpenStatic, adLockOptimistic
Set DataGrid1.DataSource = data_1

I tried several methods to give the DataGrid the layout I need.

- Removing not needed columns with a for loop and then re-assign the columns(i).DataField and Caption.
However this does not seem to work. The datagrid is no longer repopulated. If I rebind the datagrid or requery the recordset then I loose the layout and it reverts to showing all columns.

Set DataGrid1.DataSource = data_1
    DataGrid1.ClearFields
    DataGrid1.Refresh
    Dim i As Long
    Do Until DataGrid1.Columns.count = 5
        DataGrid1.Columns.Remove (1)
    Loop

    With DataGrid1.Columns
        .Item(0).DataField = "Id"
        .Item(0).Caption = "Id"
        .Item(1).DataField = "Name"
        .Item(1).Caption = "SG"
        .Item(2).DataField = "Red"
        .Item(2).Caption = "L"
        .Item(3).DataField = "NrCC"
        .Item(2).Caption = "NrCC"
        .Item(4).DataField = "And"
        .Item(4).Caption = "A"
    End With

- Hiding the columns I do not want to see with columns(i).Visible = False
This works nicely but I cannot figure out how to place the columns in a different order.

For i = DataGrid1.Columns.count - 1 To 0 Step -1
        Dim flag As Boolean
        flag = False
        Select Case DataGrid1.Columns.Item(i).DataField
            Case "Id"
                flag = True
            Case "Name"
                flag = True
            Case "Red"
                flag = True
            Case "NrCC"
                flag = True
            Case "And"
                flag = True
        End Select
        If Not flag Then
            DataGrid1.Columns.Item(i).Visible = False
        End If
    Next i
    DataGrid1.Refresh

- Putting the columns which I want, in the order I want, in the query SELECT statement. That works for single tables (when not using INNER JOIN) but I would rather have all table columns in the recordset. I thus wonder if there is an alternative.

query = "SELECT Id, Name, Red, NrCC, And FROM ABC_DEF"

Thanks in advance!

ps. If you think databound object are "evil" and have a good solution to make grids and tables interact fully in code then I'm all ears! :icon_lol: