Databases include entities without which it cannot exist. These entities are called database objects and determine the actions that are performed in the DBMS. Access database objects are elements that store information and are used to automate actions with it.
Key Access Database Objects
We list the main objects.
- Tables are the main elements of the database, without which it cannot exist. They contain data.
- Forms are an interface display of tables; these are windows in which database users work.
- Queries are used to retrieve data, record and modify information, and delete existing table elements.
- Reports provide data in the form and structure necessary for users with summing up and collecting statistics.
- Macros are database program units. Their task is to carry out the necessary actions when certain events occur.
- Modules - Access database objects. They respond to changes in the database, or perform independent tasks.
Tables
Database tables are the basic elements in which information is stored. In relational databases, tables are called relationships. They consist of rows and columns. Rows are called records; each of them contains information regarding one element. The columns contain the table parameters. Columns are called database fields.
Database development begins with creating tables. Attributes are indicated for it — properties that determine behavior. For the basic object in Access - “Tables” - in the design mode on the right side of the screen, the table properties are defined. Here, the main and subordinate fields are set, the orientation and default mode, the description is determined, etc.
At the bottom of the screen, properties are set for each of the fields. The set of parameters varies depending on the type of field. Here the general properties of the column are set and the substitution for the fields of numeric or text types is specified: a list or a combo box. Parameters for substitution are also indicated here: data source, title, format, availability of changes, etc.
Between tables there are relationships defined by keys - fields of one relationship, which are referenced by the values of another. Keys are columns with unique values in them to uniquely identify a record.
Forms
Forms are objects in Access that are used for the convenience of users. Tabular display of information is not always convenient and visual, therefore they create forms that allow you to enter and change data.
The form contains the controls: buttons, drop-down lists, text fields, flags, attachments, pictures, and others. Tools on the control panel allow you to enter the company logo, date and time, title. You can develop a form in the "Design" mode, the "Layout" mode and, in fact, in the "Forms" mode.
In the structure of the customer table in the figure above we see 7 fields: “Code”, “First name”, “Middle name”, “Last name”, “Date of birth”, “Gender” and “Age”. But the “Code” field is technical, cannot be changed and is not needed to demonstrate the database to the user. Therefore, it is removed from the form, leaving only those fields in which values need to be added or changed.
The properties of the form and each of the displayed fields - objects of the Access form - are placed on the right. Here they set what the data editing window looks like (Layout tab), where to get the data to display (Data tab), what actions to perform when individual events occur (Events tab) and other properties.
Inquiries
Queries are used for various data manipulations in Access. The most popular data retrieval requests. They make it possible to obtain the values of fields from tables that meet the given conditions, and display them in the required form with the necessary sorting and grouping of rows.
These objects in Access are created using the Constructor. The developer selects the fields necessary for viewing, sorting, and indicates the conditions for selecting records. Request properties are displayed in the window on the right. Here, the appearance of the sampling results and such parameters as blocking records when executing the query, filter at boot, data source, response timeout, and so on are determined.
Requests are also used to add and modify existing records, as well as delete lines that match the specified conditions.
Reports
Reports are objects in Access that are required to display information from tables in printable forms. Reporting forms are created by the wizard or in the "Design" mode. To do this, select the data source for the formation of the printed form. Fields in the report can be grouped and sorted, output totals.
It is also possible to create forms of contracts and agreements using a pre-created document template. The table fields are inserted into the finished form, the necessary page elements are added: number, footer, header, logo and more.
Report properties are indicated in the window on the right. The properties of individual objects of the printed form are also set there: headers, controls, data area, etc.
This is how a ready-made report on a template looks like:
Macros
One of the main objects of Access is macros. They represent a sequence of actions that must be performed when a specific event occurs. Macros are created using the "Designer" and provided by the macro system for various purposes.
The macro commands are intended for importing and exporting data, working with other database objects, setting filters and processing table records, etc. As an example, below is the addition of a simple macro that starts when you click on the "Cancel" button of the "Clients" form. Its task is simply to close the form without saving the information entered into it.
Modules
Modules - objects in Access, which are procedures and functions used to respond to events in the database or for calculations. They are developed using VBA code - Visual Basic for Application. This is a special language for writing code in Microsoft Office programs - Access, Word and Excel.
For the customer form, we’ll make the Age field computed. To do this, in the "Design" mode of the form, select the required field and select the procedure for processing the "Get Focus" event in the properties window on the "Events" tab.
Let's write the processing module:
Private Sub _GotFocus()
.Value = DateDiff("yyyy", [ ], Date)
End Sub
Now, when switching between records and focus on the "Age" field, the value of this field is calculated automatically.
It should be understood that it is not an Access object, for example, a table key, despite the fact that it is used to store information. The key is part of the table, not a standalone database item.
The information provided in the article will help to understand what database objects are, what properties they have and what functions they perform.