[AccessD] ADO - Getting a Tables columns back in physical or der

I don't have any sample code, other than what's in the help file, but you
need to look at the OrdinalPosition property of each field in order to list
them in the order they appear in in table design view.

Here's the code from the help file

This example changes the OrdinalPosition property values in the Employees
TableDef in order to control the Field order in a resulting Recordset. By
setting the OrdinalPosition of all the Fields to 1, any resulting Recordset
will order the Fields alphabetically. Note that the OrdinalPosition values
in the Recordset don't match the values in the TableDef, but simply reflect
the end result of the TableDef changes.

Sub OrdinalPositionX()

	Dim dbsNorthwind As Database
	Dim tdfEmployees As TableDef
	Dim aintPosition() As Integer
	Dim astrFieldName() As String
	Dim intTemp As Integer
	Dim fldTemp As Field
	Dim rstEmployees As Recordset

	Set dbsNorthwind = OpenDatabase("Northwind.mdb")
	Set tdfEmployees = dbsNorthwind.TableDefs("Employees")

	With tdfEmployees
		' Display and store original OrdinalPosition data.
		Debug.Print _
			"Original OrdinalPosition data in TableDef."

ReDim aintPosition(0 To .Fields.Count - 1) As Integer
		ReDim astrFieldName(0 To .Fields.Count - 1) As String
		For intTemp = 0 To .Fields.Count - 1
			aintPosition(intTemp) = _
				.Fields(intTemp).OrdinalPosition
			astrFieldName(intTemp) = .Fields(intTemp).Name
			Debug.Print , aintPosition(intTemp), _
				astrFieldName(intTemp)
		Next intTemp

		' Change OrdinalPosition data.
		For Each fldTemp In .Fields
			fldTemp.OrdinalPosition = 1

Next fldTemp

		' Open new Recordset object to show how the 
		' OrdinalPosition data has affected the record order.
		Debug.Print _
			"OrdinalPosition data from resulting Recordset."
		Set rstEmployees = dbsNorthwind.OpenRecordset( _
			"SELECT * FROM Employees")
		For Each fldTemp In rstEmployees.Fields
			Debug.Print , fldTemp.OrdinalPosition, fldTemp.Name
		Next fldTemp
		rstEmployees.Close

		' Restore original OrdinalPosition data because this is

' a demonstration.
		For intTemp = 0 To .Fields.Count - 1
			.Fields(astrFieldName(intTemp)).OrdinalPosition = _
				aintPosition(intTemp)
		Next intTemp

	End With

	dbsNorthwind.Close

End Sub

> -----Original Message-----
> From:	accessd-bounces at databaseadvisors.com
> [SMTP:accessd-bounces at databaseadvisors.com] On Behalf Of
> Drawbridge.Jack at ic.gc.ca
> Sent:	Wednesday, January 26, 2005 4:20 PM
> To:	accessd at databaseadvisors.com
> Subject:	[AccessD] ADO  - Getting a Tables columns back in physical
> order
> 
>  I'm sure someone has a solution to this.
> 
> I have A2K and am using ADOX to retrieve the structures of existing
> tables.
> 
> When I use this type of approach
> 
> Dim cat As New ADOX.Catalog
> Dim tbl As ADOX.Table
> Dim col As ADOX.Column
> cat.ActiveConnection = CurrentProject.Connection
> 
>  for each tbl in cat.Tables
>   If tbl.name = "Table I'm Looking For"  then 
>       for each col in tbl.Columns
>          debug.print col.name
>       next col
>   endif
>  next tbl
> 
>  I get the column names, but in ascending name order. How do I get the
> Column Names in the physical order in the table???  Does someone have
> sample code?
> 
> TIA
> 
> jack
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com