how to get column name for a table in MS-Access?

[jerry way]

Hi,

Does anyone know how to get column names for a specific table in Access?

For Sql Server, if I need to get column names for a specific table, I can use SQL statement "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = *"; I wonder how if there is a similiar way to achieve the same in Access?

Thanks for your help in advance!

[kalpesh at vsnl dot net]

Not the same way though.
But, you can use a "SELECT * FROM mytable where 1 = 2" statement, this
will select 0 rows & will bring all the columns, which you can then find
details of using DataSet

and this will work for all databases ;-)

HTH
Kalpesh

[William Ryan]

You have two options, 1) Use a 'connected' object (which is more economical
in most instances depending on what you want to do withthe information 2)
use a Disconnected object.

//Connected
You can use a DataReader and set the command's SchemaOnly option on the
Command.ExecuteReader(SchemaOnly). This will bring over the schema (ie
column info) but won't bring back any table data b/c it doesn't actually
execute the query. There's another option KeyInfo that will bring back info
indicating if the column is/is part of a key, but if you use the Reader's
SchemaOnly, you don't even need to do this...it will come back
automatically.

If you want to execute the query in addition to getting the schema
information you can declare a datatable and use

myDataTable = myDataReader.GetSchemaTable.. However, from the looks of your
question, the first one is probaby what you'll want to use.

//Disconnected.

once you call dataAdapter.Fill(myDataSet, "MyDataTable") your table will
have a columns collection which is enumerable. So you can use something
like

foreach(DataColumn dc in myDataSet.Tables[0].Columns)
{
Debug.Writeline(dc.Name);
}

As you can see, if you use the disconnected method, you have a few
additional items of overhead. 1 ) You need at least a Connection, Command,
DataSet/DataTable, DataAdapter vs. Connnection, Command, DataReader
2) You have to declare a DataColumn and walk through the collection (but in
all honesty, this is a trivial distinction b/c you still need to iterate
through them in a different manner with the connected way.

HTH,

Bill
"jerry way" <anon...@discussions.microsoft.com> wrote in message
news:DEE7E854-D465-40DC...@microsoft.com...

[jerry way]

Thanks! Your solution (connected one) is simple and elegent, and is exactly what I am looking for...

Jerry