How can I get all field names for a MS Access Table?

  • Thread starter Thread starter Ron Dahl
  • Start date Start date
R

Ron Dahl

Is there any way to display all Field names for a specific Access table?

Something like " SELECT Column_Names FROM MyTable"

Thanks in advance for any help.
Ron Dahl
 
Hi Ron,

i quess you work with a SQL-Database. All SQL-Databases hold metadata
in the system catalog. You can find all descriptive data of the objects of
the
database there.
Systables and syscolumns are a good starting point.
 
There are several ways to do this. One of the easiest is to add a listbox to
a form, set the RowSourceType property to "Field List", and set the
RowSource property to the name of the table.
 
Thanks Hansjörg.

I believe the system catalog was called "dict" in Oracle and I believe that
I could use "Select * from dict" to get this information. (It's been awhile
and I may have the names slightly wrong).

Do you or anyone know the name of the system catalog in MS Access?

I assume then I could use "Select Systables From Dict" or "Select Syscolumns
From Dict" by substituting the proper system catalog name for "Dict".

Thanks, Ron
 
I forgot to mention quite a few things. I am using vb DotNet and creating a
DataAdapter and DataSet at runtime. It appears that RowSourceType and
RowSource are properties of the MS Access object, and I wasn't setting any
reference to MS Access. Is there an SQL statement that I could use similar
to "Select FieldList from MyTable".

I apologize if I misunderstood your reply, but I can't find any
RowSourceType or RowSource for my listbox in VB DotNet.

Thanks
Ron Dahl
 
Hi Ron,

Because this is the microsoft.public.access.adp.sqlserver newsgroup, people
are likely to assume that you're working with an Access ADP (which would
mean that the data was stored in SQL Server) unless you state otherwise.

The Access/Jet system tables are undocumented. While some people have
managed to figure out how to extract various information from them, I have
not heard of any way to extract field names for a specified table. When I
look at the system tables in an Access MDB, I don't see anything that looks
like field names. Perhaps this information may be stored as binary data in
one of the various OLE Object fields in the system tables, but that's just a
guess on my part.

One way to get this information in a .NET app would be to use the FillSchema
method of the DataSet object ...

using System;
using System.Data;
using System.Data.OleDb;
namespace ConsoleApplication16
{
class Class1
{
[STAThread]
static void Main(string[] args)
{
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;"
+ @"Data Source=c:\dsdata\northwind.mdb;"
+ @"Persist Security Info=False";
OleDbConnection connection = new System.Data.OleDb
.OleDbConnection(connectionString);
OleDbCommand selectCommand = new System.Data.OleDb
.OleDbCommand("SELECT * FROM Categories", connection);
OleDbDataAdapter dataAdapter = new System.Data.OleDb
.OleDbDataAdapter(selectCommand);
DataSet dataSet = new System.Data.DataSet();
dataAdapter.FillSchema(dataSet,System.Data.SchemaType.Source);
DataColumnCollection columns = dataSet.Tables[0].Columns;
foreach (System.Data.DataColumn dataColumn in columns)
{
Console.WriteLine(dataColumn.ColumnName);
}
Console.ReadLine();
}
}
}
 
hello.
perhaps I'm misinterpreting the question but couldnt you just throw a little
code up in your asp like so:
(of course, my example assumes that you create a DB connection and open a
recordset. In this example it would be a recordset set to the variable RS)

<%
dim fld
for each fld in RS.fields %>

<%= fld.name %> <--- or here you would do whatever else
you wanted to with the tables
field
names beside display them.

<%
next
%>
 
Thanks Brendan,

In my particular case, I ended up using:

Dim MyDataTableForCheckedListBoxColumnNamesDataSource As New DataTable
MyDataTableForCheckedListBoxColumnNamesDataSource = _

myOleDbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, _

New Object() {Nothing, Nothing, strTableName, Nothing})

I should have pointed out that I'm a Newbie in this forum. I don't even
understand the relationship between SQLServer and MS Access, but I enjoy
learning from you guys and will keep on reading posts.
Thanks again for your help.
Ron


Brendan Reynolds said:
Hi Ron,

Because this is the microsoft.public.access.adp.sqlserver newsgroup,
people are likely to assume that you're working with an Access ADP (which
would mean that the data was stored in SQL Server) unless you state
otherwise.

The Access/Jet system tables are undocumented. While some people have
managed to figure out how to extract various information from them, I have
not heard of any way to extract field names for a specified table. When I
look at the system tables in an Access MDB, I don't see anything that
looks like field names. Perhaps this information may be stored as binary
data in one of the various OLE Object fields in the system tables, but
that's just a guess on my part.

One way to get this information in a .NET app would be to use the
FillSchema method of the DataSet object ...

using System;
using System.Data;
using System.Data.OleDb;
namespace ConsoleApplication16
{
class Class1
{
[STAThread]
static void Main(string[] args)
{
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;"
+ @"Data Source=c:\dsdata\northwind.mdb;"
+ @"Persist Security Info=False";
OleDbConnection connection = new System.Data.OleDb
.OleDbConnection(connectionString);
OleDbCommand selectCommand = new System.Data.OleDb
.OleDbCommand("SELECT * FROM Categories", connection);
OleDbDataAdapter dataAdapter = new System.Data.OleDb
.OleDbDataAdapter(selectCommand);
DataSet dataSet = new System.Data.DataSet();
dataAdapter.FillSchema(dataSet,System.Data.SchemaType.Source);
DataColumnCollection columns = dataSet.Tables[0].Columns;
foreach (System.Data.DataColumn dataColumn in columns)
{
Console.WriteLine(dataColumn.ColumnName);
}
Console.ReadLine();
}
}
}

--
Brendan Reynolds (MVP)

Ron Dahl said:
I forgot to mention quite a few things. I am using vb DotNet and creating
a DataAdapter and DataSet at runtime. It appears that RowSourceType and
RowSource are properties of the MS Access object, and I wasn't setting any
reference to MS Access. Is there an SQL statement that I could use
similar to "Select FieldList from MyTable".

I apologize if I misunderstood your reply, but I can't find any
RowSourceType or RowSource for my listbox in VB DotNet.

Thanks
Ron Dahl
Click to expand...
Click to expand...
 
Back
Top