| | |
|
|
|
How to get the order of a field? |
|---|
| message from Özden Irmak on 13 May 2004 |
Hi,
Is there any method/property in ADO or ADOX to get the order of a field in a
table?
Thanks,
Özden
|
| Brendan Reynolds replied to Özden Irmak on 13 May 2004 |
Public Function GetOrdinal(ByVal strTableName As String, ByVal strFieldName
As String) As Long
Dim rst As ADODB.Recordset
Dim lngLoop As Long
Dim lngResult As Long
'A result of -1 will indicate that no field with the
'specified name was found in the specified table.
lngResult = -1
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.Source = strTableName
.Open
For lngLoop = 0 To .Fields.Count - 1
If .Fields(lngLoop).Name = strFieldName Then
lngResult = lngLoop
Exit For
End If
Next lngLoop
.Close
End With
GetOrdinal = lngResult
End Function
|
| Özden Irmak replied to Brendan Reynolds on 13 May 2004 |
Hello,
I've miss pointed something I guess..
I can loop thorugh the fields collection to get fields but the order I get
the fileds are not the same seen on Access table design window, it seems
that they're ordered as alphabetically in the fields collection...
I want to get the fields in the same order as they are in the Access table
design window...
Kind Regards,
Özden
"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message
news:eDFxRbNOEHA.3452@TK2MSFTNGP10.phx.gbl...
|
| Brendan Reynolds replied to Özden Irmak on 13 May 2004 |
I can't reproduce that. When I modify the code as below, the list I get in
the Immediate window is in the same order as defined in the table. Note that
I'm opening the recordset directly on the table. If you used a SQL statement
or a saved query, I would expect the fields to be returned in the order in
which they appear in the SQL statement or query.
BTW: In theory, the order of the columns should be irrelevant (in relational
theory, both the columns and rows of a relation are unordered) and usually
is in Access, where you can almost always refer to a field by its name
rather than by its ordinal number.
For lngLoop = 0 To .Fields.Count - 1
If .Fields(lngLoop).Name = strFieldName Then
lngResult = lngLoop
'Exit For
End If
Debug.Print .Fields(lngLoop).Name
Next lngLoop
|
|
Archived message: How to get the order of a field? (MS Access)
