How to get the order of a field?

  • Thread starter Thread starter Özden Irmak
  • Start date Start date
Ö

Özden Irmak

Hi,

Is there any method/property in ADO or ADOX to get the order of a field in a
table?

Thanks,

Özden
 
If you mean the ordinal position of the field, the only way I can think of
is to loop through the fields comparing the names ...

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

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
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
 
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

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Back
Top