I have a query:

SELECT ClientID AS ID, Name AS [User Name]
FROM tblClients

Is it possible to use both in ADO? (my explanation of why I need to do this is too long):

rs.fields("ClientID")
  ...or...
rs.fields("ID")

I get an error saying that field is not a member of the collection...

It's not possible in ADO or anything else.

The alias in the SELECT clause names the column in the SQL resultset, before something like ADO even sees it. A column only has one name, which by default is the name of the original column.

You could duplicate the column in the resultset, i.e.:
(I can't imagine why anyone would want to do this to themselves. :) I'd be interested in hearing the explanation, even if it is long...)

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Hi,

I remember that I used a lot of times something similar to this and had no problems as U.N.C.L.E ;) had faced.

Here is what I have done recently.

Query=SELECT MAX(ProductId)+1 AS NewProductId FROM Products

And have accessed it using

rs("NewProductId")

Which worked... and was able to use its value for inserting a new record into Products.

Also used COALESC function in many occasions and accessed its values as Aliases.

Just wondering!!!...

Jeff,
This is somthing that we could face manytimes going for aliases, in case there is a necessity to access a userdefined column but not the original column from the table itself.

E.g. - May be as I mentioned above one may need to get the incremneted value from the table or sometimes would try to calculate something from the table and try to get it back as aliases in the frontend.

Hope this is what U.N.C.L.E would have tried to do.

Still eagar to hear his explanations, as I dont see any calculations done in his case.

-Vijay G

Thank you for your response.

The explanation is that I wanted to reuse some existing code that relies on the original field/column names. I planned to create several queries for the purpose of exporting information out of the database, but each client has different column name and record criteria requirements...

The capability in question would allow me to have one query for each client with the column names as per the client requirements but still be able to reuse the existing code to manipulate the recordset after export.

Thanks again anyway for your response.

I forgot to mention that what I did finally is to have two queries. Both with the same select criteria, but one with the original column names and one designed for export with client required column names.

Hope that makes some sense. If anyone has a better suggestion, I would be interested in hearing it, because as it is now, I now have to manage the criteria in two different places. Not too troublesome, but annoying never-the-less. :D

Yes, this would work, as the name of the column in the resultset (which gets turned into an ADO recordset by the OLEDB provider working with ADO) is in fact 'NewProductId'. That column only has that name, and you cannot refer to it by any other name, which is what I read the OP wanted to do.
Again, once you give a column an alias, no matter how the column was derived, be it a column from a table or an expression, you must refer to it by that name when you manipulate the resultset (recordset).
Referring to a column in a resultset which is the result of evaluating an expression is a good reason for a column alias, but not the only one...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

I do agree with you Jeff.