Optimization of appendices in Access
Sometimes by development of appendices there is a situation when still recently working program suddenly starts "to brake", the inquiries which are earlier carried out that refers to « in 1/2» start to hang, etc. we Shall try to understand, that can cause to it and make the general recommendations in occasion of optimization of appendices. As is known, in ????ss there is a master, allowing to lead the fluent analysis of the appendix. We press Service – the Analysis – Speed – All types of objects – To allocate all – OK. So, what usually we see?
The appendix is not kept in completely compiles a kind
It means, that it is necessary to transform base from a format .mdb to a format .mde. To do it it is necessary having closed the appendix and having started Access. Further we find Service – Service programs – To create mde a file. And, if the appendix was created in other version Access, it all over again should be transformed in current: Service – Service programs – To transform a database, and already then to do(make) of it .mde. The transformed copy of base (.mde) As a result will turn out.
Since version Access 2000 compatibility of versions « from below upwards » is supported. It means, that .mde the file made in Access 2000 will work in later versions, and here on the contrary – will not be. The matter is that from the version to the version the format .mde a file, and that is clear for Access 2002 – a double-dutch for Access 2000 varies.
The base transformed thus will work more quickly because modules of the appendix will be compiled all. But as a result you cannot edit the form any more and reports in a mode of the designer. To transform .mde in .mdb back standard means Access as it will be impossible. It is necessary to remember it and always to keep initial (.mdb) the version of base. Standard it is impossible, but … As is known, there is no such lock which cannot be opened. Theoretically it is possible to decompile base (and not such programs "broke"), but it already concerns to area of "hackers", and for the clear reasons in given clause(article) will not be shined. We shall stop only on one question interesting to beginning developers Access.
Often programmers earlier developed the appendices in other environments (Si, Delphi, FoxPro, etc.) ask: « How to make in Access .exe? (the executed file, capable to work in itself, without Access) ». The answer always one: in any way. And the reasons for that a little.
The matter is that initially language VBA was developed as auxiliary means of creation of office documents, its definition in translation and sounds: « the Visual programming language for appendices», and by itself it was meant, that the appendices created with its help will work only under control of the parental appendix, in this case Access. Therefore at compilation in Access the machine code, and a so-called p-code is created not. The Ó-code is close to machine, but the program in the Ó-code cannot be directly executed by the processor. Transformation to a binary code occurs during performance of the program. It is made for debugging appendices. Without a p-code it would be impossible to interrupt performance of appendices, to edit texts of programs and again to continue performance. By the way, similarly in fact occurs and in other means of development. Everywhere there is a "intermediate" translation.
About a format mde it is necessary to tell especially. Very often beginning developers create a database, where given and forms, reports are stored in one file. Though in many managements on Access it is recommended to divide base on two parts: a file with data and a client part, and last to transform in mde. Why such scheme is considered optimum?
The file mde is "native" for Access, i.e. its structure is close to a binary kind, practically it too most as a file exe for Windows. Hence, if you allocate from a database of the table (by the way on volume of the information they always cost on the first place) and will compile all rest in mde a file, you receive the program with the minimal size. From here follows, that users will load more quickly the program and will work with it more reliably and more conveniently. It is necessary to note such: do not spend many efforts to converting mde files to other versions Access and do not try to open them from other versions Access is it will not turn out. Files mde have no initial texts of programs on Basic, hence for compiler Access is a double-dutch, just as for the majority of us language of tribe Mumba-Umba from Northern Africa. As already it was spoken above that mde the file opened in other version Access, it should be created in earlier version (it fairly for Access since version 2000)
If you develop the program some years and have spent many forces and energy there is no sense it to distribute in the open kind. In 2-3 weeks, having changed your copyrights to the, already someone will start it to sell as own development.
Kernel Access processes a database in which is easier are stored one tables. Hence, if in your mde-program there are latent mistakes in calculations there can be a destruction of a mde-file, but in case of a separate data storage your data in tables will remain.
At work in a network the database mdb is placed on a server, and with mde-programs on computers of the user. If you should change the old version of the program on new not what for to stop all work of the enterprise in case of with one mdb a file. You simply debug the mde-program on the computer, add fields, tables in a database mdb on a server, and then replace on a network mde-files of users. All this borrows some minutes, and practically various departments, for example, the Accounts department, Selling, work without a stop.
If at you one file mdb to provide compatibility of different versions Access practically it is not possible, i.e. it will be difficult to work with a file mdb from Access versions 2, 97 and 2000. If mdb-tables are stored on a server, you can be connected to them through internal or external drivers ODBC from any version Access.
If you work with the program of the warehouse account you need to describe about 40-50 different tables for a warehouse, administrations or adjustments. But it is obviously not enough of it, since on a warehouse should pass and the tables connected with cash department, with bank and the cost price (~60 pieces). If all this to write down in one file not only the bookkeeper, but also you will start to be confused in them. Therefore also it is necessary to divide a database into some parts.
Working with a "serious" database it is necessary to care of its preservation and development. For this purpose there are various commands: restoration, copying and removal, i.e. addition of new fields and tables. All is the most easier for carrying out these actions, if in your database there will be one tables.
The table …: connect with other tables of a database.
As follows from the answer of the master, in base there is a table not connected with others. In some cases it is justified – it is a question of so-called "service" tables in which are brought what or parameters of the appendix. In this case the answer of the master can be ignored. And here if it is a question of the help table, values in the basic table to the master it is necessary to listen whence are substituted. The matter is that Access concerns to the class most widespread now relational Base which basic attribute is that data are distributed in base as if clothes in to a case: socks in a box for socks, trousers in a box of trousers, etc. Besides it they also are connected among themselves by logic communications. For example: the Complete set of outer clothing are trousers + a shirt + socks. Having pulled for such "thread" the machine can pull out the complete set and to define its structure, and it means, that if you will set to it to search for a headdress – in section for outer clothing it any more will not get, so, the cap will be more quickly. Here it is the master and tries to din into us. That is such "correct" base, it as a good case at the accurate owner in whom there are departments, in departments of section, in cells. In turn the "bad" base is a chest where all is fallen down in one heap – try, find there that. In general, personally I, when wish to define a level of developer Access, first of all I look, how tables as data are distributed are connected at it. And sometimes it appears, that in general in any way. Say, let itself Access understands, as with what there it is connected.
Installation of communications between tables is done in special the Scheme of data. We press service – the scheme of data or simply right button in a window of the project and in the contextual menu it is chosen the scheme of data. In the opened dialogue window again we press the right button and is chosen To add the table. In the appeared dialogue window it is opened tables and it is chosen necessary: we click twice under names and tables appear in a window of the designer. We close a window with the list of tables.
For installation of communication(connection) between tables it is necessary to choose in one of tables table a field for communication, to press the left button of the mouse and to drag a field in the second table. To release(let off) the left button of the mouse above that field of the subordinated table with which communication is established. After that there will be a dialogue window Change of communications. Here it is possible to put automatic check of reference integrity – a tag maintenance of integrity of data and if it is required, cascade updating/removal of the connected fields. I practically always include a tag maintenance of integrity of data because if it to not make shortly your base will be hammered by "dust" - there will be with what not connected records.
The table …: add an index for a field
The index is the ordered list of values and references to those records in which these values are stored. To find the necessary records, Base all over again searches for demanded value in an index, and then under references(links) quickly selects corresponding(meeting) records.
In each table there should be a so-called key – the identifier of record. Exception is made with only special service tables, is usual with parameters of the appendix in which not many records, as a rule, are usual. More often by keys do fields of type "counter" - a numerical field with unique values. But the master asks one more floor in the table though in it already there is a key-counter. The matter is that probably in this case in what or inquiries this field is often used, and on it(him,them) other records are extended. If a field search (performance of inquiry) will go more quickly.
However indexing can result and in a boomerang effect. The matter is that at addition and removal of records or at updating values in an index floor it is required to update an index, that at a plenty of indexes in the table can slow down work. Therefore indexes usually are recommended to be created only for those fields of the table on which search of records is most often carried out. To index it is possible any fields, except for fields of type the Hyperlink and objects OLE.
So, we open the table in a mode of the Designer, we choose a field for which it is required to create an index, further the General and the field value Yes (Are supposed concurrences) is chosen for property or not (Concurrence are not supposed).
The index can be as also compound. Usually such indexation apply, when it is necessary to set unique values for group of fields. For example, in one floor number of the contract, in other the identifier. It is necessary, that in the table preservation of data of type 12R, 12?, 12? was authorized, and here two times 12R it was not authorized – at once there should be a message on repeating records. It is done simply: again we open the table in a mode of the Designer, on the panel of tools the Designer of tables we press the button Indexes. In the first empty line weeding the Index we enter a name of an index, then in a floor the Name of a field we press on an arrow and it is chosen the first field for which it is necessary to create an index. A next line weeding the Name of a field it is specified the second indexed field, and for the given line the field the Index should remain empty. Further we repeat it for all fields which are necessary for including in an index (it is authorized to include in an index up to 10 fields).
The form (module) …: add instruction Option Explicit.
In many programming languages variables should be declared necessarily, and these announcements are used by the compiler to reserve memory for variables. At the same time in VBA the announcement of variables is not obligatory, and use of undescribed variables is supposed.
Once among the programmers using different programming languages, disputes in occasion of necessity of the obligatory description of all variables were conducted. Really, to whom hunting to register each variable Dim i As Integer, j As Integer, etc. However other party, one of the most dangerous sources mistakes in the programming languages supposing application of undescribed variables, is typing errors in a writing of names of variables. Such typing errors are interpreted by the compiler as occurrence of one more, new a variable which is distinct from before used, and not perceived as a mistake. At times for detection of such typing error time many times over surpassing what would be required on the obvious description of all variables used in the program is required.
In VBA has been solved so — to give the sanction of this dilemma to the programmer. In this language there is operator Option Explicit. If you will begin the module with this operator should be located right at the beginning of the module before the first procedure of this module will begin VBA will demand the obligatory announcement of variables in this module and to generate messages on a mistake every time as will meet not declared variable. Besides that this requirement became obligatory for all modules without exception, it is possible to establish parameter Require Variable Declaration (the Obvious description of variables) on Editor dialogue windows Options of editor VBA.
If to not use Option Explicit and variables to not declare, the variable will by default appropriate type Variant. Here on it the master – on a variable of the general type also swears. The matter is that such variables borrow more places in memory in comparison with data of other types, and the main thing, they cannot be processed immediately. When the compiler finds out an alternative variable all over again should find out its present type, transform to it data, and then to do with them calculations. On all this are required time and if in simple appendices where there are no complex calculations, it is possible to apply variables of type Variant in complex, especially where there are many calculations to double accuracy, the difference on speed of calculations can differ in times. In general, at professional programmers VBA it is considered a good form to register in heading Option Explicit – that you will avoid not only a problem, but also many other things, concerning not only productivity.
The form …: use smaller number of elements.
It perhaps, the most often meeting recommendation of the master. Really, many beginning developers try "to hang" set of elements on one form: fields, fields with the lists, the subordinated forms, on which again the fields, the subordinated forms, etc. All this is done with the purpose to make the appendix more "evident" that « all at once was visible ». Than it is fraught? Loss of speed of loading of such form.
The matter is that usually each element has the source of data connected with it which needs be "to picked up" at opening. Knowingly in fact there are so much events of the form connected with its start: opening, loading, inclusion, reception of focus – just to operate process data, when there is no an opportunity to use is less than elements. We shall consider usual mistakes of beginning developers and ways from elimination.
1. Number of records in the form of
Happens so, that before normally loaded form, suddenly starts "to brake". One of the reasons can be that a source of data of such form – the table which, as is known, is filled. All is normal, while data there the order of several thousand, but it is necessary to work with base some years - and the quantity of records (especially in accounting programs) can pass for some hundreds thousand. And at work such form noticeably, especially, if it is made as the subordinate on the basic. As an output – to use as a source of data of the form inquiry where it is possible to limit quantity of records, for example, the period of dates.
2. The subordinated forms
Try to not use more than one - two subordinated forms on the basic. Remember, that at opening the basic form consistently there is a loading in the form and subordinates that demands time. Especially it concerns(touches) forms with on which subordinates are located. If to refuse such design it is impossible, then apply a dynamic binding of data to the subordinated forms at transition on, using property of form RecordSource:
Forms! frmGlawn! frmSub. Form. RecordSource = " a name of inquiry "
One more attribute of the beginning developer – presence on the form of set of the latent fields and the subordinated forms which are used either for calculations, or in case of with forms – as the original filter for reception of value of a field from the table on the set parameter (to value in other floor). Such way is really simple: the source of data of such form does inquiry in which there is a reference to a field of main form Forms! frmGlawn! Field1. During the necessary moment updating the form (Requery) is done and value from the subordinated form is read out. But if such forms it is fastened a little, and each time them to update is can noticeably affect speed of work of the appendix. Better instead of it to take advantage of standard function:
DLookup (" the Name of a field of the table or inquiry ", " the Name of the table or inquiry ", " [the Name of a field of the table on which it is filtered] = value of the filter ") – see Hel
3. Fields with lists
Presence of a plenty of fields with lists which sources of data inquiries to tables with a lot of records as it is undesirable owing to loading data in such fields at opening the form. It is possible to try to use dynamic connection of a source, for example, at reception of focus, by means of property:
Field.RowSource = "Query_name "
We have considered only the most simple recommendations which the master could define. It is possible to add still: