databasedev.co.uk - database solutions and downloads for microsoft access

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:

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:

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:

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);
begin
//close query component
if q1.Active then begin
q1.Close;
q1.Parameters.ParamByName('c').Value:='';
edparam.Clear;
end;
with q1 do
begin
SQL.Add(qmem.lines.text);
if edparam.text <> '' then begin
Parameters.ParamByName('c').Value:=edparam.text;
end;
Open;
end;  

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.