Database. Microsoft Access. Description of the Microsoft Access database Microsoft Access how to use

Application program Microsoft Access 2007 designed to create Database. Access 2007 uses logically related tables. DBMS Microsoft Access 2007 provides convenient and reliable control data that is stored in tables.

The essence of creating a database in Microsoft Access 2007 is that you first need to create a database structure (create a table structure and establish relationships between them), and then you need to fill in the tables of the new database.

In addition, it is advisable to create forms for entering data into tables, requests to search for information in the database and reports to display the necessary information from the database in a convenient form on the screen, print or file.

The database can be created either manually (a new empty database) or based on templates.

Templates do not always correspond to the required database, but with their help you can quickly create a database close to what is required, and then you can change it and fill it with your own data. On startup application program Access 2007 will display a new home page

"Getting Started with Microsoft Access 2007", shown in Fig. 1.

Rice. 1. New home page When creating a database from templates, you can select the required template on the Getting Started with Microsoft Access 2007 page from the Template Categories or the templates From Microsoft Office

Online". When you select, for example, the Faculty template in the Local templates category, a description of the database will appear on the right side of the application window. Below, in the text field, the file name is indicated: Faculty.accdb and a folder icon with which you can save the file to the desired directory (by default the My Documents folder is indicated).

The file name and file storage directory can be changed. Then you need to click on the Create button. As a result, the Faculty database window will appear: database (Access 2007) - Microsoft Access. The Editing Area will display the Faculty List that needs to be filled out. If the Navigation Pane is closed, you must open it by pressing F11 or clicking on the Open/Close Navigation Pane Border button. Microsoft Office is an editor that allows you to create and edit. It also has a built-in application that allows users to work with databases. Databases are, first of all, a convenient tool for storing, organizing and retrieving necessary information. In this material, the Microsoft Access editor will be discussed in detail and published step-by-step instruction for working with the application.

What is Microsoft Access

Microsoft Office Access – powerful editor, which is a database management system based on a relational model. It should also be noted that it uses a dynamic exchange model between network resources and applications. Wherein editor uses advanced tools for processing any type of information and presenting them in a clear, consistent structure.

Access also includes support for dynamic ActiveX libraries. This technology, which helps not only to present information in the form of text, but also in the form of multimedia objects. The relational model allows you to establish connections between databases and control any change, making timely adjustments.

Some users believe that one of Microsoft editors Office Excel is similar to Access, but this is misleading. Excel is a tool for working with spreadsheets, and the latter, in turn, is used to create databases in the form of tables.

Principle Excel work is based on organizing data within a separate table, Access allows you to do more complex tasks, working with large array information. And in conclusion, it is necessary to note an important feature: Excel is designed for one user, since changing information is local in nature, and Access implies multi-user work with databases.

Why is it used?

The editor is used to fully automate work with databases in various fields of activity, business, personnel management, etc. It is worth noting that due to the fact that software has a universal structure, it helps to get rid of excess data when you need to change required parameter, not by introducing a new one, but by adjusting the old one. Moreover, the changes will be reflected not only in the main database, but also in those associated with it.

Application structure

Convenience of working with the program is achieved thanks to the presence of elements that allow automate the process creating a base. The following is a list of the main components of the software.

Components:

table. The application component is designed to record and store data;
request. The element is designed to obtain information from one or more tables. It is a means for accessing related databases and third-party software;
form. The object is used to represent entered information in a more user-friendly manner;
report. Allows you to obtain the final result in the form of a finished document;
macro. It is an element containing a sequential description for performing a particular action. Using it, you can specify a command that will perform a specific task, for example, checking for changes in data in one of the tables;
module. A component that contains software written in a language Visual programming Basic. With its help, the editor significantly expands its functionality. This is achieved through the use of functions and procedures designed to respond to certain changes;
access page. With its help, you can access remote databases stored on other personal computers.

Relationship with other DBMS

Access allows you not only to use your own entered information, but also to establish a relationship with another DBMS. There is also the ability to import from other applications, for example, dBase, MySQL, Paradox, FoxPro, Excel. For user convenience, it is possible not only to use import, but also link data with other programs and network resources.

User Interface Overview

Important! The interface will be reviewed using Microsoft Access 2013 as an example. However, Microsoft Access 2007 and 2010 are almost identical

The interface in this version of the software is designed to improve the convenience of working with numerous elements; it is based on a ribbon that provides quick access to main functions and commands.

User interface:

« Backstage" (create). An interface element appears after the program starts and allows the user to select a template for creating a database. During work, to go to this tab, you need to follow the path “File” and “Create”.
« File" Allows you to save, open, print the finished document, as well as set Access parameters and set a suitable theme.
« Ribbon" It is the main element when working with the editor. It contains modules with tools for creating and editing a database. It also includes a panel quick access, which contains the most commonly used components.
« Navigation area" Allows you to see the result of the actions performed and reflects the structure of the database.
« Dialog window" An interface element with which the user can clarify the parameters of an object.
" To display the component, you will need to right-click on the object element. It includes commands that depend on the task being performed.
" Used to switch the document presentation mode, and to display the status of the current operation.

The interface was reviewed using Microsoft Access 2013 as an example. It may differ in lower editions.

Working with databases in Access

Creating a database

You can create a database in several ways: from scratch or using a ready-made template. In the first case, the user needs to independently create elements and database objects; in the second option, everything is much simpler. We launch the ready-made template and enter our information. Next, we will consider in detail both options for working with databases.

Clean base

Let's start creating a new database and then filling it with information:

Create from a template

Working with the template looks like this:

Filling the database

Filling the database involves creating a table and entering the necessary information into it.

To begin with, it should be noted that using MS Access you can import. To do this, select the “External” tab and click on the “Import” icon. Next, a new window will open where you need to specify the path to the file and click “Ok”. Then the import window will appear again, click “ Further» and set information about each object. We set a key that will help quickly process information and click “ Further" After this, the object will appear in the main window and you can begin creating it.

Process of creation:

It should be noted that the “Price” field has a numeric parameter. This is due to the fact that information in monetary terms has a dimension expressed in rubles (if the official localized version is used office suite). If we're talking about price computer peripherals, then, as a rule, to express its comparative value, conventional units. For such a case, a numerical parameter is used; this will avoid reconfiguring the entire database.

Project No. 1 “Products”:

Project No. 2 “Supply”:

Data Schemas

The relationship between the tables is established using the structure used in the project. In this case, the structure is displayed graphically, which makes data processing much easier.

We establish relationships between projects:

Now we need establish a connection between two objects, we do it like this:

Creating requests

Classic sampling

A sample query allows you to select data from the database according to pre-created conditions. In our project, a selection will be created that will allow you to find products by their names. We will carry out the work in the “Products” database.

Creating an Entity

A query to create an entity in Microsoft Access selects from interrelated tables and other previously created queries. Unlike the first option, the result will be saved in a new permanent table.

The process looks like this:

To add, delete, edit

This type of query implies the creation and execution of some action, as a result of which the parameters in the table will change.

We create a request as follows:

Request to add:

For editing:

Creation and design of forms

Forms are one of the elements that are designed to properly organize data storage.

What forms are needed for:

the purpose of the forms is to data output on the screen in a user-friendly form;
launch control. In this case, the main purpose of the forms is to run macros;
show dialog boxes . Using forms, you can display a warning about possible errors.

Use "" to create an object:

After this, the user will see new inset, where the table will be presented as a form. As you can see, the perception of information has become much more convenient.

Let's consider the option of creating forms using the Designer:

We create a form from scratch using "". Using this option, you can customize the design, change the fill of the field, add multimedia files, etc.

You can also configure the required parameters for the picture: “Background color”, “Background type”, “Borders”, etc.

We generate reports

To work with reports we will use “”:

Report using the Constructor:

The main purpose of this program is to create and work with databases that can be linked to both small projects and large businesses. With its help, you will be able to conveniently manage data, edit and store information.

The Microsoft Office suite application – Access – is used to work with databases

Naturally, before you begin, you will need to create or open an existing database.

Open the program and go to the main menu by clicking on the “File” command, and then select “Create”. Creating new base, you will be presented with a choice of a blank page that will have one table or a web database that allows you to use the program’s built-in tools for, for example, your publications on the Internet.

In addition, to make creating a new database as easy as possible, the user is provided with templates to choose from that allow him to create a database focused on a specific task. This, by the way, can help you quickly create the necessary table form without setting everything up manually.

Filling the database with information

Having created the database, you need to fill it with relevant information, the structure of which should be thought out in advance, because the functionality of the program allows you to format data in several forms:

Nowadays the most convenient and common type of information structuring is a table. In terms of their capabilities and appearance, tables in Access are not very different from those in Excel, which, in turn, greatly simplifies the transfer of data from one program to another.
The second way to enter information is through forms; they are somewhat similar to tables, however, they provide a more visual display of data.
To calculate and display information from your database, reports are provided that will allow you to analyze and calculate, for example, your income or the number of contractors with whom you work. They are very flexible and allow you to make any calculations, depending on the entered data.
Receiving and sorting new data in the program is carried out through queries. With their help, you can find specific data among several tables, as well as create or update data.

All of the above functions are located in the toolbar, in the “Creation” tab. There you can select which element you want to create, and then, in the “Designer” that opens, customize it for yourself.

Creating a database and importing information

When you create a new database, the only thing you will see is an empty table. You can fill it out manually or fill it out by copying the necessary information from the Internet. Please note that each piece of information you enter must be placed in a separate column, and each entry must have a personal line. By the way, columns can be renamed to better navigate their contents.

If all the information you need is in another program or source, the program allows you to configure the import of data.

All import settings are located in a separate tab in the control panel called “External Data”. Here in the Import and Links area, the available formats are listed, including Excel documents, Access, text and XML files, Internet pages, Outlook folders, etc. Having selected the required format from which information will be transferred, you will need to specify the path to the file location. If it is hosted on a server, the program will require you to enter the server address. As you import, you will encounter various settings that are designed to correctly transfer your data into Access. Follow the program's instructions.

Basic keys and table relationships

When creating a table, the program automatically assigns each record a unique key. By default, it has a column of names, which expands as new data is entered. This column is the primary key. In addition to these primary keys, the database may also contain fields related to information contained in another table.

For example, you have two tables containing related information. For example, they are called “Day” and “Plan”. By selecting the “Monday” field in the first table, you can link it to any field in the “Plan” table and when you hover over one of these fields, you will see information and related cells.

Such relationships will make your database easier to read and will certainly increase its usability and efficiency.

To create a relationship, go to the “Database Tools” tab and in the “Relationships” area, select the “Data Schema” button. In the window that appears, you will see all the databases being processed. Please note that databases must have special fields designated for foreign keys. In our example, if in the second table you want to display the day of the week or a number, leave an empty field, calling it “Day”. Also configure the field format as it should be the same for both tables.

Then, with two tables open, drag the field you want to link into the specially prepared field for foreign key. The “Edit Links” window will appear, in which you will see individually selected fields. To ensure data changes in both related fields and tables, check the box next to “Ensure data integrity.”

Creation and types of requests

A query is an action in a program that allows a user to edit or enter information into a database. In fact, requests are divided into 2 types:

Selective queries, thanks to which the program retrieves certain information and makes calculations on it.
Action requests that add information to the database or remove it.

By selecting “Query Wizard” in the “Creation” tab, the program will guide you through the process of creating a specific type of request. Follow the instructions.

Queries can greatly help you organize your data and always access specific information.

For example, you can create a custom query based on certain parameters. If you want to see information on a specific date or day of the “Day” table for the entire period of time, you can set up a similar query. Select the “Query Builder” item, and in it the table you need. By default, the query will be selective; this becomes clear if you look at the toolbar with the “Selection” button highlighted there. In order for the program to search for exactly the date or day that you need, find the line “Selection condition” and enter the phrase [what day?] there. Remember, the request must be placed in square arms and end with a question mark or colon.

This is just one use case for queries. In fact, they can also be used to create new tables, select data based on criteria, etc.

Setting up and using forms

Thanks to the use of forms, the user can easily view information for each field and switch between existing records. When entering information for a long time, using forms simplifies working with data.

Open the “Creation” tab and find the “Form” item, clicking on which will display a standard form based on the data in your table. The information fields that appear are subject to all sorts of changes, including height, width, etc. Please note that if there are relationships in the table above, you will see them and can reconfigure them in the same window. At the bottom of the program you will see arrows that will allow you to sequentially open each column of your table or immediately move to the first and last. Now each of them is a separate record, the fields of which you can customize by clicking on the “Add fields” button. The information changed and entered in this way will be displayed in the table and in all tables attached to it. After setting up the form, you need to save it by pressing the key combination “Ctrl+S”.

Creating a report

The main purpose of reports is to provide the user with an overall summary of the table. You can create absolutely any report, depending on the data.

The program allows you to choose the type of report, providing several to choose from:

Report - an auto-report will be created using all the information provided in the table, however, the data will not be grouped.
A blank report is an unfilled form for which you can select data yourself from the required fields.
Report Wizard - will guide you through the process of creating a report and will group and format the data.

In an empty report, you can add, delete or edit fields by filling them in necessary information, create special groups that will help separate certain data from the rest, and much more.

Above are all the basics that will help you cope and customize the Access program for yourself, however, its functionality is quite broad and provides more fine tuning functions discussed here.

Today any user computer systems on Windows based knows that in standard package office programs from Microsoft includes a unique editor called Access. We will now look at what this program is and how to work with it. This article, of course, will only cover the basic issues of working with the program. Full description All the features of this application will take more than one page.

Access: what is it?

What is it? Microsoft program Access? Access is a full-featured program that is designed to work with any type of database. The program is based on a dynamic data exchange model with online publications and other applications. This program involves the use of automation tools for processing any type of information presented in a structured form. Among other things, Access is also a software package that provides support ActiveX controls. This significantly expands the capabilities of the program in the sense that it can use not only text and tabular components, but also objects from the Internet and multimedia. The connections established in the application between databases (DBs) make it possible to accurately track changes in any of them and automatically adjust parameters in others.

Access: main areas of application use

It is not at all surprising that in most cases Microsoft Access is used to fully automate data analysis processes in business, accounting, and so on. The program, thanks to its universal structure, can eliminate the appearance of so-called data redundancy, when it is necessary to change some parameter not by entering a new one, but by adjusting the old one, and in such a way that this change is reflected in all related databases. An enterprise can use Access to keep records of suppliers, clients and the events in which they are involved. Let's say the supplier's bank details change. Then it will be enough to change them in the database, and the automatic adjustment will affect the remaining databases. In this case, the data will be replaced, not entered new information along with the existing one. This change will only affect related events. In a sense, the user receives complete automation. The same applies to warehouse accounting. Let's assume that a certain group of goods is sold through the corresponding division of the enterprise. Then the product items will be automatically written off in the database of goods available in the warehouse. It is worth noting that these are only the most simple examples. The application actually has more advanced features.

Microsoft Access: structure

If we talk about ease of use, it can be achieved thanks to the presence of basic elements that play an important role in analyzing and processing data. The main elements include the following:

A table is an element that stores basic information in a specific format (text, numeric, graphic);
A query is a means of accessing related items, other databases, or third-party programs;
A form is the presentation of data or information in a user-friendly manner;
The report is the output of the processed results;
A macro is an executable element that allows, when an event occurs, to perform certain actions, generate a report, create a request;
Module – is a tool Visual language Basic, which allows you to significantly expand the capabilities of the program based on the use of numerous functions and the creation of procedures;

Microsoft Access: Connecting with external bases data and other programs

As should be clear by now, Microsoft Access allows you not only to use your own data entered by the user, but also to link them together. The program's capabilities are such that information can be imported from various applications, for example, Paradox, FoxPro, Excel, Word and so on. To simplify procedures, data can not be imported, but linked, not only with these programs, but also with sources on the Internet or network environment. The linking process itself is carried out on the basis of queries similar to how databases work SQL data. By the way Access program supports them too.

How to create databases based on templates?

In Microsoft Access, the main element is the table. This component is appearance is very similar to Excel spreadsheets, however it has more advanced capabilities. And the principle of working with these elements has its own distinctive features. However, creating your own database at startup is quite simple. After the welcome window appears, the user is given a choice of templates, on the basis of which the future database structure in the form of a table will be created. Differently this presentation called Backstage. Here you can also find built-in templates that you will need to perform specific tasks. If none of the presented blanks meets the user’s requirements, which is unlikely, you can search on the official Microsoft resource. Once the template you want is selected, you will need to save it as a file, specifying a name and location. The application will then automatically generate the required table structure.

How to create a database from scratch?

There are a number of limitations to consider in this matter. For example, desktop databases personal computers do not support online publishing. Webbases are not consistent with some of the previous features. Once the initial table has been created, you can proceed to entering information. It is worth paying special attention to the fact that data can only be entered into adjacent rows and columns. You also shouldn't add empty cells between them, as is done in Excel. The most important condition is that each column should contain only one type of data. Therefore, if the format is natively designed to use date and time, information entered into the column with exponent-based calculations will not be recognized. If possible, you should plan the table from this perspective. To make things easier, you can use a special design mode.

Features of importing and linking data with other sources

If we talk about importing data, then the Microsoft Access program has almost unlimited possibilities. The main condition is that the imported data must be divided into tabular types, as is done in Word or Excel. If the import is carried out, for example, in the text version of the Notepad program, then you can use the “Tab” key to create such a structure. It is also possible to use Share Point lists and data linking to simplify work. For this purpose, on the external data tab, which is located in the linking and import group, special team. Here we also offer already ready-made solutions(Word, Excel, etc.). If selected, you will only have to specify the location required file, storage location in the current database, and then confirm your selection.

Afterword

This is what an Access application looks like. On this moment this program is very popular among a wide range of users, as its developers tried to combine the capabilities of other programs in it of this type. This allowed us to do this application very flexible in automating most of the required functions and customization. We can only add that Microsoft Access is a powerful software for data processing. Access makes it easy to create and manage databases. This software product is suitable for both small projects and big business. Access is an excellent assistant for storing various types of information.

Ministry of Education and Science of the Russian Federation

Federal State Budgetary Educational Institution of Higher Education

vocational education

"Khabarovsk State Academy of Economics and Law"

Basics of working with Microsoft Access 2007 database

Khabarovsk 2011

Basics of working with DBMS Microsoft Access 2007: guidelines for performing laboratory work for 1st year bachelor's students of all areas of full-time study / comp. L. V. Samoilova. – Khabarovsk: RIC KhSAEP, 2011. – 32 p.

Reviewer D. V. Timoshenko, Ph.D. tech. Sciences, Associate Professor, Department of Internal Combustion, Tomsk State University

Approved by the publishing and library council of the academy as guidelines for 1st year bachelor's students of all full-time specialties

Lyudmila Viktorovna Samoilova Basics of working with databases Microsoft Access 2007

Guidelines for performing laboratory work for 1st year bachelor's students of all areas of full-time study

Editor G.S. Odintsova

_____________________________________________________________

Signed for printing Format 60x84/16.

Writing paper. Digital printing. Conditional p.l. 1.9. Academician-ed.l. 1.3.

Circulation 100 copies. Order No.___________________________

_______________________________________________________________

680042, Khabarovsk, st. Pacific, 134, hgaep, ritz

Basic Concepts

In the modern world, people have to deal with huge amounts of homogeneous information. This information must be organized in some way, processed using the same type of methods and, as a result, obtain consolidated data or search for it in bulk. specific information. Databases serve this purpose.

Under database It is customary to understand a set of logically organized and interconnected data shared by various tasks within the framework of some unified automated information system.

The software that performs operations on databases is called a DBMS - database management system. A DBMS allows you to structure, systematize and organize data for computer storage and processing.

DBMS– a set of language and software tools designed for creating, maintaining and sharing a database with many users.

Program Microsoft Access 2007 is a database management system. It is included in Microsoft Office Professional 2007, which ensures its connection with other office applications (test Word editor, a program for working with Excel tables). Using the Microsoft Access DBMS, you can easily store and process large volumes of information, control the correctness of data at the stage of its entry, extract the necessary information from the database, prepare reports, and create forms for more convenient work with data. Several users can work with the database simultaneously. Microsoft Access has enormous capabilities, and at the same time, to get started and create your own database, you only need to master a few simple operations.

The Microsoft Access program is a relational DBMS (from the English relation). This means that a database in Access consists of interconnected tables.

A database table is a regular table consisting of rows and columns.

The table columns are called fields (attributes). They store the attributes of the object. Each table field has a unique name and contains a strictly defined data type.

The table rows are called records (tuples). A record contains several table cells that store specific information about objects. Each record contains information about one object. The lines are in random order and do not have numbers. The search for strings is carried out not by numbers, but by identifiers ( keys).

Key– this is the field by which tables are linked.

The key can be simple or compound. A key defined by one table field is called simple. If a key consists of two or more attributes, it is called composite.

The key can be primary or foreign. Primary key uniquely identifies each record in the table; Duplicate key values are not allowed. This means, The primary key must identify a single record (row) in the table, that is, be unique.

External key is an attribute of one relation that is the primary key of another relation. Foreign keys are used to organize relationships between database tables (master and slave) and to maintain referential integrity constraints.

To fill tables with information, you can enter data manually in table editing mode, create form to enter data or import data from external sources. To search, select, and sort data, you can create requests, and for visual presentation of data and printing – reports.