Hi everyone,

  I have a problem with this code section because the tables columns come out in alphabetical order of column names. I would prefer them to come out in the order of fields(0..n)

  Two questions I need help on:

how can I refer to the field number in the following subroutine and how can I make the columns not appear in alphabetical order:

The code is:
Private Sub Command0_Click()

  Dim intLocCnter1 As Integer
  Dim cnn As ADODB.Connection
  Dim cmdDelete As ADODB.Command
  Dim rstDictionary As ADODB.Recordset
  Dim cat As ADOX.Catalog
  Dim tbl As ADOX.Table
  Dim col As ADOX.Column

  Set cnn = CurrentProject.Connection
  Set cmdDelete = New ADODB.Command
  Set cmdDelete.ActiveConnection = cnn
  cmdDelete.CommandText = "DELETE * FROM DataDictionary"
  cmdDelete.Execute
  Set rstDictionary = New ADODB.Recordset
  rstDictionary.Open "SELECT * FROM DataDictionary", _
   cnn, adOpenStatic, adLockOptimistic

  Set cat = New ADOX.Catalog
  Set cat.ActiveConnection = CurrentProject.Connection

  For Each tbl In cat.Tables
  intLocCnter1 = 0

   If tbl.Name = "ToTest" Or tbl.Name = "FromTest" Then
      For Each col In tbl.Columns
        rstDictionary.AddNew
          rstDictionary.Fields("TableName").Value = tbl.Name
          rstDictionary.Fields("ColumnName").Value = col.Name
          rstDictionary.Fields("ColumnNumber").Value = intLocCnter1
          rstDictionary.Fields("Type").Value = TranslateType(col.Type)
          intLocCnter1 = intLocCnter1 + 1
        rstDictionary.Update
      Next col
    End If
  Next tbl
  MsgBox "we are done"
  rstDictionary.Close
End Sub

This subroutine produces :
TableName ColumnNumber ColumnName Type
FromTest 0 Address Line 1 Text
FromTest 1 Address Line 2 Text
etc.

Any help will be appreciated

Thanks

Jack

hi
I had the same problem
It is an issue related to the OLEDB 4.0 when opening an Access MDB database with Adox to create / append a new table and the columns will have a certain ordinal.

use MSSQL instead of OLEDB to do the job with ADOX
or use ADO OpenSchema that would do a similar job
Details of both solutions and the problem are vailable in the followin link

http://support.microsoft.com/kb/299484/EN-US/

This should solve the problem

BR

Hassan