Accessing a column without its complete name

[marsg]

Hi

I'm not sure what I'm trying is feasible but I'd like your input.

I have *.csv created dynamically which I access in Crystal Report9 through ODBC (Micrsoft Jet). Everything works fine but I found a problem for deployment, the column name of the first column in the csv file changes according to timezone. I didn't find a way for it to have always the same name.

So my question is:
Is there a way in MS Jet or ODBC to get access to this table`s first column using a partial name (which would always start with the same prefix and no other columns would start by this prefix) or by an index?

I.e.: column name would start with "(PDH-CSV"

   SELECT
     table_name.(PDH-CSV% AS my_date,
     table_name.column2
   FROM
     table_name
   WHERE ...

Thanks a lot
Marcel

 

[jrbarnett]

Hi,

You can do

SELECT 1, 2
FROM Tablename
ORDER BY 1

will return the 1st and 2nd columns from table "tablename" and sort the result by the first column, without needing to know their names, but it will display Expr1001 type headers, so you may want to put an As clause after the name to give them a sensible title.

John

 

[marsg]

Thanks for your fast reply but unfortunately, it doesn't work.

When I do this, I get value 1 for every record in my first column.

I made a mistake when I said it was Microsoft Jet because when I make a mistake ODBC tells me it is a [Microsoft][ODBC Text Driver]. Unless Microsoft Jet is the DBMS handling the text datasource files...

I'm still trying some things but if you have other ideas, I'd like to hear about them

 

[marsg]

Thanks for your fast reply but unfortunately, it doesn't work.

When I do this, I get value 1 for every record in my first column.

I made a mistake when I said it was Microsoft Jet because when I make a mistake ODBC tells me it is a [Microsoft][ODBC Text Driver]. Unless Microsoft Jet is the DBMS handling the text datasource files...

I'm still trying some things but if you have other ideas, I'd like to hear about them

Marcel

 

[jrbarnett]

Hi,

If your problem is that the first column name changes according to the timezone, therefore it must write different titles to different files.

If this is all the problem is, you have inadvertendly answered your own question: In the source code - use a query (rather than a table directly) and use an As clause to have a fixed title for the first column, so when it gets written to the file, all the column 1's have identical titles regardless of the timezone.

John

 

[danvlas]

Select * From YourTable

will retrieve all columns with their current names.

Select 1, 2 doesn't work, as it indeed returns two calculated fields with the values of 1 and 2 respectively.

I'm not sure that can be done in SQL directly...I'd love to find a way, as I've struggled with this one for a long time and found nothing...

You could however modify QueryDef's SQL property

FirstField = CurrentDb.TableDefs("YourCsv&quot.Fields(0).Name

Set qdf=CurrentDb.QueryDefs("YourQuery&quot
qdf.SQL = "Select [" & FirstField & "] "
qdf.SQL = qdf.SQL & "From [YourCsv] Where ..."

and then use the query.

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html