Create Database Referencing External File

To create a new database using external reference file, call the New command. In the appeared Database Properties window, specify the name of the new database (mandatory), comment (optional) aand background color (optional), as described in the Create Database section.

Then you should specify data source file and mode of updating the database.

For specifying the source file it is required preliminary to enable the Reference flag. After that the File Name field becomes accessible. To select a file, use the corresponding button on the right. The standard window of the file selection dialog will open, in which selection of the required file format and its location is carried out.

To create a database by reference, it is possible to select the files of the following formats:

•Microsoft Access Files (*.accdb, *.mdb);

•Files of the format dBase (*.dbf);

•Microsoft Excel Files (*.xls);

•FoxPro Files (*.dbf);

•Paradox Files (*.db);

•Text Files (*.txt, *.csv).

When creating the database on the basis of the file of *.xls format, it is required to specify additionally that part of the information from the Excel book which will be used as the database contents. Selection is carried out with the help of the drop down list of the Table Name parameter. By default, the system prompts the user to select one of the pages of the Excel document. In this case, as the contents of the created database, the entire page of the Excel book will be used. The columns’ headers will be created on the basis of the first string of the selected page.

When it is required to use only a part of the page’s contents, it is possible to preliminary create the named data domain in the Excel document (select the desired cells in Excel and perform Insert\Name\Assign). In this case, when creating the database by reference, this name will also appear in the list of the Table Name parameter. The first line of the selected information will also be treated by the system as the string with the columns’ headers of the database table.

When creating the database on the basis of the file of *.accdb and *.mdb format, it is required, in addition to the file’s name, specify the table’s name from this file. The contents of the selected table will be transferred to the database being created. The list of all tables in the selected file will automatically be placed in the drop down list of the Table Name parameter.

The Table Name parameter may also be defined via a textual variable. To do so, input a variable name in curvy braces (e.g.: {$name}).

When creating the database on the basis of the file of *.dbf and *.db formats, you need to specify only the file’s name (the Table Name parameter is not available). In addition, the DOS Codepage flag is available. When this flag is disabled (default state), the system assumes that the format of the external file is Dbase-Windows. When this flag is enabled, the external file is opened only as a file of the format Dbase-DOS. This allows us to correctly open the databases that contain Russian names for the columns and the contents of the text cells.

When creating the database by reference on the basis of the file of textual format, this file must have the following structure:

•the first line must contain the column’s names that are delimited by a symbol. The symbol of delimitation is specified in the database properties;

•the following rows contain the records of the database. The contents of the fields in each record are also separated by this symbol.

 

In the case when the T-FLEX DOCs system is installed on your computer, one more option of creation of the database by reference is available – the database by reference to the T-FLEX DOCs dataset.  This is described in more detail in the T-FLEX DOCs user manual.

The mode of updating the created database can be selected from the drop down list of the Update Mode parameter:

•Auto. Updating the data is carried out when opening the file of the model.

•On Full Regeneration. Updating the data is carried out when performing the total recalculation of the model with the update of references.

•Manually. Data updating is performed only manually using the special Update option in the Database tab of the ribbon.

By pressing OK you complete creation of a database by reference. The Database Properties dialog is closed, and the table of the new database appears on the screen. It can be viewed but editing the contents is not allowed. The access to the data from a database by reference is carried out similar to the access to internal databases, i.e., by using the name of the database by reference similar to the name of the internal database. In this case the syntax of functions remains the same.