How to query table structures from an access database?

Question

I want to get the structure of all the tables and odbc-Datasources in an Access database with C#. So I tried this code:

string text="";
var tables = GetApp().CurrentData.AllTables;
for (int i = 0; i < tables.Count; i++)
{
    var currentTable = tables.Item(i);
    text = text + currentTable.FullName + Environment.NewLine;
}
MessageBox.Show(text);

This returns all the availible tablenames. But how do I get the columnnamens and types of the tables?

I tried this:

var props = currentTable.Properties;
for (int j = 0; j < props.Count; j++)
{
    var prop = props.Item(j);
    text = text + Environment.NewLine + prop.Name;
}

but it didnt work (An exception was thrown when I accessed the properties).

I tried to use a oleDB connection + GetSchema to get the table structure. But with this method I only received the "native" (or local) tables inside of the access-db. But there are also some ODBC-Linked-Tables which I am also interested.

So how is it possible to get the TableNames, ColumnNames and Columntypes in an ms-access database file?

Erik A · Accepted Answer · 2019-01-07 14:09:29Z

4

You need to use CurrentDb().TableDefs if you want to access table columns. The AllTables collection doesn't offer access to the table fields.

CurrentDb().TableDefs[0].Fields[0].Name should return the name of the first column of the first table, for example.

answered Jan 7, 2019 at 14:09

Erik A

32.8k13 gold badges49 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to query table structures from an access database?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related