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 the Microsorf Access DBMS

• 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 in desktop PCs. This means that you can always seek help from a friend who has already used this database for a long time, for advice, and also work together with one database on the network.
• Virtually limitless data export capabilities: data from tables can be moved to Excel, Word, exported to XML, published to PDF, not to mention, without problems, to transfer 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.
• Wide possibilities of data import: if you have tabular data created with the help of the word processor MS Word or the 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, there is another significant plus: the 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 from the Microsoft website for free.

As you can see, the advantages of MS Access are obvious. But all the pluses can be covered by quite significant drawbacks. Consider them.

Disadvantages of Microsoft Access

• MS Access refers to file-server DBMS. This means that all data processing takes place 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 possible only 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 Windows operating systems. To try to use data created in this database in other operating systems (for example, LINUX), you will have to tinker a lot. It is easier to transfer data to another DBMS.

After we considered the minuses and pluses of MS Access, for the 2007 version we give some instructions on the capabilities of this DBMS.

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. We have 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, data entry into 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 will automatically be asked 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 are more comfortable switching to the "Designer" mode 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 at any time 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 the data. It should be remembered that you can make a choice only if several tables can be logically linked to each other using a 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 the fields from each table that you want to see as a result.
5. On the menu bar on the Design tab, click on the large exclamation point. Before you in the tabular form will appear the result of your sample.

This is the most common formula for creating queries. For more complex queries, read the online help or use special tools.

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

Form Wizard

On the example of Access 2007, let's look at how to use the form master:

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

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

Having examined these instructions, we can conclude that MS Access is a good opportunity for a small cost. A clear interface, prevalence, built-in helper constructors and wizards - all this makes it easy to design the database and work with it.