View Single Post
  #1 (permalink)  
Old September 10th, 2006, 12:25 PM
Jack1000 Jack1000 is offline
Registered User
 
Join Date: Sep 2006
Location: , , USA.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default VBA Column Sort Order and numbers

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