Using Delphi XE3 How Do I get a List Of Tables in MS Access AND Exclude Views / Queries?

Question

Using Delphi XE3 How Do I get a List Of Tables in MS Access AND Exclude Views / Queries ?

I have tried using ADOConnection1.GetTableNames - but it returns ALL Tables and Views (Queries).

I have also tried using a query eg. "SELECT * FROM MSysObjects WHERE Type=1 AND Flags=0" which requires a System.mdw file, however this causes more issues as a mdw file does not always exist.

I am wanting to develop an application that compares the Table structures of TWO mdb files, and creates a script to modify / synch the table structures within a database.

Any help greatly appreciated.

Answer 1

You can use the OpenSchema method passing the siTables value. And then filter the result using the TABLE_TYPE column of the returned dataset.

Try this sample code

{$APPTYPE CONSOLE}

{$R *.res}

uses
  SysUtils,
  ActiveX,
  ComObj,
  Adodb,
  Variants;


procedure ListTables(const FileName : string);
var
  LADOConnection : TADOConnection;
  LADODataSet: TADODataSet;

begin
  LADOConnection := TADOConnection.Create(nil);
  try
    LADOConnection.ConnectionString := Format('Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%s;Persist Security Info=False;', [FileName]);
    LADODataSet := TADODataSet.Create(nil);
    try
     LADODataSet.Connection := LADOConnection;
     LADOConnection.OpenSchema(siTables, EmptyParam, EmptyParam, LADODataSet);

     LADODataSet.Filter   := '(TABLE_TYPE = ''TABLE'')'; //show only the tables
     LADODataSet.Filtered := True;

     while not LADODataSet.EOF do
     begin
      Writeln(Format('Name %s Type %s',[LADODataSet.FieldByName('TABLE_NAME').AsString, LADODataSet.FieldByName('TABLE_TYPE').AsString]));

      LADODataSet.Next;
     end;

    finally
     LADODataSet.Free;
    end;
  finally
    LADOConnection.Free;
  end;
end;

begin
 try
    CoInitialize(nil);
    try
       ListTables('C:\Test\Northwind.MDB');
    finally
      CoUninitialize;
    end;
 except
    on E:EOleException do
        Writeln(Format('EOleException %s %x', [E.Message,E.ErrorCode]));
    on E:Exception do
        Writeln(E.Classname, ':', E.Message);
 end;
 Writeln('Press Enter to exit');
 Readln;
end.