Order of fields in table creation with ADOX

[hakke]

Hi,

I've created a database (.mdb) with several tables using ADOX. When I look to the table created, the fields are in alphabetical order instead of the order like in the VB code.
Here's an example :

Private Sub CreateTable()
' Create the tables

Dim NewDB As New ADOX.Catalog
Dim tbl As New ADOX.Table
Dim col As New ADOX.Column

' Connect to database
NewDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=c:\temp\webupdate.mdb"

' Create Table tbl_whs_artikelgroep
Set tbl = New ADOX.Table
With tbl
.Name = "tbl_whs_artikelgroep"
.Columns.Append "grp_code", adChar, 20
.Columns.Append "grp_omschrijving", adChar, 50
.Columns.Append "grp_commercieleomschrijving_nl", adLongVarChar
.Columns.Append "grp_commercieleomschrijving_fr", adLongVarChar
.Columns.Append "grp_lastenboek_nl", adLongVarChar
.Columns.Append "grp_lastenboek_fr", adLongVarChar
NewDB.Tables.Append tbl
End With
Set tbl = Nothing

End Sub

In the table : tbl_whs_artikelgroep the fields are stored in the following order :
grp_code
grp_commercieleomschrijving_nl
grp_commercieleomschrijving_fr
grp_lastenboek_nl
grp_lastenboek_fr
grp_omschrijving

Is there a way to store the order of the fields in the same order like introduced in the VB code?

Thanks,

Hans Hubin

[Clunietp]

Can you add the columns one at a time?

I mean:
Create a table object
add a column
append the table object

append column to table
append column to table
append column to table
etc.....

[hakke]

It doesn't work. When I try to append a column at a time I get the following error :
Run-time error '3367': Can't append. Object already in collection.

I think it's a bug in ADOX, because in msdn they use exactly the same code as I and there isn't anything mentioned about an order.

[Clunietp]

This code works fine for me. This uses ADO and ADOX

Dim objCN As Connection
    Dim objTbl As Table
    Dim objCat As Catalog
    
    Set objCN = New Connection
    Set objCat = New Catalog
    
    objCN.Open "Provider=Microsoft.jet.oledb.4.0;data source=c:\test.mdb"
    
    Set objCat.ActiveConnection = objCN
    
    Set objTbl = New Table
    
    'add column to table
    objTbl.Columns.Append "field3", adVarWChar, 50
    objTbl.Name = "MyTable"
    
    'add table to db
    objCat.Tables.Append objTbl
    
    'get table that we just appended
    Set objTbl = objCat.Tables("MyTable")
    
    'append the columns
    objTbl.Columns.Append "field4", adVarWChar, 50
    objTbl.Columns.Append "field1", adVarWChar, 50
    objTbl.Columns.Append "field2", adVarWChar, 50
    
    'cleanup
    Set objTbl = Nothing
    Set objCat = Nothing
    Set objCN = Nothing

Most of the ADOX functions aren't supported by the Jet.3.51 provider, so you must use the Jet 4.0


Tom

[hakke]

It works !!!!
Thanks a lot.


Hans