Select Ordinal position

[FederalProgrammer]

I am trying to write a SQL-Query that Selects a column based on its position on the table... ie, I want the 4th column of my table, but I don't know its name...
Is there a way to do this?

thanx in advance!

 

[cmmrfrds]

You can pick off the 4th column from the Fields Collection on the table, then build a dynamic sql statement with the column name.

 

[FederalProgrammer]

I'm not sure I follow?
The table is in the database... what collection are you exactly referring?
do you mind elaborating a bit further please?
thanx!

 

[cmmrfrds]

I assume an Access database or whatever database is on the CurrentProject.Connection.

'-- set reference to ADOX library
'- Microsoft ADO Ext. 2.6 for DDL and Security
'-- Microsoft ActiveX data objects 2.6 library also needed for ADO

Dim cg As New ADOX.Catalog
Dim tb As New ADOX.Table
Dim cn As ADODB.Connection
Dim cl As Column
Dim pp As Property

Set cg.ActiveConnection = CurrentProject.Connection

Set tb = cg.tables("yourtable")
Set cl = tb.Columns(3)
Debug.Print "property name = "; cl.Name

 

[FederalProgrammer]

Thanx a lot...
I will try it!
cheers!

 

[PHV]

Anyway, strange question, as in a relational database the order of the columns in a row is irrelevant...

 

[FederalProgrammer]

Well, PHV
I need to access the 4th column of my table not knowing what the name of the column is going to be. How would you do it?!