An example of connecting database Microsoft Access to application
Database, which were created by Microsoft Access, is given. Database is placed at disk by following path:
The structure of database and connections between tables are shown in Figure 1.
Fig. 1. The scheme of relations between database tables
Need to connect database to Windows application.
1. Run Delphi. Create the project
Detailed example of creating the project is described here. 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.
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).
Рис. 3. Component DBGrid1 for data displaying from database
3. Setting up the components
There are two methods to connect to database using the above mentioned components (fig. 4).
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).
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.
Fig. 6. Methods of connection source selection.
As a result of previous choice the window “Свойства связи с данными ” will be opened (Fig. 7).
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:
Click at the button “…” and select the database file.
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).
Fig. 9. Connection testing with database file
After that we select “OK“. Window “Form1.ADOConnection1 ConnectionString” has the following view (Fig. 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).
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).
Fig. 12. Setting ADOTable1 and output of table “Students“.
The task is solved.