I always had problems with MS Access when I use it as my backend database. The biggest issue was that you cannot get the system information easily. For example: tables, columns and index etc.
Most of enterprise databases don't have this problem such as MS SQL Server, Oracle etc. But MS Access is still widely used today.
Day by day, I finally cannot stand this problem and force myself to solve this issue. It turned out that with .NET, the OLDEB provider already provides a great method GetOldDbSchedmaTable() to retrieve these information. Damn, I should know it early. That's why I want to post my discovery here so that you guys don't waste time. Here is the sample code with C#:
OleDbConnection cn = new OleDbConnection("YourConnectionStringHere");
cn.Open();
DataTable dt = new DataTable();
dt = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] {null, null, null, "Table"});
cn.Close
Below is a list of returned table column names with its index:
0: TABLE_CATALOG
1: TABLE_SCHEMA
2: TABLE_NAME
3: TABLE_TYPE
4: TABLE_GUID
5: DESCRIPTION
6: TABLE_PROPID
7: DATE_CREATED
8: DATE_MODIFIED
Get ForeignKey:
DataTable dt = new DataTable();
dt = DAConnection.GetOleDbSchemaTable(OleDbSchemaGuid.ForeignKey, null);
0: PK_TABLE_CATALOG
1: PK_TABLE_SCHEMA
2: PK_TABLE_NAME
3: PK_COLUMN_NAME
4: PK_COLUMN_GUID
5: PK_COLUMN_PROPID
6: FK_TABLE_CATALOG
7: FK_TABLE_SCHEMA
8: FK_TABLE_NAME
9: FK_COLUMN_NAME
10:FK_COLUMN_GUID
11:FK_COLUMN_PROPID
12:ORDINAL
13:UPDATE_RULE
14:DELETE_RULE
15:PK_NAME
16:FK_NAME
17:DEFERRABILITY
Get indexes:
DataTable dt = new DataTable();
dt = DAConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Indexes, null);
0: TABLE_CATALOG
1: TABLE_SCHEMA
2: TABLE_NAME
3: INDEX_CATALOG
4: INDEX_SCHEMA
5: INDEX_NAME
6: PRIMARY_KEY
7: UNIQUE
8: CLUSTERED
9: TYPE
10:FILL_FACTOR
11:INITIAL_SIZE
12:NULLS
13:SORT_BOOKMARKS
14:AUTO_UPDATE
15:NULL_COLLATION
16:ORDINAL_POSITION
17:COLUMN_NAME
18:COLUMN_GUID
19:COLUMN_PROPID
20:COLLATION
21:CARDINALITY
22:PAGES
23:FILTER_CONDITION
24:INTEGRATED
Please also read this article:
http://www.kbalertz.com/kb_309681.aspx