Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
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
  #1 (permalink)  
Old September 10th, 2006, 12:25 PM
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
  #2 (permalink)  
Old October 5th, 2006, 10:53 PM
Registered User
 
Join Date: Oct 2006
Location: Cairo, Cairo, Egypt.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


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.