Application program Microsoft Access 2007 designed to create database... Access 2007 uses logically related tables. DBMS Microsoft Access 2007 provides convenient and reliable management of the data that is stored in tables.

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

Also, it is desirable to create shape to enter data into tables, requests to search for information in the database and reports to output the necessary information from the database in a convenient form to 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 the one you need, and then you can change it and fill it with your own data.

When you start the Access 2007 application, the new Getting Started with Microsoft Access 2007 home page appears on the screen, as shown in Figure 1-4. one.

Figure: 1. New start page

When creating a database from templates, you can select the template you want on the Getting Started with Microsoft Access 2007 page from Template Categories or templates From Microsoft Office Online. If you select, for example, the Faculty template in the Local templates category, the database description will appear on the right in the application window. Below, in the text field, the file name is indicated: Faculty.accdb and the folder icon, with which you can save the file to the required directory (by default, the My Documents folder is specified).

The file name and directory for storing the file can be changed. Then you need to click on the Create button. As a result, the Database window Faculty: Database (Access 2007) - Microsoft Access will appear. The Editing area will display the Faculty List, which must be completed. If the Navigation Pane is closed, you must open it by pressing the F11 key or by clicking on the "Open / Close Navigation Pane Border" button.

Microsoft Office is an editor that allows you to create and edit office documents. It also has an embedded application that allows users to work with databases. Databases are, first of all, a convenient tool for storing, organizing and retrieving the necessary information. In this material, the Microsoft Access editor will be examined in detail and step-by-step instructions for working with the application will be published.

What is Microsoft Access

Microsoft Office Access is a powerful editor that 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 sequential structure.

Access also includes support for dynamic activeX libraries... This technology 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 links between databases and control any change, making adjustments in a timely manner.

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

The principle of operation of Excel is based on organizing data within a separate table, Access allows you to perform more complex tasks, working with a large amount of information. And in conclusion, it is necessary to note an important feature, Excel is designed for one user, since the change in 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 a database in various fields of activity, business, personnel management, etc. It is worth noting that due to the fact that the software has a universal structure, it helps to get rid of an overabundance of data when you need to change the desired parameter, not by entering a new one, but by correcting the old one. Moreover, the changes will be reflected not only in the main database, but also in those related to it.

Application structure

Convenience of work with the program is achieved due to the presence of elements that allow automate the process base creation. The following is a list of the main components of the software.

Components:

• table... The application component is designed to write and store data;
• request... The element is intended for obtaining information from one or several tables. Is a means of accessing related databases and third-party software;
• the form... The object is used to present the entered information in a more user-friendly form;
• report... Allows you to get the final result in the form of a finished document;
• macro... It is an element containing a consistent description for performing an action. Using it, you can set a command that will perform a specific task, for example, check if data has changed in one of the tables;
• module... A component that contains software written in the Visual Basic programming language. With its help, the editor significantly expands the functionality. This is achieved through the use of functions and procedures designed to respond to certain changes;
• access page... With its help, you can get access to 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 the convenience of the user, the ability to not only use imports, but also bind data with other programs and network resources.

User interface overview

Important! The interface will be reviewed using the example of Microsoft Access 2013. 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 the main functions and commands.

User interface:

• « Backstage"(Create). The interface element appears after the start of the program and allows the user to select a template for creating a database. In the process, to go to this tab, you must follow the path "File" and "New".
• « File". Allows you to save, open, print the finished document, as well as set Access options and set a suitable design theme.
  
  
• « Tape". It is the main element when working with the editor. It contains modules with tools for creating and editing databases. It also includes a Quick Access Toolbar that contains the most frequently 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 specify the parameters of the object.
  
• ". To display the component, you need to right-click on the object element. It contains commands that depend on the task being performed.
  
• ". Used to switch the document view mode, and to display the status of the current operation.

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

Working with databases in Access

Base creation

There are several ways to create a database: from scratch or using a ready-made template. In the first case, the user needs to independently create the elements and objects of the database, in the second version 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.

Net base

Let's start creating a new database with the subsequent filling it with information:

Create from template

Working with the template looks like this:

Base filling

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

First, it should be noted that using MS Access you can import... To do this, select the "External" tab, 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 reappear, click " Further»And set information about each object. We set a key that will help to quickly process information, and click " Further". After that, the object will appear in the main window and you can start creating it.

Process of creation:

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

Project No. 1 "Products":

Project No. 2 "Delivery":

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 greatly facilitates data processing.

We establish the relationship between projects:

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

Create requests

Classic sampling

The selection request allows you to select data in the database according to pre-created conditions. In our project, a selection will be created that allows you to find a product by its name. The work will be done in the Production database.

Entity creation

An entity creation request in Microsoft Access fetches from related 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:

Add, delete, edit

This type of query implies the creation and execution of an 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 are forms for:

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

We use "" to create an object:

After that, a new tab will appear in front of the user, where the table will be presented in the form of a form. As you can see, the perception of information has become much more convenient.

Consider the option of creating forms through the Designer:

Create a shape from scratch using "". With this option, you can customize the appearance, change the fill of the field, add multimedia files, etc.

You can also set 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:

Microsoft Access 2016 training in Moscow

Educational microsoft Access 2016 courses are intended for students who want to master the skills of building databases, learn how to create, edit, update databases.

Students of the course study the program from the very beginning: from building simple databases to complex ones, studying the subtleties and nuances of creating databases, master the possibilities of building queries and reports. The Microsoft Access curriculum is built on a close fusion of theory and practice. In the classroom, students constantly perform exercises to practice their skills in MS Access 2016, receive homework to review the material passed in the lesson.

In the course of training on the MS Access 2016 course, students are given a set of exercises necessary for working in the classroom. Students attend computer courses for PC users at a convenient time for themselves, training takes place in computer classes using modern computer technology.

Curriculum of the course

Module 1. Tables inMS Access 2016

• Structure of tables. Their creation.
• What are the types of fields.
• Working in tables with data.
• Hyperlinks.
• Input masks
• Condition on value.

Practical work

Module 2: Formatting Access Tables 2016

• Search, replacement, use of filters.
• Deleting and cutting data.
• Data formats.
• Import and attachment of tables, sheets of the MS Excel workbook, text files (Word).
• Working with a large table.
• Keys.

Practical work

Module 3. How queries workMSAccess 2016?

• Query constructor.
• Basic and composite conditions for selecting records.
• Create a calculated field in the Expression Builder.
• Types of requests and their elaboration

Practical work

Module 4. SQL - QueriesMS Access 2016

• SQL Expressions.
• The syntax for the Select statement.
• Statistical functions: Count, Avg, Last, Min, Max, First.
• Sum statistical function.
• Request to create, add, update and delete tables.

Practical work

Module 5. Multi-Tabular Databases

• How do I split the data into separate tables and create linking fields?
• Elimination of redundancy, data inconsistency and their dependencies.
• Subtables as a link element
• Organization of connections and preservation of data integrity.

Practical work

Module 6. Form as an Editing Object inAccess 2016

• Create a form using the "Form Wizard". Using AutoForms.
• Creation of forms of any complexity using the "Form Designer".
• Creation of main and subforms.
• Form binding types.

Practical work

Module 7. Reports and printing inMSAccess 2016

• Printing tables and forms. Saving the form as a report.
• Sorting and grouping of reports.
• Pagination. Expanded inscriptions.
• Print options.
• Export of reports.

Practical work

Final examination.

Getting Started in Microsoft Access 2010

Starting Access

To work with Access 2010, one of the desktop operating systems Windows XP / Vista / 7 and the Access DBMS must be installed on the user's local computer. In order to start working in the Access DBMS, you need to start it after loading the operating system. This can be done, for example, like this: at the bottom of the Windows XP desktop on the taskbar, click the Start button, in the menu that opens, select All programs. From the list of programs installed on this computer, select Microsoft Office | Microsoft Access 2010and start the DBMS. Access can be launched from the list of recently used programs, also provided on the Start menu.

To quickly launch Access, it is convenient to have a shortcut to this program on the Windows desktop. There are many ways to create a shortcut. For example, select the Microsoft Access program as you would when starting it. Click the right mouse button. In the context menu, select the Send command and in its menu, run the command Desktop (create shortcut)... The shortcut will be created and displayed on the desktop as shown in Fig. 1.7.

Figure: 1.7. Access 2010 shortcut

Access can now be launched by double-clicking on the shortcut. When Access 2010 starts, Backstage view appears, as shown in Figure 4. 1.8.

NOTE

You can start Microsoft Access by double-clicking the Access database file. This will immediately open the database.

From the Backstage view page, you can create a new database, open a database, create a database using one of the local templates, or view the latest content on the Office.com Web site. Thus, this page provides quick access to tools to get you started,

in including kits of professionally designed templates.

IN an item is selected on the left side of an open Backstage viewCreate (New). In this case, on the right side, it is enough to click the Create button to create a database file with the specified name and in the default folder. Obviously, both the name and location of the file can be changed here.

Figure: 1.8. Backstage view window

You can open an existing database by selecting the appropriate command on the left side of the view window. You can open one of the databases the user has worked with by selecting it from the list of recent databases for quick access. The number of documents in the list of recent files for quick access is set by the corresponding parameter on the Recent page (default 4). The number of documents in the list of recent files on the Recent page is set in the Access Options window on the

Client parameters(Client Settings) under Display (default 17).

Backstage view can be opened at any time by clicking on the colored File tab, which replaces the Office Button in previous versions. When the database is open, the view contains commands such as: Co-

store (Save) the database; Save database as(Save Database As) -

save a copy of the database in the current format or the format of another version of Access, or save the current database object as a new object, such as a table as a form; execute Print of the current database object; Per-

hide database(Close Database). In the Info section, you can Compress and

Introduction

Database management systems (DBMS) are perhaps the most common type of software. DBMS have more than thirty years of development history while maintaining continuity and stable traditions. The ideological value of a DBMS is explained by the fact that programs of this kind are based on the concept of a data model, that is, some abstraction of data presentation. In most cases, the data is assumed to be in the form of files of records. The structure of all records in files is the same, and the number of records in a file is variable. The data items that make up each record are called fields. Since all records have the same fields (with different values), it is convenient to give the fields unique names. Many practically important cases fit well into this data presentation. For example, in the HR department, information about employees is of this nature. Employees are hired and fired, but the form of the personnel record sheet remains unchanged for each employee. Inventories come and go, but the shape of the inventory card remains unchanged. The number of examples can be easily multiplied. It is clear that the DBMS is an adequate tool in all cases when the initial information can be presented in the form of a table of constant structure, but of an indefinite length, or in the form of a card index containing an indefinite number of cards of a constant structure.

All DBMSs support, in one form or another, four basic operations:

add one or more records to the database;

delete one or more records from the database;

find one or more records in the database that satisfy a given condition;

update the value of some fields in the database.

Most of the DBMS also support the mechanism of links between the various files included in the database. For example, a link can be established explicitly when the value of some fields is a link to another file, such DBMS are called network, or the link can be established implicitly, for example, by the coincidence of field values \u200b\u200bin different files. Such DBMS are called relational.

MS Access is a relational DBMS that reasonably balances all the tools and capabilities typical of modern DBMSs. The relational database makes it easy to find, analyze, maintain, and protect your data because it is stored in one place. Access translated from English means "access". MS Access is a functionally complete relational database management system. Besides, MS Access is one of the most powerful, flexible and easy-to-use DBMS. In it you can create most applications without writing a single line of program, but if you need to create something very complex, then MS Access provides a powerful programming language for this case - Visual Basic Application.

The popularity of Microsoft Access DBMS is due to the following reasons:

ease of study and understandability make Access one of the best systems for quickly creating database management applications;

The DBMS is fully Russified;

the ability to use OLE technology;

integration with Microsoft Office packages;

support for WWW ideology (only in Access 97);

visual technology allows you to constantly see the results of your actions and correct them; in addition, working with the form designer can greatly facilitate the further study of such programming systems as Visual Basic or Delphi;

the help system is widely and clearly presented;

the presence of a large set of "wizards" for the development of objects.

There are several ways to start Access:

launch using the main menu in WINDOWS 95/98;

launch using a shortcut on the desktop or MS Office panel;

opening any database automatically starts Access.

If you have questions about Microsoft Access, you should first try to find answers to them in the documentation or Help system.

In this work, we propose a set of tasks that will allow you to master the basic techniques of working with the Access DBMS. Hint cards can help when working with Access DBMS. They are intended for training directly in the process of working with Microsoft Access and after opening the database will constantly accompany any user actions.

Lesson 1

Database creation. Entering and formatting data

Download WINDOWS system. Download DBMS ACCESS. Pop-up hint cards will appear. Roll them up. If you need a hint, at any time you can call the cards in the Help menu or the corresponding button on the toolbar.

First you need to create a new database.

Let's perform the following sequence of actions: in the File menu, select the New command. File name: skaz.mdb. OK. The Database dialog box appears.

Carefully read the purpose of the buttons on the toolbar by slowly moving the mouse cursor over the buttons.

After that, create a table using the following sequence of actions: Table / Create / New Table.

The creation of the table, that is, the definition of the fields included in the table, is performed by filling in a special table:

Fill in such a table with the following data:

Field No. is optional, we enter it in order to define a key field, since any table must have a key.

Enter the information in the Table / "Character" / Open table and enter the data in the usual way, for example:

Use the mouse to highlight:

a) record 5,

b) record 3,

c) from the third to the seventh entry. Deselect the selection.

d) Select all entries. Deselect the selection.

e) Highlight the "Character" field.

f) Select the fields: "Profession", "Special signs" and "Hero" at the same time, deselect them.

g) Select all fields. This can be done with the mouse or in the Edit menu, select the Select All Records command.

Deselect the selection.

Highlight:

a) In the field "Special signs" mark the sixth entry.

b) In the "Character" field, select the fourth to sixth entries.

c) Without releasing the mouse button, mark the same entries in the "Special features" and "Hero" fields.

Deselect the selection.

Select the entire table.

Deselect the selection.

Change the width of each column so that the width of the columns is minimal, but all the text is visible.

This can be done using the mouse, expanding the columns or as follows.

Select the required column and press the right mouse button, in the context menu select the "Column width" command; in the window that opens, click the Fit to data width button.

Do the same work for all fields.

The line height can be changed in the same way using the mouse or on the Format menu using the Line height command. Moreover, it is enough to edit one line, the height of the remaining lines changes automatically.

Change the line height in any way and make it 30.

Change the table font to Arial Cyr, font size 14, bold.

You can change the font as follows: move the mouse pointer outside the table and press the left mouse button, select Font from the context menu or select the Font command from the Edit menu on the toolbar.

Change the text font to Times New Roman Cyr, font size 10.

Change the width of the margins.

a) Make the column "Character" 20 wide.

b) Column "Special signs" with a width of 25.

You can see that the text in these fields was printed on two lines.

Adjust the column widths so that the text fits completely.

Sort the table by the "Character" field in reverse alphabetical order.

It can be done like this. Select the Character field and click the Descending Sort button on the toolbar.

Return the table to its original state.

Save the "Character" table.

Close the "Character" table.

Session 2

Database editing

Open the Character table and add the following entries to the end of the table:

This can be done in three ways:

a) Move the cursor to the end of the table and enter new records.

b) On the toolbar, click the New entry button.

c) In the Records menu, select the Data Entry command.

Copy the first entry to the location of the sixth entry.

Delete the fifth entry.

Copy the first record to the end of the table.

Change Duremar's profession to a leech vendor.

This can be done as follows: mark the pharmacist entry with the mouse cursor, delete it into the clipboard and enter the leech seller from the keyboard. Or in the following way: open the Edit menu on the toolbar, select the Replace ... command. A replacement dialog box will appear on the screen. Enter the replacement format.