MS Access. MS Access databases. MS Access 2007

MS Access is a relational client-server database management system (DBMS) from Microsoft. Relational means that it is based on tables. Let us consider this system in more detail.

What is a relational database?

A relational database consists of a set of tables, between which relationships are built, or links. DBMS is a shell that allows you to create and use MS Access databases.

Consider the advantages and disadvantages of this database.

Advantages of Microsoft Access

• Simple and intuitive interface. You can start working with this database without having any special skills. In addition, many help manuals, online and offline courses come to the aid of the beginner.
• The MS Access database is well-known and very popular indesktop PCs. This means that you can always seek help from a friend who has used the database for a long time already, for advice, and also work together with one database on the network.
• Virtually unlimited export opportunitiesdata: data from tables with a click of a single mouse button can be transferred to Excel, Word, exported to XML, published in PDF, not to mention, without problems, to transfer the selected objects to another database.
• Low price. If you buy MS Access as part of a full package of Microsoft Office, then, in comparison with other paid DBMS, the price will be very tempting.
• A wide selection of designers for building forms, reports and queries, through which you can filter the data and display them in a convenient form.
• Широкие возможности импорта данных:if you have tabular data created with the help of a word processor MS Word or a table processor MS Excel, you can easily transfer them to your database with the help of the wizard. In addition, you can import from a simple text document, from an XML document, as well as from database files created in other DBMSs (such as dBASE, PARADOX).
• Ability to create a password on your database.
• Built-in high-level VBA language.
• Ability to record macros.
• The SQL editor.

For programmers it is possible to allocate one moreA significant plus: Access JET 4 core is built into Microsoft operating systems, starting with Windows-98. This means that the developed application that uses the database created in Access can be transferred to any Windows computer without problems. It will work without requiring the installation of the DBMS itself. In a pinch, you only need to install RUNTIME, a version that you can download for free from the Microsoft website.

As you can see, the advantages of MS Access are obvious. But all the pluses can overlap with quite significant disadvantages. Consider them.

Disadvantages of Microsoft Access

• MS Access refers to file-server DBMS.This means that all data processing occurs directly on the client's computer, the user. The server part only retrieves the data and transmits it on demand. This results in large data streams running on the local network, and with a large number of users working with the database, network congestion may occur. A classic file-server architecture always entails low performance with a large number of users. In MS Access 2010, this issue is partially resolved.
• Lack of reliable data protection tools. In fact, only the password on the database and the user's password are used. For an experienced specialist, it is not difficult to remove such protection.
• Multi-user mode is only possible in a peer-to-peer network or in a network with a file server.
• To develop a non-standard interface, it is required to attract programmers.
• The SQL query editor is primitive, it's inconvenient to use.
• This DBMS is not free.
• MS Access is sharpened for operating systemsWindows. To try to use the data created in this DBMS in other operating systems (for example, LINUX), it is necessary to tinker. It's easier to transfer data to another DBMS.

After we considered the minuses and pluses of MS Access, we will give a small instruction on the capabilities of this DBMS on the example of the 2007 version.

Creating tables

There is nothing easier than creating a new table using MS Access 2007:

1. After opening the database, go to the "Create" tab.
2. We click on the "Table" icon, it is the first on the left.
3. Before us is a visual representation of the table with the standard name "Table 1" and an autoincremental "Code" field.
4. You can start entering data into the second column.After entering the line, you must press the Enter key. The column will automatically be renamed to "Field 1", the "Code" field will take the value of one, the data entry in the next line will become available.
5. To rename a column, just click on it with the mouse two times. It is advisable to specify column names without spaces!
6. The database builder in Access 2007 is very smart.If you enter the date value in the first line of the new table, as in the next row of the same column, you are automatically prompted to use the calendar.
7. To complete the creation of the table, you must save it by clicking on the corresponding icon or by pressing CTRL + S.

Many users find it more convenient to switch to"Designer" for more visual work on the fields of the table, because in this mode you can specify the types of fields, restrictions on values. In this mode, you can go to any moment of work with the table, just click on the corresponding button on the panel.

Creating queries

Consider how to create queries in MS Access. Let's create in 2007 a simple query in the design mode:

1. In the "Menu" panel, on the "Create" tab, click the "Query Designer" button.
2. You will immediately see a window for selecting tables.Click on each table from which you want to select data. It should be remembered that the choice you can make only if several tables can be logically linked with each other using any field.
3. If you have selected several tables, you need to build a link between them by dragging a line from one field of one table to the associated field of the other.
4. Now double click on the fields from each table that you want to see in the result.
5. On the menu bar on the Designer tab, click on the large exclamation mark. You will see the result of your sample in tabular form.

This is the most common query creation formula. For more complex queries, read the program's help or use special manuals.

Sometimes even a novice user wants to present their data in a more interesting format than a simple table. Then the MS Access tool "Forms" will come to his aid.

Form Wizard

Using Access 2007 as an example, consider how to use the form wizard:

1. On the “Create” tab in the “Forms” subsection, go to the “Form Wizard” via “Other Forms”.
2. Choose from the drop-down list the table or query for which you want to form a visual form.
3. Using the “>” and “>>” buttons, transfer the required columns from the “Available fields” block to the “Selected fields” block one by one or all at once, respectively. Click the Next button.
4. Select the appearance of the form, using the switch.
5. Next, select the form style, and you can click "Finish". Before you the data of your table or query in an interesting format.

To create more complex shapes, you must use the appropriate constructor.

After reviewing these instructions, you can concludethat MS Access is not a bad opportunity for a small price. Clear interface, prevalence, built-in auxiliary designers and wizards - all this facilitates database design and work with it.