Autoconnection tables at opening the appendix of a database

The divided (undressed) database

Create the very first databases in Access, beginning programmers as a rule build appendices which usually consist of one file of a database, that is tables, forms, reports - all are located in one file mdb. Such scheme can be convenient only when the base is done that refers to « for itself ». But Access as allows to create except for local appendices of databases and network. In the elementary a case it is the divided database which includes two files mdb: the first is a file of objects of data (the majority of tables is stored(kept), by the second — a file of objects of the appendix in the remained objects of base — forms, inquiries, reports, pages of access to data, modules VBA contain all, macros. In this case in a file of objects of the appendix it is necessary to establish communications with the tables stored(kept) a file of objects of data.

At division of a database receive following advantages:

1. In the one-user environment it is possible to update objects of the appendix of base, not rendering influence on existing data. Thus the appendix is updated by simple replacement of a file of objects of the appendix of base. As alternative to it such way can serve: present, that you have established not divided database, users have started to work with it, and is constant, and here to you give the task, that or to change: to make the new report, inquiry, etc. It is necessary to expel users, to sit down the machine, to do work. And all this time the base will be not the worker. Certainly, such situation is not real. More really, that will fire you more likely, than will stop manufacture. Therefore, even it is better to do the one-user databases divided.
2. In the multiuser environment with the same data users of the appendix as the file of objects of data is placed on a file server can work in common all. As a file server the general folder can act in which place a file of objects of data.

I hope, that in occasion of expediency of division of a database of doubts is not present. It is necessary to solve the problem, as it to make. There are two variants:

1. We do new empty base, we press in a window of base the right button, we choose in the contextual menu "Import" or « the File – External data – import », and further on dialogue. As a result to base will be imported tables (are copied). We delete tables of base, import whence was done, and connected it to tables of new base.
2. We do division of base by means of the master: « Service – service programs – division of databases » and further on dialogue.

Connection to a server is done very simply – we press the right button in a window of base of objects of the appendix (there where forms and all rest), we we choose in the contextual menu « Communication with tables » or « the File – External data – Communication with tables » and further on dialogue. As a result in our base there will be labels of tables, and with a badge of an arrow at the left which means, that tables external. Restriction at work with such tables – it is impossible to change their structure (to add, change fields, etc.) in this base. It is possible only in that where they are created.
But it is better, if have decided to do base divided, at designing at once to create two bases mdb: in one we place tables, in another all rest.
So, the base is divided. I shall remind, the basic advantage of division of base – an opportunity of creation of the network appendix. We shall present: on one of network computers we create a folder "Base", we open to it the general access (it is necessary as to open access and to a disk on which it is established(installed) Access, is usual "With"), and the file of objects of data (base with tables) is placed in it. On other computers we place copies of files of objects of the appendix (base with forms, reports, etc.) and it is connected them to our "server". The network appendix has turned out – many users are brought by data in one base. Such appendix refers to « the File – server » as as "server" the file of objects of data acts.
But we shall admit, on what that to the reasons of a site of "server" has changed – a folder Base have moved. As soon as users will start the appendices, they will have a message that tables are not found. In this case, we press the right button on a label of the table in the appendix of users, we choose in the contextual menu « the Dispatcher of the connected tables », we mark those tables, the way to which needs to be updated, or we press « To allocate all », then «??» and further on dialogue.
All the above-stated actions are done(made) manually. But whether it is possible for all this to make automate?

Autoconnection tables

First we shall think, in what else cases, except for as unwillingness manually to press buttons, can it is required? It would Seem, has placed base on a computer – "server", has connected to it bases of users, and do not touch it (do not move). What for each time at start to cling tables? Has put here here in what:

1. Sometimes it is necessary to change a site of " a file server ». Present, 50 users, 50 times should be run to everyone and to adjust new connection.
2. Let's assume, you have made the adjusting distribution kit of your appendix Access – Setup.exe. Whence to you the nobility where the user will want to establish your program: on "With", «D» or where that still? It is possible certainly in adjustments of the program – the packer to specify, that the program will be put only in « ?:/Program Files … ». But such approach is not serious, and only will show your nonprofessionalism.

So, to disappear there is no place, it is necessary to cling. What for this purpose it is required?

The most important is CurrentProject. Path (the current project - a way): it appears, Access itself knows, where it have established. Program methods of work with tables (reading, record, removal of data) further will be necessary – in fact it is necessary to define not simply where have established the appendix, but also to keep its way to the table, and then "to pick up" them on this way. It is done by means of objective model of access to data DAO - Data Access Objects. Objects of access to data were created, as the object-oriented interface for a kernel of databases Jet of firm Microsoft just that it was possible automat to bring, change, delete data in tables.

The general scheme(plan) of work of connection:

At start of the appendix it is started macro AutoExec - that it was started at start, it should refer to AutoExec is the reserved name in Access. Macro in turn starts function SetReferences () from module AutoPatch which actually and makes autolink tables. Further macro starts the form frmStart – the starting form of the appendix.

The way of base is kept in the table tSystemPath – Pname. Names of tables which need to be attached – in the table tSystemTables – Tname. If you will apply this example in the projects, you will need to write down there names of the tables.

In the given example at start of the appendix besides autolink one more important issue is done: the way to a folder with backup copies of base (about it hardly below) enters the name. These are the so-called given adjustments of the appendix. Them can be much: for example, the catalogue of a unloading of pictures of reports, the catalogue of patterns .dot, etc.

Reserve copying of a database

Though Access also it is considered enough reliable system, but also it has failures and bases "fall" – are not present in the world of perfection. That is why it is considered a good form to make service – preservation of backup copies of base. Really, what for to risk?

Methods of work will be necessary for this purpose with file system Windows – module FileSystem and to function of work with files: fDeleteFile, fCopyFile. Names speak for themselves. That the user could specify/create the catalogue for preservation of backup copies it is required GetDirFolder – function of opening of a dialogue window of a choice of the catalogue.

Directly copying is made by means of procedure sCopBase in the module of the form frmStart. And, all over again presence of the catalogue for preservation of a copy of base, presence of the base – functions IsFolderName, IsFileName is checked.

Names of spears of base are set with the instruction of date and time of copying.

author: Admin the Size: 240 Kb add: 07.01.2007