Wrox Programmer Forums
BOOK: Access 2003 VBA Programmer's Reference
This is the forum to discuss the Wrox book Access 2003 VBA Programmer's Reference by Patricia Cardoza, Teresa Hennig, Graham Seach, Armen Stein; ISBN: 9780764559037
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Access 2003 VBA Programmer's Reference section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old September 10th, 2006, 12:25 PM
Registered User
Join Date: Sep 2006
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"
  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.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
      Next col
    End If
  Next tbl
  MsgBox "we are done"
End Sub

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

Any help will be appreciated


Old October 5th, 2006, 10:53 PM
Registered User
Join Date: Oct 2006
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts

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


This should solve the problem



Similar Threads
Thread Thread Starter Forum Replies Last Post
Incrementing Job Order Numbers marclf Access VBA 3 February 29th, 2008 12:34 PM
Dynamic sort order or sort datatype kapy_kal XSLT 2 September 18th, 2007 02:10 PM
sort recordset in different order pablohoney Classic ASP Databases 0 December 29th, 2005 08:32 PM
Sort order Question Tere Crystal Reports 1 February 14th, 2005 03:18 PM
sort numbers hosefo81 PHP How-To 1 January 28th, 2004 02:06 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.