FireDAC.MSAccess Sample

This sample demonstrates how to use FireDAC to work with MS Access databases.

Location

You can find the MSAccess sample project at:

• Start | Programs | Embarcadero RAD Studio 10.1 Berlin | Samples and then navigate to:
  • Object Pascal\Database\FireDAC\Samples\Getting Started\MSAccess
• Subversion Repository:
  • Delphi: http://sourceforge.net/p/radstudiodemos/code/HEAD/tree/branches/RADStudio_Berlin/Object%20Pascal/Database/FireDAC/Samples/Getting%20Started/MSAccess

Description

The MSAccess getting started sample shows how to use FireDAC with MS Access databases. This application sample provides the following features to the user:

• Temporary connection definition at run time.
• Master-details relationship between datasets using TFDQuery.
• Management of MS Access databases, such as: creating and compacting a database.

How to Use the Sample

1. Navigate to the location given above and open GettingStarted.dproj.
2. Press F9 or choose Run > Run.
3. Click on the Connection combo box and select <Open database…>. Open the following database: C:\Users\Public\Documents\Embarcadero\Studio\17.0\Samples\data\FDDemo.mdb.
4. Interact with the sample.

Files

File in Delphi	Contains
GettingStarted.dproj GettingStarted.dpr	The project itself.
fGettingStarted.pas fGettingStarted.fmx	The main form.

Implementation

The sample implements the following features:

Creating Temporary Connection Definition

The simplest way to configure connection to MS Access database at run time is to build a temporary connection definition:

with dbMain do begin
  Close;
  // create temporary connection definition
  with Params do begin
    Clear;
    Add('DriverID=MSAcc');
    Add('Database=x:\path_to_db\db.fdb');
  end;
  Open;
end;

In the sample, the temporary definition is created when the <Open database…> item is selected in the Connection combo box.

Master Details

In the demo database, the Categories and Products tables have one-to-many relation by CategoryID field. The qryCategories.SQL property is set up as follows:

select * from Categories

In addition, the qryProducts.SQL property is set up as follows:

select * from Products
where CategoryID = :CategoryID

Finally, the qryProducts.MasterSource property is set to dsCategories, while the MasterFields property is set to CategoryID. This creates a master-details relationship between the datasets.

Executing Queries

Simple queries execution is demonstrated via ExecSQL method of TFDConnection.

Inserting a record providing parameters of various types:

dbMain.ExecSQL('insert into Categories(CategoryName, Description, Picture) ' +
               'values(:N, :D, :P)', ['New category', 'New descr', $0334]);

Updating records:

 dbMain.ExecSQL('update Products set UnitPrice = UnitPrice * :P1 + :P2 ' +
                'where ProductID < 3', [Random(5), Random(3)]);

Deleting a record:

dbMain.ExecSQL('delete from Categories where CategoryName like :N', ['New category']);

Getting a scalar value from the database:

iID := dbMain.ExecSQLScalar('select MAX(CategoryID) from Categories');

Management of MS Access database

The management of databases, such as: creating, dropping, compacting/repairing, and setting a password is done using TFDMSAccessService component.

This demo demonstrates how to create and compact the user database:

with accService do begin
  Password := 'p@ssword';
  Database := 'x:\path_to_database\db.mdb';
  CreateDB;
end;
…
with accService do begin
  Password := 'p@ssword';
  Database := 'x:\path_to_database\db.mdb';
  Compact;
end;

Uses

• TFDQuery
• TFDConnection
• TFDMSAccessService
  • CreateDB
  • Compact

See Also

• Defining Connection (FireDAC)
• Connect to Microsoft Access Database (FireDAC)
• Master-Detail Relationship (M/D)
• Mobile Tutorial: Using FireDAC and SQLite
• MS Access Questions (FireDAC)

Samples

• FireDAC InterBase sample
• FireDAC MS SQL sample
• FireDAC MySQL sample
• FireDAC SQLite sample
• FireDAC Firebird sample