Scalabium Software

#168:How can I process the records from query in MS Access?

Today I want to show how you may execute the stored query in MS Access database (mdb-file) and process the records in query (view).

In the sample code below you may see how do it in Delphi:

var
  access, db, td, queryDef, curField, Value: Variant;
  str: string;
  i: Integer;
begin
  { load a DAO in memory }
  access := CreateOleObject('DAO.DBEngine.36');

  {open a database}
  db := access.OpenDatabase(yourMDBFileName);

  {find the query by name}
  queryDef := NULL;
  td := db.QueryDefs;
  for i := 0 to td.Count-1 do
    if (td.Item[i].Name = TableName) then
    begin
      queryDef := td.Item[i];
      td := UnAssigned;
      break
    end;

  if not VarIsNull(queryDef) and not VarIsEmpty(queryDef) then
  begin
    recordset := queryDef.OpenRecordset;

    {navigate thru records in query}
    while not recordset.Eof do
    begin
      for i := 0 to recordset.Fields.Count-1 do
      begin
        curField := recordset.Fields.Item[i];
        str := curField.Name; // field name
        Value := curField.Value; // field value

        <do something with value>
      end;

      recordset.MoveNext;
    end;

    {close recordset instance}
    recordset.Close;
    recordset := NULL;
  end;

  {close database and DAO}
  db.Close;
  db := NULL;
  access := UnAssigned;
end;

Copyrightc 1998-2008, Scalabium Software. All rights reserved.
webmaster@scalabium.com

November 21, 2005