SQL question: field name changes but is always the 1st field

  • Thread starter Thread starter AC
  • Start date Start date
A

AC

Hi

I have a data table which is often dropped and replaced by another
table which does not always have the same field names. It always has
the same number of fields and in identical order, but the names of the
fields sometimes change. I was wondering if there is a way to exploit
this last fact. So...

Is there a way to write an SQL statement which is something like:

SELECT <field1_regardless_of_name> FROM myTable

The field I want is always, and will always be, the 1st field in the
table.



The reason for this happening is the table gets read in from an Excel
workbook and sometimes the user has changed the table headings (but
never the number of fields or the order). And then someone removes
the original table and shifts this table into the database.

Of course I could just run a quick bit of code to rename the 1st field
before my query and this way I can always guarantee it will have the
name my SQL expects, but I was wondering if I can avoid having to do
that.

Thanks
AndyC
 
You need to reference the table definition in the db or the recordset. Walk
down through the
TableDefs until you hit one where the tableDefs(lngTable).name = your table
name.
Then grab the TableDefs(lngTable).Fields(0).Name.

That will give you the name of your first field in the table of interest
which you can then reference
in your query.

There may be a way of addressing the ordinal position of the first column
but I do not
know how to do that in the select statement



The references will be something like:
db.TableDefs(lngTable).Name
and
db.TableDefs(lngTable).Fields(lngField).Name
 
You need to reference the table definition in the db or the recordset.  Walk
down through the
TableDefs until you hit one where the tableDefs(lngTable).name = your table
name.
Then grab the TableDefs(lngTable).Fields(0).Name.

That will give you the name of your first field in the table of interest
which you can then reference
in your query.

There may be a way of addressing the ordinal position of the first column
but I do not
know how to do that in the select statement

The references will be something like:
db.TableDefs(lngTable).Name
and
db.TableDefs(lngTable).Fields(lngField).Name













- Show quoted text -
Click to expand...

Thanks

Having to edit the table def is what I suspected, but am still holding
out hope it can be done in a SQL statement (albeit a very very very
small hope).

AndyC
 
Back
Top