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. Consider this system in more detail.

What is a relational database?

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

Consider the advantages and disadvantages of this DBMS.

Advantages of the Microsorf Access DBMS

• Simple and intuitive interface. You can start working with this DBMS without any special skills. In addition, numerous help guides, online and offline courses come to the aid of a beginner.
• DBMS MS Access is well-known and very popular in desktop PCs. This means that you can always ask for help from a friend who has been using this DBMS for a long time, for advice, and also work together with one database over the network.
• Almost unlimited possibilities of exporting data: data from tables with the click of a single mouse button can be transferred to Excel, Word, exported to XML, published to PDF, not to mention the fact that you can easily transfer selected objects to another database.
• Low price. If you buy MS Access as part of the full Microsoft Office suite, then, in comparison with other paid DBMSs, the price will be very attractive.
• A wide selection of designers to build forms, reports and queries with which you can filter data and display them in a convenient way.
• Ample opportunities to import data: if you have tabular data created using the MS Word word processor or MS Excel table processor, you can easily transfer it to your database using the wizard. Import can also be performed 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.
• SQL editor.

For programmers, there is another significant plus: the Access JET 4 kernel is built into Microsoft operating systems, starting with Windows-98. This means that a developed application that uses a database created in Access can be easily transferred to any computer running Windows. It will work without requiring the installation of the DBMS itself. In extreme cases, you only need to install RUNTIME - a version that can be downloaded absolutely free from the Microsoft website.

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

Disadvantages of Microsoft Access DBMS

• MS Access refers to the file-server DBMS. This means that all data processing takes place directly on the client computer, the user. The server side only retrieves the data and transfers it on demand. This leads to the fact that large flows of data run through the local network, and with a large number of users working with the database, network congestion can occur. Classic file-server architecture always entails poor 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 database password and user password are used. It is not difficult for an experienced specialist to remove such protection.
• Multiuser mode is only possible on a peer-to-peer network or on a network with a file server.
• To develop a custom interface requires the involvement of programmers.
• The SQL query editor is primitive and inconvenient to use.
• This DBMS is not free.
• MS Access is sharpened for Windows operating systems. To try to use the data created in this DBMS in other OSs (for example, LINUX), you will have to tinker a lot. It’s easier to transfer data to another DBMS.

After we examined the pros and cons of MS Access, using the example of version 2007 we will give small instructions on the capabilities of this DBMS.

Create tables

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

1. After opening the database, go to the tab "Creation".
2. 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 the auto-increment field "Code".
4. You can start entering data in the second column. After entering the line, press the Enter key. The column is automatically renamed to "Field 1", the "Code" field will take the value of one, data entry in the next row will become available.
5. To rename a column, just double-click on it. It is advisable to specify column names without spaces!
6. The table builder in Access 2007 is very smart. Once you enter a value of the "Date" type in the field in the first row of the new table, you will automatically be offered to use the calendar in the next row of the same column.
7. To complete the creation of the table, you must save it by clicking on the corresponding icon or the CTRL + S key combination.

It is more convenient for many users to switch to the "Designer" mode for more visual work on the table fields, because in this mode you can set field types, restrictions on values. You can switch to this mode at any time when working with the table, just click on the corresponding button on the panel.

Create Queries

Let's consider how to create requests in MS Access. In version 2007, we create a simple query in design mode:

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

This is the most general query creation formula. For more complex queries, read the help for the program or use special tools.

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" through "Other Forms".
2. Select a table or query from the drop-down list for which you want to create a visual form.
3. Using the ">" and ">>" buttons, transfer the necessary columns from the "Available Fields" block to the "Selected Fields" block one at a time or all at once, respectively. Click on the “Next” button.
   
4. Select the appearance of the form using the switch.
5. Next, select a form style, and you can click the Finish button. Here is the data of your table or query in an interesting format.

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

After reviewing these instructions, we can conclude that MS Access is a good opportunity at a low cost. A clear interface, prevalence, built-in auxiliary constructors and wizards - all this makes it easy to design and work with the database.