Brief description of the microsoft access database. Main features of ms access database

Microsoft Access is a functionally complete relational DBMS. It provides all the necessary tools for defining, processing, and managing data when working with large volumes of information.

DBMS Microsoft Access provides the opportunity to control the definition of the structure and description of your data, work with them and the organization of collective use of this information. DBMS Microsoft Access also significantly increases the capabilities and facilitates cataloging and maintaining large volumes of information stored in numerous tables. DBMS Microsoft Access includes three main types of functions: definition (setting the structure and description) of data, data processing and data management. MS Access is a set instrumental means for creating and operating information systems.

Using Access, you can perform the following operations.

1. Design basic IS objects - two-dimensional tables with different types of data, including fields of OLE objects. Before filling a table with data, you need to create its layout.

2. Establishment connections between tables, with support for data integrity, cascading field updates and cascading record deletions.

3. Entering, storing, viewing, sorting, modifying and retrieving data from tables using various means of information control, indexing tables and logic algebra (for filtering data).

4. Creation, modification and use derivatives IS objects (forms, queries and reports).

The MS Access processing object is database file, having an arbitrary name and extension .MDB. This file contains the main objects MS Access: tables, forms, queries, reports, macros and modules. Thus, an information system created in Access is a database file.MDB plus the tools of Access itself. The operation of such an IS is the processing of an .MDB document by the Access application.

Information in an Access database is presented in the form of separate tables. In this case, each table column corresponds to a data field, and each row corresponds to a data record.

A data record consists of several fields. The following rule applies: a data record is a group of interrelated fields considered as a single whole. For example, a data record may contain information about a specific customer or product. Each data record in a table contains the same number of fields. Each field contains the same type of information. For example, in each data record of a certain table, the CLIENT NAME field will be for the client's name, i.e. will contain the same type of information.

There are different types of data. The data type for a particular data field is selected depending on what information will be located in this field. The size of the data field is determined depending on the type selected for it. Descriptions of data types and their characteristics are given in Table 2.

Table 2 - Data types

Data type	Application	Size
Text	Text or a combination of text and numbers, such as an address, and numbers that do not require calculations, such as telephone numbers. item numbers or postal code.	Up to 255 characters. Only characters entered in the field are stored; positions not used in the text field are not stored. To control the maximum number of characters entered, you must define the FieldSize property.
MEMO field	Long text or numbers, such as comments or descriptions. MEMO fields cannot be indexed or sorted. For storing formatted text or long documents, instead of the MEMO field. you should create an OLE object field.	Up to 65535 characters.
Number	Numeric data used for mathematical calculations, excluding calculations involving monetary transactions (for which the monetary type is used). The type and size of values that can be contained in a number field. Can be changed in the FieldSize property. For example, in the field. occupying 1 byte on disk, you can enter only integers (without decimal places) from 0 to 255.	1, 2, 4 or 8 bytes. 16 bytes only for replication codes.
Date/Time	Dates and times. Storing date and time values in a Date/Time field ensures correct sorting. Any changes made to date and time formats in the Language and Standards window of the Windows Control Panel will be automatically reflected in the Date/Time fields.	8 bytes.
Currency	Currency values. The currency type is used to prevent rounding during calculations. Assumes up to 15 characters in the integer part of the number and 4 in the fractional part.	8 bytes.
Counter (AutoNumber)	Automatic insertion of sequential (differing by 1) or random numbers when adding a record. To create an incremental counter, leave all property settings at the bottom of the window the same (default), with the FieldSize property set to Long Integer, and the New Values property set to Increment. To create a random number counter, the New Values property must be set to Random.	4 bytes. For replication codes - 16 bytes.
Logical (Yes/No)	Fields. containing only one or two values, such as Yes/No, True/False. On off.	1 bit.
OLE Objects	Objects (for example, Microsoft Word documents, Microsoft Excel spreadsheets, pictures, sounds, and other data) created in other programs that use the OLE protocol. Objects can be linked or embedded in a Microsoft Access table. To display an OLE object in a form or report, you must use the Attached Object Frame control.	Up to 1 gigabyte
Lookup Wizard	A field is created. allows you to select a value from another table or from a list of values using a combo box. When you select this option in the list of data types, a Wizard is loaded to define them.	The size is the same as the size of the key field, which is also the Lookup Wizard, usually 4 bytes.
Hyperlink	The field in which hyperlinks are stored. A hyperlink can be either a UNC type (Universal Naming Convention - a standard file path format with the inclusion of a network server) or a URL (Uniform Resource Locator - the address of an object on the Internet or an internal network with the inclusion of an access protocol type). A hyperlink can consist of four parts: text that appears in a field or control; path to the file (in UNC path format) or page (URL); additional address - location inside a file or page; tooltip - text displayed as a tooltip.	The length of each part of the hyperlink is no more than 2048 characters.
Note: Numeric, currency, and Boolean data types, as well as Date/Time, provide standard display formats. To select formats for each data type, you must define the Format property. For all data except OLE objects, you can also create a custom display format. For more information, see the "Field Format Property" section below.

In addition to tables, Access DBMS works with the following objects:

Forms;

Requests;

Reports;

Macros;

Modules.

The table is base MS Access object. All other objects are derivatives and are created only on the basis of previously prepared tables.

A form is not a standalone Access object. It simply helps you enter, view, and modify information in a table or query. Queries and reports perform independent functions: select, group, present, and print information.

Each MS Access object has Name. The length of the name of any MS Access object (tables, forms, etc.) is no more than 64 arbitrary characters (except for the dot and some service characters). The name may include spaces and Russian letters. Each object is worked in a separate window, and two modes of operation are provided:

1) mode designer, - when a layout, object structure (for example, table structure) is created or changed;

2) operational mode - when information is viewed, changed, and selected in the IS task window.

In addition, the database file includes another document that has its own window: Data schema. In this window you can create, view, change and tear communications between tables. These connections help you control data and create queries and reports.

Form, strictly speaking, it is an optional element of the IS (in principle, you can do without it), but it allows you to simplify the operations of entering and viewing data.

Request is a derived table that includes data from other tables and performs various operations on them. In particular, the request may contain calculated fields, i.e. fields whose values are functions values of other fields (possibly from different tables). In addition, queries allow you to group operations, i.e. operations on a group of records united by some common characteristic (for example, you can sum the quantity for records with the same code). Finally, queries allow you to make selections from tables based on some condition. In such cases, the algebra of logic is used.

Report - this is actually the same request, but formatted so that it can be printed on paper and presented to management (with beautiful headings, subtotals, etc.).

In essence, tables and forms are used to maintain the contents of the database, and queries and reports perform the main function of the information system - retrieving, transforming and presenting information.

Creation derivatives objects - requests, forms and reports - are maintained approximately according to the same scheme. Here are some features:

For derived objects, you must specify the name(s) of the table (query) on the basis of which this object is created;

When using the Wizard, you must check the “Element Panel” checkbox on the toolbar, and a set of icons will appear on the screen (Fig. 14.5), with which you design a form (report, query).

Creating any objects is possible in two ways: using the wizard and in design mode.

MS Access has powerful, convenient and flexible tools visual designing objects with the help of Wizards, and this allows the user, with a minimum of preliminary preparation, to quickly create a full-fledged IS - at the level of tables, forms, query selections and reports.

The masters themselves perform the necessary actions. When you call the Wizard, the corresponding sequence of dialog boxes opens. Having received answers to the questions asked, the Master performs all the necessary actions. Let's say you want to generate a report. The Report Wizard will ask you what the report should look like and what data it should contain. After receiving the answers, the report will be completely ready. Using other Wizards, you can create database tables, queries and forms.

Working with Wizards is simple, reliable and efficient, and allows you to use the full richness of Microsoft Access.

The Builder is an Access tool that makes it easier to complete a specific task. The Expression Builder is extremely useful - it allows you to quickly create a complex expression. Using the Query Builder, you can easily create queries that are used to obtain data samples for a form or report. In addition to the ones listed, Access has other constructors. This is a macro constructor with the help of which various macros are generated, as well as constructors for menus, fields, colors, codes and others.

1 Launch Microsoft Access:

When Access starts, a window appears on the screen (Figure 1.1), in which you can:

Or create a new database, i.e. a file with the extension .MDB;

Or open an existing database, i.e. a file with the .MDB extension.

Creating a database. After selecting the [File-Create...] command, a standard filer appears on the screen (Figure 1.2), in which you need to specify the name and address created database file. For example, for an information system named Telephone Directory.MDB.

After creating the file, Access opens an empty database window, and in this window all operations are carried out - creating and manipulating database objects. This window contains 7 objects (Tables, Queries, Forms, Report, Pages, Macros, Modules) and groups.

Opening a database. After selecting the [File-Open...] command, a standard filer appears on the screen, in which you must specify the name and address existing database file. Then the application window opens database window, providing access to already created database objects and the ability to create new objects.

MS Access - multi-window application, however only one database can be open at any time. It is her window that is main document window in the Access application (Figure 1.3), and closing it means closing the corresponding .MDB file. However, this window generates many subsidiaries windows (table, query, form, etc.). You can save an object (for example, a table layout), the window of which is on the screen, and assign it a name in the same way as you do with files: with the [File-Save] or [File-Save As...] command.

Working with tables

So, in the database window there is a list of existing objects DB - tables, queries, etc. You can work with the window of any object (child window) either in operational mode (for example, enter or view data in a table), or in designer(for example, change the table layout).

Before using any object (for example, filling a table with data), you must create this object (for example, create a table layout).

If the Table tab is selected, the tab window displays a list existing tables of this database.

To open an existing table, you need to highlight her name in this list and click the “Open” button.

Figure 1.3 shows the MS Access application window along with the Intercity database window. There are no objects in this file yet.

To be included in the database new tables, you need to click the “Create” button.

In order to fix layout existing table, select its name in the list and click the “Design” button.

The same operations are performed with all other MS Access objects.

Any object (table, query, form, report) can be created either manually (design mode) or using the Wizard.

When you call the Wizard, the corresponding sequence of dialog boxes opens. Having received answers to the questions asked, the Master performs all the necessary actions. For example, when generating a report, the Report Wizard will ask you what the report should look like and what data it should contain. After receiving the answers, the report will be completely ready. Using other Wizards, you can create database tables, queries and forms.

Create tables and queries manually(in design mode) ;

- create forms and reports using Masters.

Let's consider the sequence of steps when included in the database new tables. First you need to determine the subject area for which the database will be created. As a subject area, we will define long-distance telephone conversations, calculation of the amount of payment for long-distance telephone calls for individual subscribers. First of all, the fields that the table will contain are determined. Subscribers" database "Mezhgorod". The required fields are shown in Table 3, with the “Subscriber Number” field selected as the key one. The key represents a field in the corresponding table. It uniquely characterizes each record contained within the table.

Table 3 – Fields of the “Subscriber” table

An ideal primary key is information such as customer number, account number, etc. Using such a key, it is easy to identify the corresponding record. Fields containing the customer name are not suitable for the primary key. It is always possible for several people to have the same name.

After determining the required fields, you can begin creating the table itself in MS Access.

To do this, you need to open the table designer window (Fig. 2.1) by selecting the Table tab and the line “Creating a table in design mode” or clicking the “Create” button.

In the upper part of the window that opens there is a created or modified layout table, which is simply a list of fields indicating the field name, data type and description. Each table field is one line at the top of this window (Figure 2.1).

Each table field should be assigned a unique name, determine the type of data that will be entered into it, and describe the contents of the field, i.e. enter text comments indicating the purpose of this field. The field name can have up to 64 characters. Description is an optional parameter and is used to further explain the field being used.

In the Field Name column (Figure 2.2) type an arbitrary Name. In the next column you must indicate data type for this field. The data type can be selected from the drop-down list that appears in this column (data types are described in Table 3). Additionally, as soon as the cursor is in the Data Type column, a message appears at the bottom of the window. properties form(characteristics) of this field.

The properties form (lower part of the window) is a list of properties (on the left is the name of the property, on the right is meaning of this property) with a hint window for each property.

The list of properties changes depending on the data type that is currently displayed in the Data Type column. Clicking the mouse on the field values in the properties form, you can change this value (within the limits allowed for this data type). The field properties are listed in Table 4.

Table 4 – Field properties displayed in the table designer properties sheet

Property	Purpose
Field size	Sets the maximum number of characters to enter in this field
New values	Defines how counter values change when new records are added
Field Format	Specifies the format for displaying the values of this field
Number of decimal places	Determines the number of decimal places used when displaying numbers
Input mask	Specifies an input mask to make it easier to enter data into a field
Signature	Defines the text that appears as the field label
Default value	Allows you to specify a value that is automatically entered into the field when a new record is created
Condition on value	Defines the requirements for data entered into the field
Error message	Allows you to specify the text of the message displayed on the screen if the entered data violates the condition defined in the property
Obligatory field	Indicates whether the field requires a value to be entered
Blank lines	Determines whether blank lines are allowed in this field
Indexed field	Defines an index created on a single field
Unicode Compression	Sets compression for fields that use Unicode encoding to store data (MEMO fields, hyperlinks, and text fields)

Most values are accepted by the system by default; some values can be selected from the drop-down list.

For text and number fields you must specify size fields, and for text this is the permissible length of the value (for example, 20 or 40 characters), and for a number - the representation format in the machine (byte, integer - two bytes, long integer, etc.). The maximum length of the text field is 255 characters. If you need to enter text that is longer than this value, you can use the data type Memo.

For the Date/Time field, you must specify a format so that the system knows how to process the entered data. For example, if you select Short Date Format, the system will expect you to enter exactly dates(in the Russian version - DD.MM.YYYY), and if you select “Short time format”, you will have to enter HH:MM (hours and minutes) in this field.

In the input mask property line, it is possible to create a mask according to which data will be entered. For example, for the field phone number you can set a mask 00-00-00 , according to which phone numbers will be entered.

As the value of the “Condition on value” property, you can specify verification rule, that is, a Boolean expression that must evaluate to TRUE when data is entered into this field. For example, if for the conversation date field you write:

The system recognizes as an error the entry into this field of any value equal to or greater than the current date.

In the following property, you can record an arbitrary error message that will be issued by the system, for example: “This date value is not valid” or “the date is invalid.” In the future, if the date is entered incorrectly, this message will appear on the screen.

The Required Field property can be set to Yes (blank values are not allowed) or No (blank values are allowed).

For primary table key (the “Subscriber Number” field) in the “Indexed Field” property, you need to select: “Yes, matches are not allowed,” and then click on the “Define Key” button in the toolbar (with the key image). This defines a key field, or simple index.

Having completed creating the table layout, you must save the created table and exit the table designer window. If you close the designer window without saving the layout, the system will remind you that you either need to give the table a name or cancel your work. Let's give the created table the name “Subscriber”.

If it is needed change(modify) the layout existing object, you need to select this object in the list of the corresponding tab of the database window and click the button Constructor. A window with a layout of the object will appear on the screen (the same as when creating it). You can correct the layout and save it under the same or a different name.

After creating the layout, data is entered into the table. By double-clicking on the table name, it will open in table mode and you can begin entering data (Figure 2.3). If the first field in the table is a key field of the Counter type, then the contents of this column are automatically increased by 1 when you enter a new line. This ensures that the column values are unique.

Relational DBMSs are the second generation of DBMSs, based on the use of the relational data model proposed by Z. F. Codd in 1970. In the relational model, all data is logically structured within relationships (tables). Each relationship has a name and consists of named attributes (columns) of data. Each tuple (row) of data contains one value for each attribute. The great advantage of the relational model lies precisely in this simplicity of the logical structure.

Commercial systems based on the relational data model began to appear in the late 1970s and early 1980s. Currently, there are several hundred types of different relational DBMSs, both for mainframes and personal computers, although many of them do not fully correspond to the precise definition of a relational data model. Examples of relational DBMSs for personal computers are DBMS Access and FoxPro from Microsoft, Paradox from Corel Corporation, InterBase and BDE from Borland, and R: Base from R: Base Technologies.

Taking into account the requirements, capabilities and resources of the state of emergency “Nal”, it was decided to choose the Microsoft Access 2000 DBMS as the target DBMS.

MS Access is, first of all, a database management system (DBMS). Like other products in this category, it is designed for storing and retrieving data, presenting information in a convenient way and automating frequently repeated operations (such as accounting, accounting, planning, etc.).

Figure 6.1 shows Microsoft's original marketing concept for Access. This simple graphic illustrates the capabilities of working with MS Access at all levels. Objects are listed at the bottom level of the hierarchy; they allow you to easily create tables, queries, forms, and reports. Expressions (functions) can be used to perform simple data manipulation to verify that data has been entered correctly, to support business rules, or to display a number with a currency symbol. Macros allow automation without the need for programming, but it must be taken into account that VBA (Visual Basic for Applications) code allows the user to program more complex processes. And finally, using the Windows API (Application Programming Interface), which accesses functions or Dynamic Link Libraries (DLLs) written in other languages, such as C, Java or Visual Basic, the programmer can write an interface for other programs and data sources.

Let's list the main advantages of MS Access:

1)Data types. MS Access provides all the basic data types, some of which, such as numbers, have several varieties.
2)Indexes. Fields can be indexed, which significantly reduces the time spent searching for the information you need.
3) Restrictions. Restrictions on entered data can be set both at the table level (by entering an input control expression in the Value Condition property line) and at the form level (in the form field properties window in Form Designer mode).

Representation. It is possible to create, open and modify a view in the same way as with MS Access queries. Using views, you can find information stored in tables, related tables, or even other views.

Stored procedures and triggers. Creating stored procedures is much like writing a procedure in Visual Basic, but instead of writing Visual Basic code, you use SQL statements. A trigger is a special type of stored procedure. Which is executed when the user changes data in the table. Triggers are a powerful tool for maintaining data integrity across an entire database.

Primary keys. The system maintains primary and foreign keys and enforces data integrity at the kernel level, which prevents incompatible update or delete operations.

Users and roles. In the MS Access DBMS, users are designated as members of a certain group. There are two default groups: administrators and users, but additional groups can be defined. Groups and users are granted access rights that allow them to regulate the list of operations allowed for them with each database object.

8)Requests. One of the most powerful features of MS Access is also the most important. You can build queries on any data in tables, define the sort order, create calculated expressions, and enter criteria for selecting the desired records. You can display the results of a query as a table, form, or report. To establish relationships between tables, you can use the Query Builder.
9) SQL. Wide range of possibilities for using the SQL language to manipulate data and build complex queries (join queries, server queries, control queries).

User defined functions. The user has enormous opportunities to create his own procedures and functions using VBA programming constructs.

Transaction support. The MS Access system supports transaction processing with a guarantee of their integrity. In addition, user-level security is provided, which allows you to control access to data for individual users and entire groups.

Reference system. Microsoft's help system is one of the best in the industry. MS Access provides context-sensitive help that you can access by just clicking , and reference information on the issue of interest will immediately appear on the screen. In addition, at any time you can use the services of the Assistant or read the tooltip.

Import, export and link external files. MS Access allows you to import and export files in many well-known formats, including dBASE, FoxPro, Excel, SQL Server, Oracle, Btrieve, many ASCII text formats (including those with a fixed line length or specified delimiter), as well as HTML data. As a result of exporting an MS Access table, a file is created in the specified format. Linking means that you can use external data without creating MS Access tables. A very powerful feature is linking MS Access tables with their external tables and then sharing them; this applies to MS Access, dBase, FoxPro, SQL Server tables.

WYSIWING forms and reports. The form and report designer windows have the same interface and provide the user with many options. The form or report is designed according to the WYSIWYG (What You See Is What You Get) principle. By adding another control element, the user sees how the created form or report changes.

15) Network support. The Microsoft Access DBMS can be used as a stand-alone system on a single personal computer or as a multi-user system on a network. Client/server implementation. Microsoft Access 2003 can create an .adp (Access project File) file that can store forms, reports, macros, and VBA modules locally and can connect to a remote SQL Server database using OLE DB technology for display and operation with tables, views, relationships and stored procedures. Such an implementation may also use the MSDE database engine.

These are just some of the capabilities of MS Access that make this system one of the best DBMSs on the market today. It remains to add that this is one of the most common DBMS, since it is included in the standard Microsoft Office package.

A powerful arsenal of Microsoft Access tools makes it possible to successfully solve the problem of automating sales accounting in the private enterprise “Slast”.

Main objects of MS Access DBMS

The MS Access database management system (DBMS) is a relational database management system running in Windows XP. It provides all the necessary tools for defining, processing, and managing data when working with large volumes of information. The DBMS allows you to create applications that run in the Windows environment and fully meet the customer's needs.

The DBMS includes tools for managing tables, queries, forms, reports, macros and modules as independent objects stored in one database file (extension .mdb). This makes creating related objects and checking data integrity much easier.

Let's briefly look at the composition of the database file and the architecture of the DBMS.

The DBMS calls everything that can have a name objects. In an Access database, the main objects are tables, queries, forms, reports, macros and modules.

Table is an object defined to store data. Each table includes information about a real-world object, such as a firm's clients. A table consists of a header and a body. The header includes the object attribute names (columns) and their properties, such as the customer's last name, phone number, and address. The body contains tuples (rows), each row representing a set of column values that store data about a particular object instance. For example, customer information:

Semenov; 75-48-09; Ryazan, st. Polevaya, 20.

For each table, you can define a primary key to ensure that each row is unique, one or more indexes to ensure the ordering of records, and other properties.

When working with Access, tables are one of the main objects; all other elements, such as forms, queries and reports, are built on their basis. The table collects data on a specific topic, for example, all information about the company’s clientele. Each row of data in the customer table contains information about a specific customer. This information may be heterogeneous, and therefore the line consists of several different types of fields containing the client’s last name, telephone number, address, account number, etc.

An Access database can consist of several tables, each of which stores information on one topic. One table can store information about clients, another - about all trade agreements that have been concluded with certain clients, the third - information about expenses, taxes and expenses for the development of the company, the fourth - an inventory list of the warehouse fund, the fifth - timing of exhibitions and presentations, etc.

Request is an object that allows the user to obtain the desired data from one or more base tables and other queries. In a query, you can specify conditions that the data must satisfy. Thanks to this, the query allows you to extract only the necessary data from a large array of information stored in the database. To create a request, use a sample request (QBE) or instructions SQL. You can create queries to select, update, delete, or add data. Using queries, you can create new tables using data from one or more tables that already exist.

Form is an object designed primarily for entering data, displaying it on the screen, or controlling the operation of an application. Forms are used to implement customer requirements for presenting data from tables and queries. The forms can be printed. Using a form, you can launch in response to some event macro or procedure, performing certain data processing.

Report is an object designed to create a document that can later be printed or included in a document in another application. Before printing the report to the printer, you can view it on the screen.

Macro is an object that represents a structured description of one or more actions that will be performed in response to a specific event. For example, you can define a macro that, in response to selecting an element on the main form, opens another form. Using another macro, you can check the value of a field when its contents change. You can run another macro or module function from one macro.

Module is an object containing programs in Microsoft Access Visual Basic that can be developed by the user to implement non-standard procedures when creating an application.

Distinctive features of MS Access

Definition 1

Database management systems (DBMS) are a separate class of software products whose main task is to store and manage large volumes of data.

There are various mathematical data models on which DBMSs are based. Regarding data models, DBMSs are divided into hierarchical, network, relational, object-oriented and others. Today, DBMSs based on the relational model are considered the most popular. The class of relational DBMSs includes: MS SQL SERVER, PostgreeSQL, MySql, InterBase, MS Access. The main functions of relational DBMS are:

creating, editing and deleting table structures;
adding, changing and deleting records in tables;
building queries based on the SQL language;
creating stored procedures on the database server.

To develop a full-fledged information system with a user interface and printing of various forms of documents, a DBMS alone is not enough. We also need a high-level programming language, perhaps even more than one.

MS Access is not an ordinary DBMS. Rather, this software product is a comprehensive development environment that includes a relational DBMS. In addition to the listed functions, MS Access has a number of additional features:

creating user interfaces called forms in a visual editor;
building report layouts with subsequent printing;
use of the built-in programming language Visual Basic for Applications to build application business logic.
the use of macros, which allow users new to programming to insert small pieces of code into their applications.

Note 1

Thus, MS Access allows you to develop full-fledged applications and even information systems based on relational databases without using additional development tools.

Basic MS Access Objects

The MS Access model consists of the following main objects:

table – a structure for storing data based on the relational model;
form – user interface window;
query – an object that allows you to select data from tables according to various criteria;
report – an object that allows you to prepare a printed form of the output document based on data stored in the database;
module – programs in VBA that create additional functionality in the information system.
macro is a program object that allows you to use programming elements without knowledge of the VBA language.

Starting to work in MS Access is preceded by designing a data model. The developer designs tables and relationships between them using any convenient means. After this, he can begin the first stage of working in MS Access - creating tables. The tables created using the “Data Schema” tool are linked in accordance with the project.

Filling tables with data directly is possible, but not convenient. These actions are best done through the user interface. Therefore, the next step is to create the forms. Forms in MS Access are:

simple (single) - show only one table record;
tape – show all table records at once;
complex - consist of a main and subform, which are linked by selected fields.

You can create forms in three ways: automatically, using the “form wizard” and using the designer. The form designer provides the most extensive opportunities for creating form layouts based on the developer’s personal project.

Queries are most often used to select data based on various criteria and conditions. Most relational DBMSs use the SQL language to create queries, which is not easy for the average user to master. In MS Access, the complexities of the SQL language are hidden from the user by the convenient Query Builder tool. There are also several types of requests:

Select query – allows you to compose data from different tables into one table based on constructed relationships.
Query with condition – allows you to impose conditions in the form of logical expressions on individual fields. There can be several conditions, then they are connected by the logical operators AND, OR.
Request with a parameter – allows you to put in the request condition not a constant value, but a parameter whose value is requested from the user.
Query with group operation - allows you to apply operations to a group of data such as summing, counting the number of rows, finding the average value, searching for the minimum and maximum of the group.
A crosstab is a special type of group query that produces a crosstab with row headings, column headings, and intersection values.
Request to add a record.
Request to update a record.
Request to delete a record.

A form can have not only a table but also a query as a record source. This allows you to create even more flexible interfaces.

Note 2

Reports are used to create document layouts and then print them. The principles of creating reports are similar to the principles of creating forms. The report data source can be a query or a table. A report can be created, just like a form, using the “Report Wizard”. For more professional users, there is a “report designer” that allows you to develop a layout with a custom appearance.

The MS Access programming environment is object-oriented. This means that a certain list of events can potentially occur with all MS Access objects. For example, opening a form, clicking a button, updating a record, double-clicking a text field, etc. The user can “bind” program code that implements a certain algorithm to the occurrence of these events. For example, when you click a button, a procedure is activated that displays a dialog box with the message “Enter your name.” An experienced user can use VBA to program such procedures. If the user is not familiar with programming in VBA, then he can solve many problems using macros - small standard “blanks” of VBA code.

The listed capabilities are quite sufficient for developing a small information system.

Other MS Access Features

Initially, MS Access was intended as an environment for developing information systems in which both the database and the user interface were stored in one file on one computer. Such information systems are usually called “desktop”. However, as new versions were released, MS Access gained new capabilities. In versions starting with MS Access XP, it became possible to create a separate data server and connect several client computers to it. You can also create a database in another DBMS (MS SQL SERVER, MySQL) and connect forms, reports and queries from MS Access to it. It is possible to import and export data in such universal formats as:

EXCEL;

Section 2. Database organization

Lecture 13. DBMSMSAccess. Creating tables and defining relationships

Currently, the most widespread are relational DBMSs, which include office DBMSs - MS Access . Main advantage MS Access is its presence in the package MS Office. DBMS MS Access It has rich functionality and is easy to use. The visual development environment is especially convenient, significantly speeding up the process of creating a database.

Starting with the package MS Office 2000, included with MS Access New functionality has appeared:

§ Support for client-server applications. Database servers can be used Microsoft SQL Server 6.5 or 7.0, and data processor MSDE (Microsoft Database Engine).

§ Exporting database objects to format HTML , creation of static and dynamic Web -pages for accessing data.

§ Ability to use email to send data, etc.

Interaction with the environment MS Access carried out through the main menu and toolbars; their use and configuration is no different from other applications in the package MS Office. DBMS MS Access creates the entire system in one file with the extension *. mdb , this is associated with the disadvantages and advantages of this DBMS.

Basics of working in a DBMS MSAccess

After creating or opening a database through the dialog box " Microsoft Access » the working window of the “Database” dialog will open » (Figure 13.1).

Figure 13.1 - “Database” dialog box

The “Database” working window allows you to access the main objects MS Access , such as: tables, queries, forms, reports, pages, macros, modules. Let's define the main objects used in the DBMS Access:

§ Table – the main object of a relational database necessary for storing data.

§ Request is a question generated in relation to the database. It is queries that allow you to obtain the necessary information from relational databases. They are based on relational algebra and relational calculus.

§ Form – display format in the form of a Windows OS window. The entire user interface is based on forms.

§ Report – a formatted representation of data displayed on a screen, printer, or file.

§ Page – a display format in the form of a form used to access Web pages. New object added to MS Access 2000.

§ Macro – a set of one or more macro commands that perform certain, most frequently used operations.

§ Module – program in language Access Basic . Using modules allows you to more fully realize the capabilities MS Access.

Almost every object MS Access , in addition to macros and modules, has several creation options; the constructor and wizard can be called traditional:

§ Master – allows you to create a general object and is a dialogue window with a series of questions asked sequentially, the answers to which allow you to clarify the properties of the created object. Typically used as the primary means of creating an object.

§ Constructor – provides powerful customization and editing mechanisms, usually serves as a means of editing an already created object and giving it the appropriate appearance.

Creating tables

DBMS MS Access supports a number of basic data types: text, numeric, currency, counter, date/time, logical, MEMO field, object field OLE, hyperlink.Among the listed types, the last two can be considered extended: object field OLE and hyperlink, the latter is self-explanatory. Object field OLE allows you to import objects that support the technology into database tables OLE eg images, documents MS Word, etc.

To the names of tables and fields in the DBMS MS Access the following restrictions apply:

§ The field name cannot contain more than 64 characters.

§ The use of control characters with codes 0–31 and symbols (. ! ‘ ) in field and table names is not allowed.

§ The field name cannot begin with a space; in addition, it is generally not advisable to get used to using spaces; it is better to use an underscore.

DBMS MS Access offers several ways to create tables:

§ Table mode – allows you to create a table in table mode, i.e. A table opens in which you need to enter column headings (fields). All field types are accepted as text by default.

§ Constructor – allows you to create a new table in the table designer, and you can immediately specify all possible properties of the table fields.

§ Table Wizard – allows you to create a table using a wizard that has a large supply of table templates with already defined properties. The creation process involves selecting the required fields from the sample tables.

§ Importing tables – allows you to import tables from external files, for example from existing databases MS Access, MS Excel tables etc. As a result, a table obtained from an external source will be created in the current database.

§ Relationship with tables – allows you to communicate with tables located in external files.

The main means of creating tables in MS Access is a table designer. Let's look at the structure of the table designer (Figure 13.2) and the features of creating tables in this mode.

Figure 13.2 - Table designer window

At the top of the designer window there is a table form containing three columns: “Field name”, “Data type”, “Description”. The “Field name” column indicates the names of the fields (attributes) of the table being created. The "Data Type" column indicates the basic data types of the corresponding fields. The Description column is optional and is intended for comments.

Each field, along with its specific type, has additional properties that appear in the Field Properties section at the bottom of the designer.

You can select the following field properties located on the “General” tab:

· Field size – defines the maximum number of characters (used for fields of the following types: text, numeric, counter);

· New values – defines the method for changing counter values (only for the counter type);

· Field Format – defines the format for displaying field values (applies to all fields except object fields OLE);

· Number of decimal places – determines the number of decimal places when displaying numbers (used for types: numeric, monetary);

· Input mask – allows you to set an input mask to simplify data entry (used for fields of the following types: text, date/time, numeric, monetary);

· Signature – defines the text that will be displayed as a column heading instead of the field name (for all fields);

· Default value – allows you to set a value that is automatically entered into the field when creating a new record;

· Condition on value – defines the requirements for data entered in the field;

· Error message – contains a message that will be displayed if the condition for the input value is violated (see above);

· Obligatory field – indicates whether mandatory entry of values in the field is required;

· Blank lines – determines whether empty lines can be entered into the field (for text fields only);

· Index field – defines an index created on one field;

· UNICODE compression – enables field compression when using encoding Unicode (for MEMO fields, hyperlinks and text fields).

Comment.Defining such properties as “Input mask”, “Default value”, “Condition on value”, “Error message” will improve the convenience and speed of users, as well as reduce the number of possible errors.

Tab "Substitution" provides a means of creating controls for alternative input of values, such as a list box and a combo box. The default is a normal field.

The primary key is assigned using the command Edit - Key Field, context menu command Key field or toolbar button. A complex primary key can be created in a similar way by first selecting the fields included in the primary key with the mouse and holding down the key Shift or Ctrl.

As in many DBMSs, keys are defined by indexes (including the primary key), so you can use the “Indexes” dialog box to assign complex keys or specially configure them (Figure 13.3).

The Index column defines the names of the table's indexes, and the table's primary key is always named PrimaryKey . The “Field Name” column allows you to specify the set of fields included in the index. Specific index options are defined at the bottom of the Index Properties window.

Figure 13.3 - Dialog window for creating composite indexes.

Typically, after all the tables have been created and all the properties of their fields have been defined, relationships between the tables are created. Relationships allow you to implement the principles of maintaining integrity between data located in different tables. Connections are created through the “Data Schema” tool, called by the command Service - Data Schema or toolbar button. Adding tables to the diagram is done through the “Add Table” dialog box by simply selecting and clicking the “Add” button (Figure 13.4). It should be noted that not only tables, but also queries can participate in relationships.

Figure 13.4 – “Data Schema” and “Add Table” dialog boxes

Creating relationships between tables is carried out by simply dragging, to do this, press the left mouse button on the primary key field of the main table and drag to the foreign key of the subordinate table, then release the left mouse button. As a result, the “Change Links” dialog box will appear (Figure 13.5).

In the Edit Relationships dialog box, the Table/Query and Related Table/Query combo boxes indicate the master and child tables, respectively. The left part of the list contains the fields of the main table participating in the connection, and the right part indicates the fields of the subordinate table associated with them. When connecting using simple keys MS Access fills lists of connections automatically, but when connecting complex keys you have to fill the lists manually.

The “Change Links” dialog box allows you to define the principles for maintaining referential integrity; for this purpose, use the “Ensure Data Integrity” checkbox group. . If the “Ensure data integrity” checkbox is not selected, then MS Access does not ensure that the integrity of related records is maintained.

MS Access supports two basic principles for maintaining referential integrity on deletion:

§ prohibition on deleting records of the main table that are referenced in subordinate tables if the “Cascading deletion of related records” checkbox is not enabled;

§ when deleting a record in the main table that has links in subordinate tables, the latter are cascaded if the “Cascade deletion of related records” checkbox is enabled (Figure 13.5);

Similar principles apply when updating records, but are controlled by the "Cascading update of related fields" checkbox.

Figure 13.5 - “Change Links” dialog box

Control questions

1. What new features have appeared in the DBMS MS Access 2000?

2. What categories of objects are distinguished in the DBMS? MS Access?

3. What methods of creating objects can be called basic?

4. Name the main data types used in MS Access.

5. What restrictions are imposed on the names of fields and tables in the DBMS MS Access?

6. What operating modes are used to work with the table?

7. Name the main properties of fields MS Access.

8. What is the purpose of the Lookup tab?

9. How are keys and indexes defined?

10. How relationships are created between tables in a DBMS MS Access?

11. What principles of supporting referential integrity are used by the DBMS? MS Access?

Tasks for independent work

Exercise 1. Taking into account the basic data (tables 13.1 – 13.5), create the remaining tables of the IS “Library” database and establish connections. Key fields are shown in italics. The description of creating the Books table is given below.

Table 13.1 – Properties of fields in the “Books” table

Signature	Data type	Size	Obligation Field	Index. field	Empty values	Conditions
ISBN	Text	14	Yes	Yes(no matches allowed)	No
Name	Text		Yes		No
	Text
Publishing house	Text		Yes		No
Place of publication	Text		Yes		No
The year of publishing	Numerical	Whole	Yes			>19 59
Pages	Numerical	Whole	Yes

Table 13.2 – Properties of fields in the “Instances” table

Signature	Data type	Size	Obligation field	Index. field	Input mask
Inventory number	Numerical	Length whole	Yes	Yes(no matches allowed)
ISBN	Text		Yes	Yes (matches allowed)
Library card	Numerical	Length whole	Yes	Yes (matches allowed)
Availability	Logical		Yes
Date taken	Date Time	Kr.f.dates			Yes
return date	Date Time	Kr.f.dates			Yes

Table 13.3 – Properties of fields in the “Readers” table

Signature	Data type	Size	Obligation field	Index. field	Blank values	Conditions
Library card	Numerical	Length whole	Yes	Yes(no matches allowed)
Full name	Text		Yes		No
Date of Birth	Date Time	Kr.f.dates	Yes
Floor	Text		Yes		No	"M" or "F"
Home phone	Text
Work phone	Text

Table 13.4 - Properties of fields in the “Catalog” table

Signature	Data type	Size	Obligation field	Blank values
Knowledge area code	Numerical	Whole	Yes
Name	Text		Yes	No

Table 13.5 - Properties of fields in the “Communication” table

Signature	Data type	Size	Obligation field	Index. Field	Blank values
ISBN	Text	14	Yes	Yes(matches allowed)	No
Knowledge area code	Numerical	Whole	Yes	Yes(matches allowed)

An example of creating a “Books” table

Let’s create the “Books” table of the “Library” database based on the previously created relational schema, taking into account some previously formulated restrictive conditions. For convenience, the main properties of the fields are summarized in tables 13.1 – 13.5

1. In the database dialog box (Figure 13.1), select the “Tables” category.

2. Open the table designer window with the command Create-Constructor or in another way.

3. In the dialog box that opens (Figure 13.2), place the cursor in the first free line of the “Field name” column and enter the field name -ISBN.

4. In the Data Type column » indicate the type - text(this value is set by default).

5. We specify the field size by placing the cursor in the property position – “Field size” (at the bottom of the designer window, Figure 13.2) and specifying the value – 14 .

6. Actual field names may differ from those shown to users. To do this, set the value of the “Signature” property toISBNor otherwise if required by the user.

8. We repeat steps 3 – 7 and similarly create other table fields, but for the field The year of publishing Additionally, set the “Condition on value” property to >1959 . With the “Condition on value” property, the “Error message” property is usually set, for example the value The book was published before 1960!

9. Specify the primary key for the fieldISBN, to do this, place the cursor on it and execute the context menu command Key field.

10. Let's save the created table under the name Books. The “Save” dialog box is called every time you close a modified table.

The remaining database tables are created in a similar way.

Example of creating a fixed list

In some cases, it is necessary to create fields into which fixed values are entered from a list, for example for a table Readers in field Floor Only two values can be entered "M" or "AND". To create a combo box for a field Floor Let's do the following:

1. Select a field Floor.

2. Let's go to the tab of the properties form “Substitution”.

3. Let's set the property Control type in meaning Field with list.

4. Let's set the property Row source type in meaning List values.

5. Let's set the property Row source in meaning "M"; "F"

6. Let's save the changes to the table.

Figure 13.6 – Data diagram of the “Library” database

Task 2. Establish relationships between tables, as shown in Figure 13.6. Follow the rules for maintaining referential integrity defined in the problem statement.