How to find list of all tables in Access Database matching certaing format in Delphi

Question

I need to compute a list of table names in a given database (MDB format), which have certain format (for example which containt DateTime field "Date Applied"). How can I do it?

Note: I am using Delphi 7, ADO components, Microsoft JET 4.0 to connect to MDB type Database.

Answer 1

A schema may suit:

   Set rs = cn.OpenSchema(adSchemaColumns, _
   Array(Empty, Empty, Empty, SelectFieldName))

Where adSchemaColumns = 4
rs is a recordset object
cn a connection object
SelectFieldName is the column name, "Date Applied" in this case.

The constraints for this schema are:

TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME

Columns (fields) returned are:

TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
COLUMN_GUID
COLUMN_PROPID
ORDINAL_POSITION
COLUMN_HASDEFAULT
COLUMN_DEFAULT
COLUMN_FLAGS
IS_NULLABLE
DATA_TYPE
TYPE_GUID
CHARACTER_MAXIMUM_LENGTH
CHARACTER_OCTET_LENGTH
NUMERIC_PRECISION
NUMERIC_SCALE
DATETIME_PRECISION
CHARACTER_SET_CATALOG
CHARACTER_SET_SCHEMA
CHARACTER_SET_NAME
COLLATION_CATALOG
COLLATION_SCHEMA
COLLATION_NAME
DOMAIN_CATALOG
DOMAIN_SCHEMA
DOMAIN_NAME
DESCRIPTION

-- Obtaining Schema Information from a Database

Answer 2

Here's a vbscript/asp type solution. You can adapt this to Delphi

Const adSchemaTables = 20


Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = 'C:\Scripts\Test.mdb'" 

Set objRecordSet = objConnection.OpenSchema(adSchemaTables)

Do Until objRecordset.EOF
    Wscript.Echo "Table name: " & objRecordset.Fields.Item("TABLE_NAME")
    Wscript.Echo "Table type: " & objRecordset.Fields.Item("TABLE_TYPE")
    Wscript.Echo
    objRecordset.MoveNext
Loop

Answer 3

There are two methods available on the connection component which are useful for this kind of work. The first is:

procedure GetTableNames(List: TStrings; SystemTables: Boolean = False);

which populates a TStrings descendant with a list of all of the tables available in the current database. The next method is:

procedure GetFieldNames(const TableName: string; List: TStrings);

which populates a list of all fields for a specific table. You then can create a simple routine to loop through all fields for all tables for the specific field you are looking for.