Computing: Free Pascal Programming

Using Microsoft Access databases with Lazarus/Free Pascal.

Microsoft Access is a commercial database development environment, that may be purchased as such, or as part of Microsoft Office Professional. It is not a database engine like, for example, MySQL, but, like dBASE and Visual FoxPro, it allows to build complete desktop applications with menus, forms for data input, and reports for data output. Microsoft Access is not a client-server application. Instead connecting to a database server (as we do with MySQL), we connect to a database file. This connection is easy to set up using ODBC. And the ODBC driver for Microsoft Access is installed by default when installing Access or Office.

MS Access is commercial, and I don't have an actual version of the software. This tutorial uses Microsoft Access XP, that I installed as a component of Microsoft Office XP Professional, that I found at www.mysoftwarefree.com, and that I installed on a Windows XP Professional (32-bit) VMware Workstation 16 virtual machine. The version of Lazarus, that I used is Lazarus 3.2 (with FPC 3.2.2, 32-bit). As the connection to MS Access databases is standard ODBC, and because the SQL to manipulate data is standard SQL, I could imagine that the tutorial also applies to the recent version of MS Access, running on Windows 10 or 11 (?).

It is supposed that you have MS Access and Lazarus installed. The program samples use the "Northwind" database, that is shipped with Access. An intermediate knowledge of Free Pascal (and the knowledge of SQL) is required; the explanations given here are limited to database related topics.

As said above, we will connect to the database using the Microsoft Access ODBC driver. I will not describe here what ODBC is, and how the database connection string is created, when connecting to an ODBC data source. You can find details about these topics in the second part of my tutorial Connecting to a MySQL database using Lazarus/Free Pascal.

MS Windows includes the ODBC Data Source Administrator, a built in ODBC Driver Manager, which allows to set up a DSN (Data Source Name), that points to the driver to be used, and may also include various configuration settings concerning the connection to this database distribution, or a specific database of this database distribution. You can find the manager application in Control Panel > Administrative Tools. The screenshot on the left shows the items of the Drivers tab of ODBC Data Source Administrator; the Driver for Microsoft Access (*.mdb) is automatically installed when installing Access. The first step would normally be to create a User DSN, using this driver. I don't know if this is always the case, but on my system, a DSN had also automatically been created, as we can see on the User DSN tab (screenshot on the right).

The most important setting here is the name given to the User DNS. On the screenshot, we can see that it is called MS Access Database. It's this name that we will use when setting up the connection in our Lazarus application. There are several other configuration settings that can be made here. We'll let all defaults, setting our specific parameters, in particular the reference to the database, that we want to connect to, in the sample applications source code.

Connecting to an Access database.

To connect to a database from Lazarus/Free Pascal, we need 3 components, that we find in the SQLdb group of the Lazarus component menu: TSQLTransaction, TSQLQuery, and a connection component, depending on how we want to connect to what database distribution, in our case a TODBCConnection component. What these components are for is briefly described in my MySQL tutorial, mentioned further up in the text.

The first program sample is a form with 3 buttons, the first one to connect to the "Northwind" database, the second one to read the number of customers (number of records in the "Customers" table), and the last one to quit the application. The screenshot shows the form in the Lazarus IDE.

On the screenshot, you can see the 3 components mentioned above (I called them "dbTransaction", "dbQuery", and "dbConnection" respectively). We can set some of their properties in their Property sheet:

1. Set the Database property of the TSQLTransaction object to the name of the TODBCConnection object ("dbConnection").
2. Set the Transaction property of the TSQLQuery object to the name of the TSQLTransaction object ("dbTransaction"). The Database property of the TSQLQuery object should be automatically set to the name of the TODBCConnection object ("dbConnection").
3. Be sure that the Transaction property of the TODBCConnection object is set to the name of the TSQLTransaction object ("dbTransaction"). And the important step: Set the DatabaseName property of the TODBCConnection object to the name of the DSN ("MS Access Database").

We'll set the other properties in the program source code...

The screenshot shows the "DatabaseName" property of the TODBCConnection object set to the name of the DSN (as defined in ODBC Data Source Administrator).

Here is the code to connect to the database:

    { Button "Connect" pushed: Connect to "Northwind" database }
    procedure TfAccess.btConnectClick(Sender: TObject);
    begin
        if dbConnection.Connected then
            dbConnection.Close();
        dbConnection.Params.Add('DBQ=C:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb');
        try
            dbConnection.Open;
            edConnect.Text := 'Successfully connected to "Northwind" database';
        except
            on E: ESQLDatabaseError do
                edConnect.Text := E.Message;
        end;
    end;

The connection to the database is initiated using the TODBCConnection.Open method. An ESQLDatabaseError is thrown if the connection fails.

Before connecting, we have, of course, to specify the database that we want to connect to. With MySQL, we do that by setting the value of the Database property of the TODBCConnection object. With MS Access, the connection string keyword used is DBQ. This keyword is not available as such, thus we'll have to add it using the TODBCConnection.Params.Add method. The database file has to be specified as a full path, the file extension (.mdb) has also to be specified. Note that path names including spaces are fully supported; do not enclose the path with quotes in this case (as we have to do in Command Prompt, for example).

To close the connection (when quitting the application) use the TODBCConnection.Close method. Here is the code, executed when the "Exit" button is pushed:

    { Button "Exit" pushed: Exit application }
    procedure TfAccess.btExitClick(Sender: TObject);
    begin
        if dbConnection.Connected then
            dbConnection.Close();
        Close;
    end;

The screenshot on the left shows a successful connection. The screenshot on the right shows a "could not find file" error message, when I ran the application with a misspelled database name.

Reading the number of customers is neither specific to MS Access, nor to the ODBC driver; the same code may be used for any SQL database, independently of the connection used. Just assign your SQL SELECT statement to the SQL.Text property of the TSQLQuery object, and then execute the query by calling the TSQLQuery.Open method. In the case, where there is one single row of records returned, you can retrieve the data returned by reading the Fields property of the TSQLQuery object. In our case this array has one single element, and the record count will be available as dbQuery.Fields[0] ("dbQuery" being the name of my TSQLQuery object). Here is the code to read the record count of the "Customers" table.

    { Button "Query" pushed: Read number of customers }
    procedure TfAccess.btQueryClick(Sender: TObject);
    var
        Count: Integer;
    begin
        if dbConnection.Connected then begin
            dbQuery.SQL.Text := 'SELECT COUNT(*) FROM Customers';
            try
                dbQuery.Open;
                if dbQuery.EOF then
                    Count := 0
                else
                    Count := dbQuery.Fields[0].AsInteger;
                edCustomers.Text := IntToStr(Count);
            except
                on E: ESQLDatabaseError do
                    edCustomers.Text := E.Message;
            end;
        end;
    end;

However, when running the application, and pushing the "Query" button, the program will abort with the error message Driver does not support this function. The problem is due to the fact that Access is not a real RDBMS, and keys are handled differently as with MySQL or similar database systems. To use a TSQLQuery object with Microsoft Access, the TSQLQuery object property UsePrimaryKeyAsKey must be set to False.

After having changed this setting, the program executes successfully. The screenshot shows its output.

Reading data from an Access database.

We already did that in the first program sample, but that was a special case: there was one single row of data returned. How do we proceed if there are several rows of data, as it is the case, for example, when we want to display all products belonging to a given category? That's what will be explained in the following paragraphs.

The screenshot below shows the form of our data reading related application in the Lazarus IDE.

The way to proceed to manage several rows of data is the following:

1. Assign the SQL to the SQL.Text property of the TSQLQuery object, and start the query using the TSQLQuery.Open method.
2. Read the first record using the TSQLQuery.First method.
3. As long as there are records available (while loop testing TSQLQuery.EOF), do with the current record, what you have to do, then read the next record using the TSQLQuery.Next method.

Here is the code to read several rows of data from an Access database. Note, that the program first checks if the user entered a category ID, then reads the number of products for this category (and adapts the number of rows of the string grid if necessary), and finally, if there are any products, runs the SELECT query, retrieves the records' elements and displays them in the string grid.

    { Button "Query" pushed: Read products for category ID entered by user }
    procedure TfAccess2.btQueryClick(Sender: TObject);
    var
        Category, Count, I, J: Integer;
        Query: string;
    begin
        for I := 1 to sgProducts.RowCount - 1 do begin
            for J := 0 to 2 do
                sgProducts.Cells[J, I] := '';
        end;
        if edCategory.Text = '' then
            Category := -1
        else
            Category := StrToInt(edCategory.Text);
        if Category < 1 then begin
            // Invalid user input
            edQuery.Text := 'Missing or invalid category!';
            edCategory.SetFocus;
        end
        else if dbConnection.Connected then begin
            // User input and connection ok: Execute the query
            try
                // Read number of query records and adapt number of stringgrid rows accordingly
                Query := 'SELECT COUNT(ProductName) FROM Products WHERE CategoryID = ' + IntToStr(Category);
                dbQuery.SQL.Text := Query;
                dbQuery.Open;
                if dbQuery.EOF then
                    Count := 0
                else
                    Count := dbQuery.Fields[0].AsInteger;
                dbQuery.Close;
                if Count = 0 then
                    edQuery.Text := 'Unknown category, or no products for this category!'
                else begin
                    // Add rows to stringgrid if required
                    if Count > 10 then
                        sgProducts.RowCount := Count + 1
                    else
                        sgProducts.RowCount := 11;
                    edQuery.Text := 'Number of procults found: ' + IntToStr(Count);
                    // Now run the products by category query
                    Query := 'SELECT ProductName, UnitsInStock, UnitsOnOrder FROM Products';
                    Query += ' WHERE CategoryID = ' + IntToStr(Category);
                    Query += ' ORDER by ProductName';
                    dbQuery.SQL.Text := Query;
                    dbQuery.Open;
                    dbQuery.First;
                    I := 0;
                    while not dbQuery.EOF do begin
                        Inc(I);
                        sgProducts.Cells[0, I] := dbQuery.Fields[0].AsString;
                        sgProducts.Cells[1, I] := IntToStr(dbQuery.Fields[1].AsInteger);
                        sgProducts.Cells[2, I] := IntToStr(dbQuery.Fields[2].AsInteger);
                        dbQuery.Next;
                    end;
                    dbQuery.Close;
                end;
            except
                // Error during SELECT
                on E: ESQLDatabaseError do
                    edQuery.Text := E.Message;
            end;
        end;
    end;

The screenshots show the output of the application: On the left a successful execution with 12 products found for category ID "2"; on the right the case where I entered a category ID that does not exist in the "Northwind" database.

Writing data to an Access database.

You can find sample code to insert, update, or delete a record into/from a Microsoft Access database in the article MS Access in the Free Pascal wiki. I personally find the way, how they proceed, complicated, and prefer to proceed in a similar way as for selecting data, i.e. to use a TSQLQuery object. This is very easy to implement; the only thing that you must think of is to manually check if the record that you want to insert, update, or delete actually exists in the database.

General way to proceed to write data to an MS Access (or other) database:

1. Check if the record already exists. If you are inserting a new record, only proceed if it doesn't. If you are updating or deleting a record, display an error message in this case.
2. Assign your SQL statement to the SQL.Text property of the TSQLQuery object.
3. Execute the query using the TSQLQuery.ExecSQL method (use this method instead of TSQLQuery.Open, that we use when reading data).
4. To be sure that the physical record is actually inserted, updated, resp. deleted, commit the transaction, using the TSQLTransaction.Commit method.

Note: Testing if a record already exits requires to consider some filed (or combination of fields) as having a unique value. If there is a real key, this is no problem. If not (the key being for example an auto-increment), you must decide which field(s) should be unique. In the case of the "Employees" table (used in our program sample), for example, you can say that there aren't two employees who have the same full name (last-name + first-name).

The following program sample is a dummy application that allows to insert, update and delete an employee in the "Employees" table of the "Northwind" database, all information concerning the employee being hard-coded in the source (the only sense of the application is to show how to proceed to insert, update and delete a record).

Here are the hard-coded values used in the program sample.

    const
        EmployeeName = 'Baba';
        EmployeeFirstname = 'Ali';
        EmployeeCity = 'Luxembourg';
        EmployeeCountry = 'Luxembourg';

The screenshot below shows the form of this new application in the Lazarus IDE.

Concerning record insertion, our sample program adds a new employee, called Ali Baba, to the "Employees table". The program first checks if an employee with this last-name and first-name already exists. If not, the record (with last-name, first-name, city, and country information) is added to the table. The program then reads the employee ID (defined as an auto-increment in the "Employees" table), that MS Access has used for the new employee. Here is the code to insert a new record in the "Employees" table.

    { Button "Add" pushed: Add record to "Employees" table }
    procedure TfAccess3.btAddClick(Sender: TObject);
    var
        Query: string;
    begin
        if dbConnection.Connected then begin
            edAdd.Text := ''; edUpdate.Text := ''; edDelete.Text := '';
            try
                // Check if employee doesn't yet exist
                Query := 'SELECT COUNT(EmployeeID) FROM Employees WHERE LastName = ''' + EmployeeName + '''';
                Query += ' AND FirstName = ''' + EmployeeFirstName + '''';
                dbQuery.SQL.Text := Query;
                dbQuery.Open;
                if dbQuery.Fields[0].AsInteger = 0 then begin
                    // Employe does not yet exist: Add the record
                    dbQuery.Close;
                    Query := 'INSERT INTO Employees(FirstName, LastName, City, Country)';
                    Query += ' Values(''' + EmployeeFirstName + ''', ''' + EmployeeName + ''', ''';
                    Query += EmployeeCity + ''', ''' + EmployeeCountry + ''')';
                    dbQuery.SQL.Text := Query;
                    dbQuery.ExecSQL;
                    dbTransaction.Commit;
                    // Get new employee's ID
                    Query := 'SELECT EmployeeID FROM Employees WHERE LastName = ''' + EmployeeName + '''';
                    Query += ' AND FirstName = ''' + EmployeeFirstName + '''';
                    dbQuery.SQL.Text := Query;
                    dbQuery.Open;
                    if dbQuery.EOF then
                        edAdd.Text := 'Unknown error: Employee has not been added!'
                    else
                        edAdd.Text := 'Employee has been added with ID = ' + IntToStr(dbQuery.Fields[0].AsInteger);
                    dbQuery.Close;
                end
                else begin
                    // Employee already exists
                    edAdd.Text := 'Employee already exists!';
                    dbQuery.Close;
                end;
            except
                on E: ESQLDatabaseError do
                    edAdd.Text := E.Message;
            end;
        end;
    end;

Note: As a difference with MySQL, in Microsoft Access, string literals have to be enclosed with single quotes. Using double quotes results in the error message To few parameters. This rather weird error message is rather common with the MS Access ODBC driver. As far as I see, it indicates that there is something wrong with the SQL syntax (?).

The screenshots show the output of the application: On the left a successful execution with insertion of the new record (using Employee ID 16); on the right, the case, where I pushed the "Add" button a second time (error message, because an employee with this last-name and first-name now exists).

Concerning the update of a record, the sample application changes the city of employee Ali Baba from "Luxembourg" to "Echternach". The program first reads the ID of the employee; if it isn't found, the employee is not in the table, and the update cannot be done. Otherwise, the record is updated (using Employee ID in the WHERE part of the UPDATE statement). Finally, the program reads the employee's city, that now should be changed to the new value. Here is the code to update a record in the "Employees" table.

    { Button "Update" pushed: Change city for new employee }
    procedure TfAccess3.btUpdateClick(Sender: TObject);
    var
        EmployeeID: Integer;
        Query: string;
    begin
        if dbConnection.Connected then begin
            edAdd.Text := ''; edUpdate.Text := ''; edDelete.Text := '';
            try
                // Check if employee exists
                Query := 'SELECT EmployeeID FROM Employees WHERE LastName = ''' + EmployeeName + '''';
                Query += ' AND FirstName = ''' + EmployeeFirstName + '''';
                dbQuery.SQL.Text := Query;
                dbQuery.Open;
                if dbQuery.EOF then begin
                    // Employee does not exist
                    edUpdate.Text := 'Employee does not exist!';
                    dbQuery.Close;
                end
                else begin
                    // Employee exists: Change the city
                    EmployeeID := dbQuery.Fields[0].AsInteger;
                    dbQuery.Close;
                    Query := 'UPDATE Employees';
                    Query += ' SET City = ''Echternach'' WHERE EmployeeID = ' + IntToStr(EmployeeID);
                    dbQuery.SQL.Text := Query;
                    dbQuery.ExecSQL;
                    dbTransaction.Commit;
                    // Get new city
                    Query := 'SELECT City FROM Employees WHERE LastName = ''' + EmployeeName + '''';
                    Query += ' AND FirstName = ''' + EmployeeFirstName + '''';
                    dbQuery.SQL.Text := Query;
                    dbQuery.Open;
                    if dbQuery.EOF then
                        edUpdate.Text := 'Unknown error: Employee not found!'
                    else
                        edUpdate.Text := 'City has been changed to ' + dbQuery.Fields[0].AsString;
                    dbQuery.Close;
                end;
            except
                on E: ESQLDatabaseError do
                    edUpdate.Text := E.Message;
            end;
        end;
    end;

The screenshot shows the successful update of the employee's city.

And this screenshot shows the "Employees" table, opened in MS Access. Maybe that you wonder, why the new employee's ID is 16 and not 10? The reason is because the auto-increment value is incremented with each new record insertion, without being changed when a record is deleted. Thus, the new auto-increment value is always the highest value ever used plus 1. That means, for example, if we delete the employee, and insert it again, it's ID will be 17...

Concerning the deletion of a record, the sample application deletes employee Ali Baba, added before. The program first reads the ID of the employee; if it isn't found, the employee is not in the table, and the deletion cannot be done. Otherwise, the record is deleted (using Employee ID in the WHERE part of the DELETE statement). Finally, the program checks if the deletion was successful by trying to read the record of employee Ali Baba (that must no longer exist). Here is the code to delete a record in the "Employees" table.

    { Button "Delete" pushed: Delete the new employee }
    procedure TfAccess3.btDeleteClick(Sender: TObject);
    var
        EmployeeID: Integer;
        Query: string;
    begin
        if dbConnection.Connected then begin
            edAdd.Text := ''; edUpdate.Text := ''; edDelete.Text := '';
            try
                // Check if employee exists
                Query := 'SELECT EmployeeID FROM Employees WHERE LastName = ''' + EmployeeName + '''';
                Query += ' AND FirstName = ''' + EmployeeFirstName + '''';
                dbQuery.SQL.Text := Query;
                dbQuery.Open;
                if dbQuery.EOF then begin
                    // Employee does not exist
                    edDelete.Text := 'Employee does not exist!';
                    dbQuery.Close;
                end
                else begin
                    // Employee exists: Delete the record
                    EmployeeID := dbQuery.Fields[0].AsInteger;
                    dbQuery.Close;
                    Query := 'DELETE FROM Employees';
                    Query += ' WHERE EmployeeID = ' + IntToStr(EmployeeID);
                    dbQuery.SQL.Text := Query;
                    dbQuery.ExecSQL;
                    dbTransaction.Commit;
                    // Check if the deletion has been done
                    Query := 'SELECT EmployeeID FROM Employees WHERE LastName = ''' + EmployeeName + '''';
                    Query += ' AND FirstName = ''' + EmployeeFirstName + '''';
                    dbQuery.SQL.Text := Query;
                    dbQuery.Open;
                    if dbQuery.EOF then
                        edDelete.Text := 'Employee has been deleted'
                    else
                        edDelete.Text := 'Unknown error: Employee still exits!';
                    dbQuery.Close;
                end;
            except
                on E: ESQLDatabaseError do
                    edUpdate.Text := E.Message;
            end;
        end;
    end;

The screenshots show the output of the application: On the left a successful deletion; on the right, the case, where I pushed the "Update" button after I had deleted the employee (error message, because this employee doesn't exist anymore).

---

If you find this text helpful, please, support me and this website by signing my guestbook.