008 – An example of connecting database Microsoft Access to application

An example of connecting database Microsoft Access to application

The task

Database, which were created by Microsoft Access, is given. Database is placed at disk by following path:

C:\Programs\C++ Builder\Program_01_02_00_008\01_02_00_008_database.mdb

The structure of database and connections between tables are shown in Figure 1.

01_02_00_008_01

Fig. 1. The scheme of relations between database tables

  Need to connect database to Windows application.

 

Progress

 

1. Run Delphi.

Create form and save the project.

 

2. Components for working with database

From “dbGo” tab of tool palette select components TADOConnection and TADOTable.

From the “Data Access” tab we select component TDataSource (fig. 2).

We place these components on the application form.

01_02_00_008_02_

Fig. 2. Components for connecting to database

The components from the palette “dbGo” are designed to operate with databases on ADO technology, which is well suited for MS Access database.

Instead component ADOQuery you can use component ADOTable. It is also possible to combine these components.

With the purpose of visualization data from tables is used TDBGrid component from “Data Controls” palette. It displays data in a database table. After placing this component on the form, view of project window will be the following (fig. 3).

01_02_00_008_03_

Рис. 3. Component DBGrid1 for data displaying from database

 

3. Setting up the components

There are two methods to connect to database using the abovementioned components (fig. 4).

01_02_00_008_04e

Fig. 4. Methods to connect to the database

In the second case the connection is made via an optional component ADOConnection1.

This method has more general case and is best suited for cases where several components such as TADOTable or TADOQuery, used to access to one connection ADOConnection1.

We choose the second method of settings.

 

4. String “ConnectionString“.

To access the database file we need to select path to the file and set others options.

We carry out the setting of ConnectionString property of component ADOConnection1.

Likewise is configured the ConnectionString property of ADOTable1 component.

First select a component ADOConnection1. In the Object Inspector is chosen property ConnectionString (Fig. 5).

01_02_00_008_05_

Fig. 5. Property ConnectionString of ADOConnection1 component

This will open the “Form1.ADOConnection1 ConnectionString” window (Fig. 6).

Two variants of connecting is proposed:

  • Use Data Link File;
  • Use Connection String.

In the first case it is possible to connect to the database by selecting a file type Microsoft Data Link with the extension “* .udl“. These files are needed for connecting with data by ADO technology.

For familiarization we use the second method “Use Connection String“. Select the “Build…” button.

01_02_00_008_06_

Fig. 6. Methods of connection source selection.

As a result of previous choice the window “Свойства связи с данными ” will be opened (Fig. 7).

01_02_00_008_07_

Fig. 7. Window “Свойства связи с данными”

In this window we select provider OLE DB. To work with Microsoft Access databases is the good provider “Microsoft Jet 4.0 OLE DB Provider“.

Click on the “Далее >>” button.

The next step is to select the database file (Fig. 8). In accordance with problem of task, database file is placed to the following path:

C:\Programs\C++ Builder\Program_01_02_00_008\01_02_00_008_database.mdb

Click at the button “” and select the database file.

01_02_00_008_08_

Fig. 8. Tab “Подключение” with the selected database file

We can test the connection with the help of “Проверить подключение” button.

If everything is OK, the following message will be showed (Fig. 9).

01_02_00_008_09_

Fig. 9. Connection testing with database file

After that we select “OK“. Window “Form1.ADOConnection1 ConnectionString” has the following view (Fig. 10).

01_02_00_008_10_

Fig. 10. Window “Form1.ADOConnection1 ConnectionString” with the selected source connection

In window at figure 10 select “OK“. After this the ConnectionString property of ADOConnection1 component will be formed.

 

5. LoginPrompt property of ADOConnection1 component

By default, the database is set up so that when accessing data from the user will be required to enter a user name and password. To avoid inconvenience, it is necessary in the property LoginPrompt component ADOConnection1 set to “false” (Fig. 11).

01_02_00_008_11_

Fig. 11. LoginPrompt property of ADOConnection1 component

 

6. Binding of components together

Now you can bind the components together according to the scheme shown in Fig. 4 a).

To do this, using the Object Inspector, you must do the following:

  • in ADOTable1 component we need set the property “Connection” to “ADOConnection1” value;
  • in DataSource1 component we need set the property “DataSet” to “ADOTable1” value;
  • in DBGrid1 component we need set the property “DataSource” to “DataSource1” value.

 

7. Selecting and displaying table “Students

Now we have application that connected with database.

The next step you need to set the table or query for output DBGrid1.

If we use TADOQuery component, then to output will be used SQL-language string. In this case data to DBGrid1 can be displayed in several tables.

If we use TADOTable component (our case), then is displayed only one table from database. For example, select the “Students” table.

For that with the help of Object Inspector in component ADOTable1 we need:

  • in “TableName” property select the table “Students“;
  • Active” property set to “true” value.

As a result, data will be displayed in DBGrid1 component (Fig. 12).

01_02_00_008_12_

Fig. 12. Setting ADOTable1 and output of table “Students“.

The task is solved!