From this article, you will learn the difference between working with databases via the ADO technology and the FireDAC library. Using the right database technology is often a critical part of designing your apps, particularly for Windows application development where there is a very rich array of database choices, not all of which may be hosted on a Windows server.
- ADO (ActiveX Data Objects) is an app programming interface developed by Microsoft and based on the component technology ActiveX. ADO allows providing data from different sources (relational databases, text files, etc.) in an object-oriented format.
- FireDAC is a universal data access library that is intended for developing apps for different devices that should be connected to corporate databases. Thanks to a universal and highly-effective architecture, FireDAC ensures high-speed direct native access from Delphi and C++Builder to InterBase, SQLite, MySQL, SQL Server, Oracle, PostgreSQL, DB2, SQL Anywhere, Advantage DB, Firebird, Access, Informix, etc.
It’s important to understand that FireDAC is a library. At the same time, ADO is a global technology that ensures access not only to databases but also to texts, documents, tables, and others.
In this article, we will consider the connection to MS Access and SQLite databases using both technologies, conduct data sampling and display them on the grid.
Table of Contents
How to set up a connection to MS Access databases using ADO?
To get connected to the MS Access database we need to add a TADOConnection component to the form and tune it.
For setting the connection we can move to the ConnectionString property in the object inspector and press the button with three dots “…” or make a double-click on the component.
We will see a form where it will be required to choose an option Use Connection String and press a Build button.
In the next form, it is necessary to choose Microsoft Jet 4.0 OLE DB Provider and press Next >>
We will get to the next tab “Connection”. Here we need to indicate the path to the database file and click OK.
Then we need to click OK once again in the window where you can see our Connection String
We have only one step left. In the Object Inspector window, it is necessary to switch the LoginPrompt property to False so that after connecting to the database we won’t get a window for inserting login and password.
Setting the ADO connection to active
If everything is set correctly, we are able to add the Connected property to True and our component will connect to the database. But we won’t do that. The best practice is to get connected to a database during the program launch. For doing that, we can make a double-click on the form and open a code editor where we will see a procedure for a form creation event and add there one code line as you can see in the screenshot below.
Then we need to add a TADOQuery component to the form and make all the settings. First of all, we need to set the Connection property. In order to do it, from the dropdown list we need to choose ADOConnection1 which has been set at the previous steps.
Now in the SQL property, we will set a question for data sampling.
After that let’s go back to the code editor and continue writing our code for the form creation procedure.
After setting Connected in True we should check whether everything is okay with our connection and execute our inquiry.
Now it is necessary to insert our data into the form. We will use the TDBGrid component. Let’s place it onto the form and choose the Align property in alClient in order to place it on the entire form.
How to link the database data to visual controls on the screen?
Everything is almost ready for displaying our data. There is only one thing left. To link data in the dataset (ADOQuery1) with the grid we need to add the TDataSource component and indicate ADOQuery1 in its DataSet property and then go to the grid parameters and indicate DataSource1 in the DataSource.
Now we can start our app.
As you can see in the screenshot above, when the program was started it got connected to the database, opened the data set, and using the data source, displayed the content in the grid.
How to connect to a SQLite database via ADO?
The next task that we will fulfill is the connection to a SQLite database using the ADO technology.
We will need the same component set that’s why we offer to continue working with an app, changing just the way of getting connected to a database.
But before we proceed to settings we need to install a provider for SQLite databases into the system. You can download it following the link http://www.ch-werner.de/sqliteodbc/
Now we need to come back to settings. Again let’s open the ConnectionString property of the ADOConnection1 component for editing. Now let’s choose another provider. It will be the Microsoft OLE DB Provider for ODBC Drivers.
Now our window “Connection” for connection property editing looks a little bit different.
Here we need to indicate a data source. In the dropdown list, we can see only SQLite3 Datasource that doesn’t refer to data.
Now it is necessary to add our source to the system. For doing that we need to open a system window ODBC Data Sources that is shown below.
Let’s press the Add button, find the necessary SQLite3 ODBC Driver button and press the Finish button.
Now we see a setting window for the chosen driver.
Here it is important to indicate the name of the data source and the path to the database. After pressing OK we will see that our data source is now displayed on our list.
Let’s press OK again.
Now let’s make sure that the source is available to us from Delphi. In order to do it, in the window with connection properties we need to press the Refresh button and check the list.
We see that our SQLite Demo source has appeared there. We need to choose it and confirm our choice by pressing OK. We can see now that the connection row has changed.
How to view SQLite data in a grid?
Now we should start our app again and we will see that the app is working just the same way.
It’s worth mentioning that we have used the databases that were provided by the Embarcadero company together with code samples, that’s why despite the fact that we have got connected to different databases, they contain identical data.
How to connect to an MS Access Database via ADO and FireDAC?
Now we will try to repeat all our actions and to get connected to the same databases but now using FireDAC.
Let’s continue using our app and fulfill a small preparation. We need to delete the ADOConnection1 and ADOQuery1 components from the form as well as the code that we wrote for connecting with databases.
Now we can proceed directly to the connection setting with FireDAC.
Let’s add the TFDConnection component to the form, make a double-click and move to settings. In the form that will be opened let’s choose MSAcc from the Driver ID dropdown list and then indicate the path to the database in the Database parameter (highlighted with yellow in the screenshot)
Let’s save the settings and set the LoginPromt property with the value False as we’ve done in the case of ADO.
The next step will be to add a TFDQuery component to the form. It’s worth mentioning that in this case, the Connection property of the component will be automatically filled in with the value FDConnection1. If it doesn’t happen, it is necessary to do it manually.
Now with the help of a double-click, we need to open a window for editing an inquiry and insert the necessary one. Using the Execute button, we can check the correctness.
In order to fully finish our preparations, it is necessary to indicate our FDQuery1 in the DataSet property of the DataSource1 component that we have in the form.
In order to start our app and to get the data displayed, we need to add a code for creating a form, connecting to a database and opening a dataset.
If we start the program, we will see the same parameters.
How to connect to a SQLite database using FireDAC?
Let’s have a look at the method that allows us to get connected to a SQLite database using the same FireDAC. Let’s continue the app modification. Now we need to open the database connection window once again by making a double-click on a FDConnection1 component and to change the Driver ID to SQLite. The list of parameters now differs. Again, we need to indicate the path to a database in the database parameter.
Now we can start the app again. Everything is working correctly.
If you have fulfilled all the steps that are described in this article and managed to get connected to different databases via ADO and FireDAC, you may have noticed that it was significantly easier to do that with FireDAC. It is enough just to place a component into the form, choose the necessary driver from RAD Studio and indicate the necessary parameters for the connection. Work with data via ADO requires additional settings in the system as well as the support of the technology from the side of the operating system.
In programming, developers may have different tasks that’s why skills of working with different technologies are an important advantage. But which of them to use in this or that case is up to you to decide.
We’d also like to add that as RAD Studio supports the creation of cross-platform apps, the use of such a powerful in-built tool as FireDAC will be rather convenient.
This article was written by Embarcadero Tech Partner Softacom. Softacom specialize in all sorts of software development focused on Delphi. Read more about their services on the Softacom website.