- database solutions and downloads for microsoft access

Microsoft Access & Borland Delphi

Using Microsoft Access with Borland Delphi

Applications used:

  • Borland Delphi 7
  • Microsoft Access 2000

The article assumes that the reader knows how to create a basic Microsoft Access database and has some knowledge of programming in Delphi.

Microsoft Access is primarily used for developing stand alone applications. It is very fast, reliable and is very dependable when it comes to rapid application development. One of the benefits of Access from a developer's perspective is its relative compatibility with the structured query language (SQL). SQL is of course used to manipulate data within databases. So to develop efficient and dependable database applications in a rapid manner, we are going to need the advantages that MS Access offers.

So where does Borland Delphi fit in this scenario? Like Access, Delphi is also one of the frontrunners in its field. It is also a rapid application development language that offers easy application development. One of the reasons why I choose Delphi for this article is because it has a set of database components that integrates applications with MS Access’ Jet Engine. And since we intend to build a stand alone application, Access and Delphi are perfectly place to handle this task. The aim of the application is simply to show how well MS Access and Delphi work together, among other things I will demonstrate how to use SQL to manipulate the data in the database. So let’s start with building an Access database. For the sake of brevity, we are going to use Microsoft Access to create the database, but it is also possible to programmatically create an Access database with Delphi. Create a database called addressbook.mdb, and then create a table called contacts with the following columns:

The Contacts table design in the Addressbook database
Column Description Type
cid Contact ID PrimaryKey, Autonumber
name Name of Contact Text
surname Surname of Contact Text
gender Gender of the Contact Text
age Age of Contact Number
country Country of residence Text

Add the following data in the table:

The Contacts table in the database

That’s all there is for the Access side of things. Next, we create the Delphi side of things:

Start a new application in Delphi and add the following components to the form:

  • 1 Tmemo renamed qmemo
  • 1 Dbgrid
  • 1 buttons
  • 1 Tedit renamed edparam

Then add the following components from the ADO tab:

  • 1 ADOQuery rename to q1
  • 1 ADOTable rename to ado1
  • 1 AdoConnection
  • 1 Datasource from the data access tab.

Setting the component connections

Now select the adoconnection component and go to the object inspectors’ connection string property. Click on the ellipses button. You should now see a window that looks like this:

The ADO Connection component

Click on "build…" a window called "Data Link Properties" should come up. Click on the ellipses button and point to where the addressbook.mdb database is located. Then click OK twice and your connection should now be set.

Select the datasource component and go to its dataset property and add "q1" from the dropdown list.

Select the dbgrid component, go to its datasource property and add "datasource1" as its datasource.

That links up all the components, now all you have to do is to run the queries and all the data will be displayed in the dbgrid.

Your main form should look something like this at this point:

The main form design

Executing SQL Queries

The GUI of the application gives us enough flexibility to run as many queries as we like. The above picture shows a sample query statement that is going to retrieve data that is based on a country criterion:

select * from contacts WHERE country=:c

if the country specified in “=:c” is England then the above query would produce the result:

The “=:c” is what is called a parameter. It will contain the value that is to be included in the edparam text field (in the above the value is England). You can also just run a straight query that simply retrieves all of the records from the database:

select * from contacts

This produces:

Or one that retrieves all the records where the ages of the contacts are 20:

select * from contacts WHERE age=:c 

Query number one and three are what are called parameterized or dynamic queries. A parameterized query is one that provides flexible row/column selection using a parameter in the WHERE clause of a SQL statement. The ADOQuery components’ param property allows replaceable values to be stored for the query, as demonstrated in the queries above. To specify a parameter in a query, use a colon (:) preceding a parameter name, as in:

select * from contacts WHERE age=:c

All of the above queries are executed in the following procedure:

procedure TForm1.qbtnClick(Sender: TObject);
//close query component
if q1.Active then begin
with q1 do
if edparam.text <> '' then begin

The procedure itself is very abstract in the sense that it does not allow you to write the queries. In other words, the queries are not hard coded. You can use any name of a contact or country and it will just work.

To make any changes to a contact’s details is even easier. You simply call up the details using a query and then select the name of the contact and then make the changes that want. These changes will then be posted to the Access database.

So as you can see, the marriage of Access databases and Delphi’s ADO components makes it a snap to write database applications with ease. Thanks to the on going improvements of MS Access, you can now also create a database application that is able to handle multiple user access at the same time.

The Author

Leidago !Noabeb is a computer programmer based in Namibia. He has worked with both opensource and Microsoft technologies for over seven years and specializes in writing communications software. He has made many contributions to various online websites dedicated to web development. He can be reached at: leidago [at]