Features of designing in Access

One of the reasons why Access has won such greater popularity all over the world as means of development of appendices of databases, speed from which similar appendices in comparison with other means are developed is. And, it is recognized also by the programmers, engaged as development of databases by means of other packages. In this clause I shall tell about some features Access which promote such "acceleration".

Forms and reports

As is known, the basic part of the appendix of base consists of dialogue windows, or is simple forms. Usually each form has a source of data – the table or inquiry. Often beginning(starting) developers Access do not reflect at all on it, and here to the programmers using other means of development, not focused specially on databases, this well-known. They should create connection to a source of a database (usually through ADO), to cling this source to form, then to cling to everyone controls a field of the table corresponding it. In Access all this is made by default, as allows to accelerate designing of appendices considerably.

Even greater speed can achieve if to use patterns – preparations of forms by default. Really, if you do not like a standard kind of the form and controls, offered Access by default, and All of you time change height of fields, style of registration, fonts, etc. it is possible to make it once, to keep preparation of the form and to declare it as a pattern. Now at creation of the new form in your base parameters controls in it will be established by default.

So, we come on forms, we press « to create in a mode of the designer ». We cause in the designer the panel of properties of the form (double click under the form or we press button " Property "). We develop the panel of elements if it) is not present we press the button « the Panel of elements » - that, that with "hammers"). Now on the panel of elements we choose an element, for example "Inscription" and we set parameters of an element (in the form of adjustments) – a font, height of a font, alignment, etc. the Same makes with other elements. As a result we receive the empty form in which the user parameters control are set. To be convinced of it, we shower any control on the form, and we look, what parameters at it – apparently, what were set in adjustments. Now, that these parameters control have been set for all future forms, and not just for this, it is necessary to specify the created form as a pattern.

First we shall keep it, for example under a name «Form template». Then we press Service – Parameters – Forms and reports and « the Pattern of the form » a name of our pattern – «Form template» is specified in a floor. In the same way it is possible to create a pattern of adjustments of the report, to keep it and to specify in a floor « the Pattern of the report ».

These adjustments are kept only for the current project of a database. In the new project again will appear standard – type "Usual". To apply the user pattern in the new project, we import it to the project and we shall specify its name.

At creation of forms, often it is necessary to level elements. That business has gone more quickly, it is possible to level all of them at once: we allocate group of elements (a window or keeping Shift), then the right button – and we choose style of alignment: by a left edge, on right, etc. Such way is convenient, when it is necessary to shift group control which are scattered under the form – one is shifted « as it is necessary », and the others is leveled on it.

Querys

In Access there is an excellent master of creation of inquiries. Personally most of all it is pleasant to me, that the inquiry can be edited in a graphic mode that is much more evident, besides the probability of a mistake in syntax SQl is shown practically to a minimum – in fact the master writes. As a rule, in 90 % of its cases suffices, hands it is necessary to write only absolutely abstruse things, and those are usually preliminary created in the master.

How to work with the master, I think, there is no need to tell. I shall stop only on such moment: sometimes it is necessary to register line SQl in the module of the form. It is obvious to avoid mistakes, and at the same time to accelerate process of creation, it is better "to crack" inquiry in the master, to check up it, then to be switched in mode SQL, to copy received in the buffer and to insert into the text of the module. Such way allows, at all not understanding practically anything in SQl, to create complex enough inquiries, besides speed of creation of inquiries about the order above, than manually. However, syntax in VBA differs that the master in the designer writes a little. Here some features:

Instead of «;» (semicolon) in VBA it is necessary to write simply «,» (comma)

In design WHERE for fields of type "date" there should be a date in a "bourgeois" format. SQL on another will not understand. That is 22.02.04 should look(appear) as 02/22/07. It is convenient for this purpose function Format. For current date it(he) should look(appear) so: Format (Date (), " \ *MM\/DD\/YY \ * ")

Modules

Practically the first appendices by means of special masters started "to create" all, and in Access it is not not enough of them, on everyone windows of the program are the master: the master of creation of tables, the master of creation of inquiries, the master of creation of forms, the master of creation of reports, the master of creation of pages of access to data. Among all this guards « the master of creation of programs » - macros is especially allocated.

Often beginning developers Access do not hasten "to understand" with VBA, preferring to use masters. Nevertheless, it is possible to create complex enough programs … practically not understanding in programming! Also it is done by means of macroowls.

Macros in Access represents the structure consisting of one or several macros which are carried out or it is consecutive, or by way of, set by the certain conditions. By means of macroowls it is possible to execute practically all actions above objects Access. Each macro has the certain name and, probably, one or several arguments which are set by the user. For example, at use of macro OpenForm as arguments it is necessary to set, at least, a name of the opened form and a mode of its conclusion to the screen.

I would name use of macroowls as the next step on a way to professional programming in Access. Use of macroowls is justified by that they are easy for creating, and for this purpose it is not necessary to study syntax of the programming language. Besides in Russian version Access all macros are translated on Russian, that even more simplifies their application. If you have solved, that you have enough standard opportunities of masters Access, and to understand subtleties VBA do not gather, or do not gather yet, and at the same time, wish to make the appendix more professional – that macros will help you with it. However use of macroowls has also some lacks:

Opportunities of macros are limited in comparison with opportunities of language VBA, therefore in some cases programming on VBA to not manage

Macros it is possible to use practically everywhere where procedures VBA are applied, however procedures VBA, as a rule, are carried out more quickly

Macros are the objects existing separately from forms and reports in which they are used, therefore, when these objects becomes much, their support is labour-consuming enough. Procedures of processing of events VBA are an integral part of forms and reports, and in it there are advantages. For example, at carry of forms and reports from one database in another with them the connected procedures are automatically transferred

Unlike procedures macros at transformation of the appendix in .mde a format are not compiled and can be edited and even to leave.

There is one more reason on which beginning developers should look narrowly to itself: by means of macroowls it is possible … to study) VBA. I shall explain on an example.

Let's admit, you wish to write procedure of export of inquiry in Excel, and as usual, do not know, how it to make. We come on Macros, we press "to create". In a floor the Macro it is chosen «Output_In_Format». In arguments of the macro we establish parameters: the type of object – inquiry, a name of object, a format of a conclusion - Microsoft Excel (*.xls), a name of a file – (for example D:/???????.xls), automatic loading – is not present. We keep macro. Now, to look, as all this looks in VBA, we allocate in a window of the project macro, we choose Service – Macro – To transform macro and further on dialogue. As a result there will be a module in which among other will be:

DoCmd.OutputTo acQuery, "", "MicrosoftExcelBiff8(*.xls)", "", False, "", 0

Thus, it is possible to create procedures that refers to « on slices », studying at the same time VBA.

The author: Admin It is added: 20.01.2007