ADO Fields - order of retrieval?

[Guest]

I am using ADO to retrieve and append data from the web to an Access table.
Is there a way to determine the order in which the fields of the table
recordset will be cycled? This is important so that I can ensure that the
columns of data retrieved from the web match the order of fields into which
that data will be appended.

I know that I can use the field names to set the order in which they are
used. That is NOT my question. For other considerations (fields may be
added or removed later), I would like to try to determine their order as
returned using the following For loop:

dim rst as adodb.recordset
set rst = new adodb.recordset

For j = 1 To rst.Fields.Count 'UBound(arTableDesc, 1)
vFNames(j) = rst.Fields.Item(j - 1).Name
Next j

The order in which they are returned here is different than the order the
fields are returned in a For each fld in rst.fields loop. The latter seems
to be in the order in which the fields were created, for former - I can't
figure out how the order was derived. Initially, it seemed it was based on
the column order in the table. Changing the field order in Table design,
however, did NOT change the field order as determined by the For J = 1 to
rst.Fields.Count loop.

I'm sure there are 50 different ways of solving this, and I may use one of
them, but if anyone knows where the ordinal numbers are stored (I can't find
them in any of the field object properties) or set, I'd really like to know.

Thanks for the help.

 

[Douglas J. Steele]

You don't show the SQL you're using to create the recordset. Does it use
SELECT *, or does it list each of the fields individually?

 

[Guest]

The rst was created using the
rst.open strtable, cnn ... , cmdtable

I've subsequently discovered that DAO tabledef has an field ordinal
property. ADO doesn't seem to.
dim db as dao.database
dim tbl as dao.tabledef
dim i as integer, intOrdinal as integer

set db = currentDb
set tbl = db.tabledefs(strTable)

with tbl
for i = 1 to .Fields.Count
intOrdinal = .Fields(i-1).OrdinalPosition
next i
end with

Thanks for the look though. I wonder why you can't access the
..OrdinalPosition property from ADO?

 

['69 Camaro]

Hi.

Is there a way to determine the order in which the fields of the table
recordset will be cycled? This is important so that I can ensure that the
columns of data retrieved from the web match the order of fields into
which
that data will be appended.

If your application depends upon the order of the columns retrieved in the
Recordset, then you're going to have some severe programming maintenance
problems in the future, because one cannot guarantee that a relational
database's columns will always be in the same order when queried. By Codd's
rules, the column order doesn't matter.

For other considerations (fields may be
added or removed later),

This sounds like the database design is unstable, which is going to become a
programming maintenence nightmare and a risk to data integrity. Why are
columns going to be added or removed? Is the table not normalized? Do you
keep adding columns for each new month, new year, new user, new category, et
cetera?

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Aaron Kempf]

codd is full of crap
codd is full of crap
codd is full of crap

and I think that MS should give us a way to sort the column names

Select * {ABC} from MyTable (would give you all the columns in alphabetical
order)
Select * {like '%ID'} from MyTable (would give you all the columns that end
in ID)


I really think that these should be adopted into TSQL
this whole LINQ _CRAP_ that they're shoving down our throats isn't going to
make our job easier

simple enhancements like this would help newbies understand SQL