What is UniDAC

UniDAC (Universal Data Access Components), developed by Devart, is a comprehensive library of components designed to provide direct access to various databases from Delphi, C++Builder, and Lazarus.

With UniDAC’s server-independent interface, you can work across different databases and change the client engine for any server type by modifying a single connection setting. This flexibility simplifies the process of switching between database servers in a cross-database environment.

Ease of setup and use

UniDAC is straightforward to install and use, any integrations with it can be set up in minutes, which ensures a quick and hassle-free start for developers.

Server-aware providers

UniDAC uses server-aware providers to optimize performance, guaranteeing that data manipulation operations are executed in the most efficient manner.

Cross-platform compatibility and cross-IDE support

UniDAC supports multiple platforms and IDEs, including Delphi, C++Builder, Lazarus, and more. It allows development on Windows, macOS, Linux, iOS, and Android for both x86 and x64 architectures, along with support for the FireMonkey platform.

Server-independent SQL

UniDAC features a powerful macros engine that enables server-independent SQL, allowing dynamic SQL generation and cross-database support within a single application.

Access to cloud services

UniDAC supports integration with popular cloud services such as Salesforce, QuickBooks, Zoho CRM, and more, enabling cloud-based data management in applications.

Secure connection

UniDAC can be integrated with SecureBridge components to embed secure protocols like SSL, SSH, or HTTPS, allowing for secure connections to the database server.

Support for EntityDAC

UniDAC works with EntityDAC, allowing developers to use Object-Relational Mapping (ORM) for Delphi, with support for LINQ, making database queries more intuitive and streamlined.

1. Start your RAD Studio.
2. Open or create a new project. To do this, navigate to File > New > Windows VCL Application – Delphi.

3. From the UniDAC category on the Component palette, drag and drop the TUniConnection component onto your form

4. Double-click the UniConnection1 label on the form, and in the dialog that opens, configure the following TUniConnection properties.

• In the Provider dropdown menu, select Access.
• In the Database field, enter the path to the Microsoft Access file.
• Clear the LoginPrompt checkbox.

5. On the Options tab, set the Direct property to True.

6. On the Connect tab, click Connect.

Configure connection to Microsoft Access via UniDAC

You can use UniDAC components like TUniQuery, TUniTable, or TUniStoredProc to execute SQL queries, access tables, or perform other operations with data in your Microsoft Access database. Below is an example of how to configure a connection to an Access database file directly in your code.

procedure TForm1.ConnectToAccess;
begin
  UniConnection1.ProviderName := 'Access'; // Set provider to Microsoft Access
  UniConnection1.Database := 'C:\path\to\your\database.mdb'; // Specify path to your
Access file
  UniConnection1.LoginPrompt := False; // Disable login prompt
  UniConnection.SpecificOptions.Values['Direct'] := 'True'; // Enable direct connection
try
  UniConnection1.Connect; // Attempt to connect
  ShowMessage('Connection successful!');
except
  on E: Exception do
    ShowMessage('Failed to connect: ' + E.Message);
  end;
end;

This code example can be placed within a method that is called when your form is created or triggered by a specific event. It establishes a direct connection to the Access file without requiring Microsoft Access to be installed on the machine.

UniDAC allows you to connect directly to Microsoft Access and retrieve data from it. To retrieve data from Microsoft Access:

1. From the UniDAC on the Component palette, drag and drop the TUniQuery and TUniDataSource components onto your form.
2. Then, drag and drop the TAccessUniProvider component onto your form from the UniDAC Providers category.
3. Similarly, add the TButton component from the Standard category and TDBGrid from the Data Controls category.

4. Double-click the UniQuery1 label in the Form Designer and enter a SQL query to select the data from the Microsoft Access database.

For example,

SELECT * FROM emp;

5. Click OK to save changes and close the dialog.

6. Click the DBGrid1 label in the Form Designer and set the DataSource property to the UniDataSource1 value in the Object Inspector.
7. Click the UniDataSource1 label in the Form Designer and set the DataSet property to the UniQuery1 value in the Object Inspector.
8. Call the Open method in your application to execute the SQL query: double-click the Button1 label, and in the Code Editor that opens, add the following code:

UniQuery1.Open

The complete procedure code should look like this:

procedure TForm2.Button1Click(Sender: TObject);
begin
  UniQuery1.Open
end;

end.

9. Click Run
10. In the window that opens, click the button to display the result.

Steps to view tables in a Microsoft Access database using UniDAC in RAD Studio (Design mode):

1. From the Component Palette, under the UniDAC category, add the TUniTable component to your form.
2. Double-click the TUniTable1 label on your form to open the configuration dialog.
3. In the dialog that appears, open the Table Name dropdown list to view and select tables from your Access database.

The TUniTable component in UniDAC is designed to work with entire database tables in a dataset-like manner. It allows for easy access to table data without writing SQL queries. You can set the TableName property to the desired table, open the connection, and retrieve or modify data.

View the list of database tables using TUniQuery:

1. In the Form Designer, double-click the UniQuery1 label to open the query editor.
2. Enter the following SQL query to select the list of tables in a Microsoft Access database:

SELECT Name
FROM MSysObjects
WHERE Type = 1;

To exclude system tables, modify the query as follows:

SELECT Name
FROM MSysObjects
WHERE Type = 1 AND Name NOT LIKE 'MSys%';

3. Click OK to close the dialog.
4. Click Run to execute the query.

1. Right-click the UniQuery1 label on your form and select Fields Editor from the shortcut menu.
2. In the Fields Editor window, right-click inside the window and select Add All Fields from the context menu.

Alternatively, you can select New Field to add fields manually.

3. Manage field properties. After adding fields, you can manage their properties in the Object Inspector. Select each field in the Fields Editor to view and edit its properties, such as DisplayLabel, Visible, ReadOnly, Alignment, etc.

The TUniQuery component can be used not only for selecting data but also for executing any queries supported by the database server. For example, to update records in the emp table using an UPDATE statement, follow these steps:

1. Double-click the TUniQuery component to open the SQL editor.
2. Enter your query, such as:

UPDATE emp
SET Field1 = 'ANDERSEN', Field2 = 'ACCOUNTANT'
WHERE ID = 2; 

3. Execute the query at design-time by clicking Run in the editor.

To execute the query at runtime, simply call the Execute method of TUniQuery in your code:

1. Double-click the TUniQuery component to open the SQL editor.
2. Enter your query, such as:

UPDATE emp
SET Field1 = 'ANDERSEN', Field2 = 'ACCOUNTANT'
WHERE ID = 2; 

3. Execute the query at design-time by clicking Run in the editor.

To execute the query at runtime, simply call the Execute method of TUniQuery in your code:

UniQuery1.Execute;

To edit data, simply click any cell within the TDBGrid. This automatically invokes the Edit method, enabling you to modify the cell's contents. After making your changes, navigate to another record to save the update—this automatically triggers the Post method. If you want to cancel the changes to the current record, press ESC to revert the data before saving.

You can use the following code to modify data programmatically at runtime:

procedure TForm1.UpdateEmp;
begin
  UniQuery1.Edit;
  UniQuery1.FieldByName('Field1').AsString := 'Andersen';
  UniQuery1.FieldByName('Field2').AsString := 'Accountant';
  UniQuery1.FieldByName('Field3').AsInteger := 1234;
  UniQuery1.FieldByName('Field4').AsDateTime := StrToDate('1990-01-01');
UniQuery1.Post;
end;

Add new records

To add new records, use TDBNavigator, which automatically triggers the Append or Insert method. TDBNavigator enables users to control the dataset when editing or viewing data. When you click a button on the navigator, the corresponding action is performed on the linked dataset. For example, clicking the Insert button adds a new blank record to the dataset.

1. From the Data Controls category on the Component palette, drag and drop the TDBNavigator component onto your form
2. Bind TDBNavigator to the Data Source.
   • 2.1 Select the TDBNavigator component on your form.
   • 2.2 In the Object Inspector, set the DataSource property of TDBNavigator to UniDataSource1.
3. Click Run and use the TDBNavigator buttons to insert new records and write changes to the database.

Delete records

Similarly, you can use TDBNavigator to delete records. The Delete method is called automatically.

To delete records from a Microsoft Access database, bind TDBNavigator to the UniDataSource. Then, run the application, select the record you want to delete, and click the Delete button on the navigator. The selected record will be removed from the database.

Insert records at runtime:

1. Use the Append method to create a new blank record.
2. Assign values to the necessary fields using FieldByName.
3. Use the Post method to save the new record to the database.

procedure TForm1.InsertRecord;
begin
  UniQuery1.Append; // Start appending a new record
  UniQuery1.FieldByName('Field1').AsString := 'Smith'; // Assign values to fields
  UniQuery1.FieldByName('Field2').AsString := 'Manager';
  UniQuery1.FieldByName('Field3').AsInteger := 1234;
  UniQuery1.FieldByName('Field4').AsDateTime := Now;
UniQuery1.Post; // Save the record to the database
end;

Delete records at runtime:

1. Locate the record in the dataset.
2. Call the Delete method to remove the selected record.

procedure TForm1.DeleteRecord;
begin
  UniQuery1.Delete; // Delete the current record
end;

UniDAC (Universal Data Access Components) is a powerful toolkit for connecting to various databases, including Microsoft Access, from a Delphi application. This solution enables developers to work with Access databases directly through the familiar Delphi interface. UniDAC significantly simplifies data management tasks, which makes it an essential tool for developers and database administrators who aim to streamline and optimize their interactions with Microsoft Access databases.