Access 2010 is a database creation and management program. To understand Access, you must first understand databases.

In this article, you will learn about databases and how they are used. You will learn the differences between managing data in Access and Microsoft Excel.

What is a database?

A database is a collection of data that is stored in computer system. Databases allow their users to quickly and easily enter, access and analyze their data. They are such a useful tool that you see them all the time. Have you ever waited while a doctor's receptionist entered your personal information into a computer or watched a store employee use a computer to see if an item was in stock? Then you saw the database in action.

The easiest way to understand what a database is is to think of it as a collection of lists. Think about one of the databases mentioned above: a patient database in a doctor's office. What lists are contained in such a database? Well, for starters, there's a list of patient names. Then there is a list of past appointments, a list with medical history for each patient, a list of contact information, etc.

This applies to all databases - from the simplest to the most complex. For example, if you want to bake cookies, you can keep a database containing recipes you know how to make and friends you give those recipes to. This is one of the simplest databases. It contains two lists: a list of your friends and a list of cookie baking recipes.

However, if you were a professional baker, you would have many more lists to keep track of: a list of clients, a list of products sold, a list of prices, a list of orders... the list goes on. The more lists you add, the more complex the database will be.

Lists in Access are a little more complex than those you write on paper. Access stores its lists of data in tables, allowing you to store even more detailed information. In the table below, the list of people in the amateur baker database has been expanded to include other relevant information about friends.

If you are familiar with other programs in the package Microsoft Office, it may remind you of Excel, which allows you to organize data in a similar way. In fact, you can create a similar table in Excel.

Why use a database?

If a database is essentially a collection of lists stored in tables, and you can create tables in Excel, why do you need a real database? While Excel is great at storing and organizing numbers, Access is much more efficient at handling non-numeric data such as names and descriptions. Non-numeric data plays a significant role in almost any database, and it is important to be able to sort and analyze it.

However, what databases really do, over and above any other way of storing data, is connectivity. We call a database like the ones you'll work with in Access a relational database. A relational database can understand how lists and the objects within them are related to each other. To explore this idea, let's go back to a simple database with two lists: your friends' names and cookie recipes you know how to make. You decide to create a third list to keep track of the batches of cookies you make and who they are for. Since you're just making them, you know the recipe, and you're only passing them along to your friends, this new list will get all of its information from the lists you made previously.

See how the third list uses words that appeared in the first two lists? The database is able to understand that Ivan Ivanovich and Sour Cream Cookies in the list are the same things as Ivan Ivanovich and Sour Cream Cookies in the first two lists. This relationship seems obvious, and the person will immediately understand it. However, an Excel workbook cannot.

Difference between Access and Excel

Excel would treat all of these things as separate and unrelated pieces of information. In Excel, you will need to enter every single information about the person or type of cookie every time you mention it because this database will not be relative like an Access database. Simply put, relational databases can recognize what a person can do: if the same words appear in multiple lists, they refer to the same thing.

The fact that relational databases can process information in this way allows you to enter, search, and analyze data in more than one table at a time. All of these things would be difficult to do in Excel, but in Access, even complex tasks can be simplified and made quite user-friendly.

To make informed and effective decisions in production activities, in economic management and in politics, a modern specialist must be able to use computers and communications to receive, accumulate, store and process data, presenting the result in the form of visual documents. IN modern society information Technology They are developing very rapidly, they penetrate into all spheres of human activity.

In different areas of the economy, it is often necessary to work with data from different sources, each of which is associated with a specific type of activity. Coordinating all this data requires specific knowledge and organizational skills.

Microsoft Corporation's product Access combines information from different sources into one relational database. The forms, queries, and reports it creates allow you to quickly and efficiently update data, get answers to questions, search for needed data, analyze data, and print reports, charts, and mailing labels.

The purpose of this course work is to consider the design in theory and creation in practice of a database in a Microsoft product for database management " Microsoft Access».

Access is a set of end-user tools for managing databases. It includes designers of tables, forms, queries and reports. This system can also be considered as an application development environment. By using macros or modules to automate tasks, you can create user-centric applications that are as powerful as applications written directly in programming languages. They will include buttons, menus, and dialog boxes. By programming in VBA, you can create programs as powerful as Access itself.

Create applications without coding using Access macros. Spreadsheet and database users should be familiar with many of the key concepts used in Access. Before you start working with any software product, it is important to understand its capabilities and the types of problems it is designed to solve. Microsoft Access (hereinafter simply Access) is a multifaceted product whose use is limited only by the user's imagination.

Access fully implements relational database management. The system maintains primary and foreign keys and enforces data integrity at the kernel level (which prevents inconsistent update or delete operations). In addition, tables in Access are equipped with data validation tools that prevent incorrect input regardless of how it is entered, and each table field has its own format and standard descriptions, which greatly simplifies data entry. Access supports all the required field types, including text, numeric, counter, currency, date/time, MEMO, Boolean, hyperlink, and OLE object fields. If special processing does not result in any values ​​in the fields, the system provides full support for empty values.

Basic concepts about MS Access databases

1.1 Brief description MSAccess

Microsoft Access is a desktop DBMS (database management system) of a relational type. The advantage of Access is that it is very simple GUI, which allows you not only to create your own database, but also to develop applications using built-in tools.

Unlike other desktop DBMSs, Access stores all data in one file, although it distributes it across different tables, as befits a relational DBMS. This data includes not only information in tables, but also other database objects, which will be described below.

To perform almost all basic operations, Access offers a large number of Wizards, which do the main work for the user when working with data and developing applications, help avoid routine actions and make the work easier for an inexperienced user in programming.

Features of MS Access that differ from the idea of ​​an “ideal” relational DBMS.

Creating a multi-user Access database and obtaining simultaneous access of several users to a common database is possible on a local peer-to-peer network or on a network with a file server. The network provides hardware and software support exchanging data between computers. Access monitors access control different users to the database and ensures data protection. When working simultaneously. Since Access is not a server DBMS client, its ability to provide multi-user work is somewhat limited. Typically, to access data over a network from several workstations, the Access database file (with *.mdb extension) is uploaded to file server. In this case, data processing is carried out mainly on the client - where the application is running, due to the principles of organizing file DBMSs. This factor limits the use of Access to support the work of many users (more than 15-20) and with a large amount of data in tables, since the network load increases many times over.

In terms of maintaining data integrity, Access is only suitable for low to medium complexity database models. It lacks tools such as triggers and stored procedures, which forces developers to entrust the maintenance of the database business logic to the client program.

With regard to information security and access control, Access does not have reliable standard means. IN standard methods protection includes protection using a database password and protection using a user password. Removing such protection is not difficult for a specialist.

However, despite the known disadvantages, MSAccess has many advantages compared to systems of a similar class.

First of all, we can note its prevalence, which is due to the fact that Access is a Microsoft product, software and the operating systems used by the majority of users personal computers. MSAccess is fully compatible with operating system Windows, constantly updated by the manufacturer, supports many languages.

Overall, MSAccess provides a lot of functionality at a relatively low cost. It is also necessary to note the focus on users with different professional backgrounds, which is reflected in the presence of a large number of auxiliary tools (Masters, as already noted), a developed help system and a clear interface. These tools make it easier to design, create a database, and retrieve data from it.

MSAccess provides the non-programming user with a variety of interactive tools that allow him to create applications without having to develop queries in SQL or program macros or modules in VBA.

Access has extensive capabilities for importing/exporting data into various formats, from Excel tables and text files, to almost any server DBMS via the ODBC mechanism.

Another important advantage of MSAccess is its advanced built-in application development tools. Most applications distributed to users contain some amount of VBA (Visual Basic for Applications) code. Since VBA is the only tool for performing many common tasks in Access (working with variables, building SQL commands while the program is running, error handling, using Windows API, etc.), to create more or less complex applications you need knowledge of it and knowledge of the MSAccess object model.

One of the programming tools in Access is the macro language. Programs created in this language are called macros and allow you to easily link individual actions implemented using forms, queries, and reports. Macros are controlled by events that are caused by user actions when interacting with data through forms or by system events.

It turns out that Access, having all the features of a DBMS, also provides additional features. It is not only a flexible and easy-to-use DBMS, but also a system for developing database applications.

1.2 Databases and database management systems

A database is an organized structure designed to store information. Modern databases store not only data, but also information.

This statement is easy to explain if, for example, we consider a library database. It contains all the necessary information about authors, books, readers, etc. Both library workers and readers who need to find any publication can have access to this database. But among them there is hardly a person who has access to the entire database and at the same time is able to single-handedly make arbitrary changes to it. In addition to data, the database contains methods and tools that allow each employee to operate only with the data that is within their competence. As a result of the interaction of the data contained in the database with the methods available to specific employees, information is generated that they consume and on the basis of which, within their own competence, they enter and edit data.

Microsoft Access is a relational database management system used to create fully-fledged deployed client-server applications using the “DB-client” link. A simple and logical graphical shell allows you to generate primary and secondary keys, indexes, relationships between database objects, as well as normalize the relationships between discrete tables that make up the database structure to the required normal form. Access provides technological tools for exchanging data between other OLEDB and ODBC sources, including Excel tables; text files stored in CSV format; XML objects, as well as SharePoint stores, PDF or XPS containers, and Outlook folders.

Extended functionality of the Access DBMS

Along with other deployed solutions for interacting with database objects, Access provides the developer with the following set of technical capabilities and options:

• an abundance of formats for presenting and storing data in tables. Among the main categories available are text, numeric, currency, logical types, hyperlinks, date and time, logical structure, as well as a number of other auxiliary specifications
• quick switching between table mode and designer mode, allowing you to create the structure of the table and specify the formats of its individual cells
• creating data macros to automate the most common operations and sequences of actions applicable when creating database content. All macros can be generated either based on mouse clicks in the built-in macro editor, or contain elements using the Visual Basic language. As in other Microsoft Office applications, Access macros can be called by pressing a hotkey combination specified in the settings
• compression of the database and subsequent restoration of its contents from backup copy. The database archive can be stored on a secure remote server, in the cloud or on a local disk drive
• integrated report designer for displaying data from the database on paper printed forms and forms. All reports can be configured and detailed to obtain an accurate selection of information from the database. Also in the Access interface, it is possible to generate a report structure divided into sections and blocks, either manually or using a special wizard. Moreover, sorting and filtering of the displayed information is presented both at the stage of generating the report and later, when the final type of the report has already been thought out and finalized
• a nested information assistant that provides detailed information about a searched option, main menu category, module, or Access icon. The info assistant is closely integrated into the application shell, and in the latest revisions the DBMS uses developments in the field artificial intelligence And voice assistant Cortana.

On our resource you can download the complete Russified edition of Access for any generation Windows systems. Each version of the utility available for download is accompanied by system requirements, corresponding to the computer model you are using. If your device is old, it is worth going with an earlier release of the product.

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.

Application Microsoft package Office – Access – 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”. When creating a new database, you will be presented with a choice blank page, which 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:

1. 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.
2. The second way to enter information is through forms; they are somewhat similar to tables, however, they provide a more visual display of data.
3. 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.
4. 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 the two tables open, drag the field you want to link into the specially prepared foreign key field. The “Edit Links” window will appear, in which you will see separately 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:

1. Selective queries, thanks to which the program retrieves certain information and makes calculations on it.
2. 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:

1. Report - an auto-report will be created using all the information provided in the table, however, the data will not be grouped.
2. A blank report is an unfilled form for which you can select data yourself from the required fields.
3. 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, filling them with the 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.

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

© Khabarovsk State Academy of Economics and Law, 2011

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 summary data or search for specific information in the mass. 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 part of Microsoft Office Professional 2007, which ensures its connection with other office applications (Word test editor, Excel spreadsheet program). 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.

Foreign 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.