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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
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
 
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 said:
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
Click to expand...
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! Your solution (connected one) is simple and elegent, and is exactly what I am looking for...

Jerry
 
Back
Top