The main purpose of Microsoft Access
The main purpose of Microsoft Access is to support the operation of relational databases. In this type of database, information characterizing a certain class of real objects is entered into special tables. The number of characteristics (attributes) that define the essence of objects of a given class corresponds to the number of table columns. Each object of this class corresponds to one row in the table. A table column in Access is called a field, and a row is called a record.
Microsoft Access Basic Objects
When Microsoft Access starts, a dialog box appears on the screen that allows the user to create a new database or a database based on a set of standard databases. The main objects of Microsoft Access are table, form, report, query, macro and module. Tables are designed to store data about the characteristics of objects in the subject area. Basic information processing operations in Microsoft Access are carried out using two-dimensional rectangular tables. A form is a way to display and manipulate data on the screen. It is created to view, enter and edit interrelated data in the database. The appearance of the form corresponds to a regular document. A query is a question that is formulated regarding the database. They are necessary for selecting information from a database based on a specific criterion or set of criteria. Using a query, you can add, delete, or update data in a table, and you can also create new tables based on existing tables. A report is a transformed representation of data that is output to a printer, screen, or file. Reports are designed to generate a source document, which is usually printed. A macro is necessary to combine a certain sequence of actions during the execution of a multi-stage information processing procedure into a single whole. The module contains programs written in one of the algorithmic languages for implementing non-standard information processing procedures.
Tables in Microsoft Access
Tables that are the basis of the database Access data, have the following properties: each table element (cell) is a data element; each table column is assigned a unique name; Each column in the table is homogeneous, that is, it consists of elements of the same type. This can be text, dates, numbers, graphic objects, logical values, etc.; the table cannot contain identical rows; rows must have the same number of elements (fields), which are heterogeneous and interrelated; columns and rows are placed in any order.
The first DBMS
In the very first DBMSs, as a rule, a separate file was created for each table. For interconnected work between tables, we used special utilities. Microsoft Access has simple ways for a bunch of tables stored in one file. Joint storage of different tables in one file has the following advantages: the ability to place data in tables according to the content of the data, that is, information about the features of objects of the same class is in one table; saving information in separate tables (modules) makes it possible to change and update information in each module separately, in accordance with changes in subject area objects; Correcting data in one cell leads to automatic correction in other cells.
Database (DB) is an integrated set of interrelated data. Typically a database is created for one specific subject area, organization or application.
Microsoft Office Access or just Microsoft Access-- relational DBMS from Microsoft Corporation. Has a wide range of features including linked queries, communication with external tables and databases. Thanks to the built-in VBA language, you can write applications that work with databases in Access itself.
Main components of MS Access:
· table builder;
· screen form builder;
· SQL query builder ( SQL language in MS Access does not comply with the ANSI standard);
· builder of printed reports.
They can call scripts on VBA language, therefore MS Access allows you to develop applications and databases practically from scratch or write a shell for an external database.
Access versions
· 1993 Access 2.0 for Windows (Office 4.3)
· 1995 Access 7 for Windows 95 (Office 95)
· 1997 Access 97 (Office 97)
· 1999 Access 2000 (Office 2000)
· 2001 Access 2002 (Office XP)
· 2003 Access 2003
· 2007 Microsoft Office Access 2007
· 2010 Microsoft Office Access 2010
Starting the program
To launch MS Access you can use the menu Start Windows system and use the mouse to select the command Programs and then Microsoft Access, in case of absence Microsoft Access in the menu Programs select Microsoft Office and in its submenu find Microsoft Access.
After launching MS Access, the Access dialog box appears on the screen with the name MICROSOFT ACCESS in the title bar. In this window, you must select one of three options: opening an existing database, creating a new (empty) database, and creating a database using the Wizard.
To create an empty database, select the option New database and in the dialog box that opens, NEW DATABASE FILE:
1. Select from the drop-down list Folder the folder in which you want to save the created database, and in the "File name" field, enter the name of the database.
2. Press the button Create.
MS Access main window
After launching MS Access, the main Access window appears on the screen with the name MICROSOFT ACCESS in the title bar. If an existing database is opened, its name is placed in the title bar of the database window. This window can be expanded to size full screen, reduced, minimized to an icon, or closed.
The main MS Access window contains a menu bar, a toolbar, a status bar, and a database window. Closing the database window closes the database itself. Via the menu File you can open an existing database or create a new database.
Menu bar. The menu bar at the top of the screen contains the menu for the current window. You can set a command either using the mouse (by selecting the name of the corresponding menu and the line with the desired command in it) or from the keyboard (by pressing the ALT key and the key of the letter underlined in the desired menu item).
Toolbar. Below the menu bar is a toolbar, the composition of which buttons depends on the purpose of a particular toolbar. Each operating mode has its own toolbar. After you launch Microsoft Access, the Database Toolbar appears. To select another toolbar required for work, use the command Toolbars ( menu View), or place the mouse pointer in the toolbar area and click right button mouse select command Settings.
Microsoft Office is an editor that allows you to create and edit office documents. 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 instructions 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. At the same time 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 the Microsoft Office Excel editors is similar to the Access program, 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.
Principle Excel work is based on organizing data inside 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 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 an overabundance of data when you need to change the desired parameter, not by entering 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 box" 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 “ Next» and set information about each object. We set a key that will help quickly process information and click “ Next" After this, the object will appear in the main window and you can begin creating it.
Creation process:
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, conventional units are used to express its comparative value. 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 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:
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 tab, 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:
Let's take a closer look at the main functions of MS Access to have a clearer idea of its capabilities.
In Access, a database refers to a file that contains a collection of information. A database in Access 2007 can contain the following types of objects (see Fig. 1): table, query, form, report, page, macro, module.
Figure 1. Database Objects Window
Access can only work with one database at a time. But one Access database can include many tables, forms, queries, reports, macros and modules, which are stored in one file with the mdb extension.
Table is an object corresponding to the concept of “table” in the theory of relational databases. For each table in Access you can define primary key and one or more indexes to increase the speed of data access.
Access allows you to create a table structure in three modes—design mode, using a wizard, and by entering data. The difference assumes the use of these tools by users with different levels preparation, different purposes and prospects for using data. Naturally, it is possible to view, edit, delete and add records, search, replace, sort data, and change the table view. Relationships between tables are determined by a special tool called “Data Schema”.
Figure 2. Data diagram
This is a convenient graphical tool that allows you to create relationships between certain table fields, define various types of relationships, and set referential integrity constraints. In this case, the changes are immediately applied in the database (of course, if the contained data satisfies all the conditions). The resulting diagram of tables and relationships can be printed, which is undoubtedly convenient for the developer.
Request– an object containing the text of an SQL query that has a unique name in a specific database. You can create a query using the wizard and in design mode. In the first case, the user is interactively asked to select the table name and fields to select. In the second case, you can select several tables or queries, link them graphically and define selection fields. You can also set additional conditions for each field and sorting options.
Figure 3. Query Builder
Another way to create a query that is built into the constructor is to write the query manually in SQL. However text editor, designed for this purpose, has clearly poor capabilities in terms of convenience and clarity of code design. Apparently this method was not positioned by the developers as the main one.
Form is a special container object for other interface components, such as data entry and display fields, buttons, etc. On the form, the developer places components for entering, editing, viewing and grouping data, depending on the specifics of the application. You can also create a form in two ways: in design mode and using the wizard. In the first case, the developer has a set of components (if necessary, you can register your own ActiveX), which you can freely place on the form and set their parameters. In Wizard mode, the user simply selects a table, fields, and design style, and the form is generated automatically.
Report– an object intended for creating a document that can subsequently be printed or included in a document in another application. This document contains the results of a selection from a database in the form of structured information (for example, a table or list). MS Access has rich capabilities for designing and formatting reports. The report also has the same two creation modes. In the wizard mode, the user has the opportunity to set many parameters that almost completely determine the desired appearance report. To create non-standard reports, it is better to use the designer.
Pages– a means of publishing data in local network or Internet. The created page is designed similarly to a form (with some differences in the components used); when working with it, you can not only view, but also change the data in the database. Once the page is saved as an object in the database, it can be exported as an HTML file and used to access data through an Internet browser.
Macro– this is an object that is a sequence of macro commands for automating the most frequently performed actions when working with the database. Macro commands are selected from a given list, and the parameters are set by the developer. You can execute a macro by clicking a button and programmatically in code.
A module is a container of program code in VBA. To edit and view them, the Visual Basic Editor shell is used. All program code applications are contained in a set of modules. Here it has the same semantic meaning as in any programming language.
This full list objects that can be stored in an MS Access database, which, however, is similar in other desktop DBMSs of a similar class, for example Paradox.
Access provides the following functionality.
Data entry. Data can be entered in the following ways: manually directly into the table (this also includes pasting the contents of the clipboard); manually in form fields; direct import of data from other sources (Access databases, text files, DBF format, spreadsheets, ODBC data sources); a software method that can combine any tools that can be implemented in VBA. Last method It has the greatest flexibility and presents almost unlimited possibilities, but it is the most difficult to implement and requires a certain level of programming knowledge.
2. Changing data. Editing is possible in the following ways: manually directly in the table; in form fields; in a browser window in which a web page from the database is loaded; by software method.
3. Data output. Here Access provides the following capabilities: displaying on the monitor screen in tabular form, form fields or reports; export to other data formats (the same as when importing); printing, mainly in the form of reports; outputting data to an Internet browser using a Page object within a specific network; software export and output of information.
4. Interaction with other sources and consumers of information. In this regard, Access can act as an automation server or client. Connections with other products are especially transparent Microsoft package Office. For example you might have a document Microsoft Word, which will contain fields from the Access database, changes in data in the database are automatically displayed in the document. This allows you to create flexible solutions that integrate data across office tools. 5. Use of MS Access database by other applications. This option uses the MDB file as the data store. The program that accesses the data can be written in any high-level language. In this case, tables and queries are used. The database core monitors the maintenance of referential integrity and data relevance. Interaction occurs through the Microsoft Jet Engine ODBC driver.
6. Application creation tools. Creating applications in Access is much like all other Microsoft Office automation tools. Interpreted language is used here Visual Basic for Applications, which leads, as when using any interpreted language, to a certain increase in CPU time and a decrease in the speed of programs and data processing. For successful development, you need to know the object model of Access itself and the features of its use.
7. Data management in multi-user mode. When working with Databases in multi-user mode, situations arise when it is necessary to limit the number of users accessing the data. This is done in order to prevent simultaneous updating of the same record, during a global update of data or during maintenance of the Database itself.
The Access database engine provides three levels of locking:
Database lock. At this locking level, only one user can access the database. This locking level is used for global changes or updates to data, or when maintenance Databases - compression.
Locking the table. At this locking level, only one user can access the table. This level of locking is used in cases where it is necessary to process several table records at once.
Page lock. At this level, only one user can access a blocked page. This is the lowest level of blocking. The Microsoft Jet processor automatically sets a page lock and cannot be controlled by your program. A data page can contain several records, its size is 26 kb. Blocking a page means blocking all entries on that page. If the record length is 512 bytes, then 4 records will be blocked, and if it is 50 bytes, then 40 records will be blocked. The exact number of records cannot be determined or set in advance, because the table may contain deleted records (which are only deleted during compaction).
Table-level locking has two modes: pessimistic and optimistic. The default is pessimistic locking.
8. Administration. The advantage of the exclusive operating mode has actually led to the degeneration of database administration functions and, in connection with this, to the absence of administration tools in the usual sense of the word in MS Access. The available tools allow you to do the following: it is possible to divide the Microsoft Access database into two files, one of which contains tables, and the other contains queries, forms, reports, macros, modules and shortcuts to data access pages. This allows users to access a common data source while creating their own forms, reports and other objects, and reduces network traffic; it is possible to link tables from other databases Microsoft data Access or other sources. For example, you might want to use a table from another Microsoft Access database that you share online. This is especially useful when you need to store all tables in one database on network server by storing forms, reports and other objects in a separate database, copies of which are available to all users of the common database; Microsoft Access replication tools, available in the database (.mdb) and in the project (.adp), allow you to create replicas and synchronize them on demand when working in Microsoft Access; means of protection and access control. The simplest method of protection is to set a password to open the database. The database may be encrypted. When you encrypt a database, the database file is compressed and cannot be read by utilities or text editors. Decrypting a database reverses the results of the encryption operation. The most flexible and common method of protecting a database is called user-level protection. This security method is similar to those used in most network systems. However, as already noted, guessing the administrator password is not difficult for a hacking specialist; it is possible to convert the database to the format of an earlier version of MS Access to ensure compatibility in some cases; and finally, a database compression tool that erases information about deleted rows and reduces the size of the MDB file on disk. This results in better performance and in some cases can restore the database (for example, after an unexpected power outage).
It is also worth mentioning that Access comes with standard database templates to automate the most common tasks.
Figure 4. Database templates
The user, in the interactive Wizard mode, selects the data that he wishes to have in his database in accordance with the needs of the enterprise, and MS Access automatically creates all the necessary objects. In the future, this database can be refined and expanded.
Multi-valued fields for complex data
You can create a field that contains multiple values, also known as complex data. Let's say you need to assign a task to one of your employees or contractors, but you would like to assign that task to multiple people. In most database management systems and earlier versions of Access, this would require a many-to-many relationship to avoid errors.
In Office Access 2007, the hardest part of the work is done automatically when you select a field to enter multiple values. Multi-valued fields are especially useful when you use Office Access 2007 to work with a SharePoint list that contains one of the types of multi-valued fields used in Windows SharePoint Services. Office Access 2007 is compatible with these data types.
New type Attachment data allows you to store all types of documents and binary files in a database without unnecessarily increasing the size of the database. Office Access 2007 automatically compresses attachments whenever possible to save as much free space as possible. You need to attach a Microsoft Office Word 2007 document to an entry or store several digital photos in a database. Using attachments makes these tasks much easier. You can even add multiple attachments to a single post.
MEMO fields now store rich text and support a revision history.
Office Access 2007 now supports rich text, which you can use in your posts alongside plain text. Text can be formatted with a variety of options (such as bold and italics, fonts, colors, and other common formatting options) and stored in a database. Rich text is stored in the MEMO field in an HTML-based format that is compatible with the Rich Text data type in Windows SharePoint Services. Set the TextFormat property to either RichText or PlainText, and the data in text fields and in table mode will be formatted properly.
MEMO fields are convenient to use for storing large amounts of data. With Office Access 2007, you can set the Append Only property to keep a record of all changes in the MEMO field. You can then view a log of these changes. This feature also supports the tracking feature in Windows SharePoint Services. Therefore, you can also use Access to view a history of the contents of a SharePoint list.
Access is one of the most popular desktop database management systems (DBMS) that can work with text files, with spreadsheets and databases of the most popular formats.
A database stores a collection of data and objects related to a specific task. It helps us organize information according to various characteristics and allows us to quickly make a selection with an arbitrary combination of characteristics. Any information stored on a computer disk can be used as data: combinations of characters, words, files, pictures, objects, etc. Data entry and storage methods determine the structure of the database.
Access allows you to create relational databases that store data in table form. It can be used to analyze data, to create dynamic web pages that automatically reflect changes in data. It can be used to create database applications, such as client/server applications.
- Access can work as:
- stand-alone DBMS for desktop systems;
- DBMS for file server;
- as an interface client for communication with enterprise-scale server DBMSs, for example, with Microsoft SQL Server.
Access 2003 is a powerful platform for creating desktop and client/server applications at the departmental or enterprise level.
Access 2003 allows you to convert Microsoft Access databases to Microsoft SQL Server format and provides two-way XML support. To integrate Access tools with MS SQL databases, high-speed database access tools based on the OLE-DB interface are used.
File extensions used in Access
Access stores all data in one file, even though it may be distributed across different tables. Tables with data, their indexes, forms, reports are stored in files with the extension .mdb (Microsoft Access database). The closed file format with the .mde extension allows you not to include source code VBA to Access Redistributable Applications. adp (Microsoft Access. project) files provide connections to integrated repositories SQL data Server 2000 through an OLE DB connection, which allows you to create full-fledged applications that have a client/server architecture.
By default, Access uses files with the extension .mdb. This format allows developers to save files as *.mde or *.ade. This compiles any Visual Basic code and excludes the source code from both the Access database (.MDB) file and the Access project (.ADP) file, providing greater security.
