Contents

Select Database and Table

Create and Modify Database

Create Index for Database

Repair Database, Compact Database

Restructure Database

Global Replace

Manipulate Data

View Database Design

Import Data


Import Data

You can import 'ASCII-delimited' data files.  Proceed with caution.  Your complete database must be backed up on tape before you attempt an import.

To import  data

1.	In Database Selection, specify the database and the table.

2.	From Options menu, choose Import.  The Import window is displayed.

3.	Click the Source File box and select the source file.

4.	Press the Sample button.  The system runs a 'trial' import of the first record and displays the converted results.  Review the results carefully.  If you are satisfied, continue.

5.	Choose OK to run the import.  Click the Stop button to stop.  The Status fields indicate the number of records being imported, and the number of  errors.

6.	If there are errors, review the ERRORLOG.TXT file.  You should find the file in the current directory; otherwise run a file search in the File Manager.

The data is trimmed, i.e. all leading and trailing spaces are removed, if the Trim Data box is checked.


Create and Modify Database
Create and Modify the Database

The Infothek Database Manager allows you to create and modify database structures.  Before you build a database,  sketch out and plan the new database layout carefully.  Determine the tables and the associated fields, their data type and their data size.  Also, plan proper indexes for the database.

Once a field is added to a table, it cannot be erased.  If a field must be removed, a new table can be created and the data transferred from the old table to the new table.  See Restructure Database

To create or modify a database

1.	In the Database Selection window, select the database and table (unless you are creating a new database or table).

2.	From the Options menu, choose Create New Database, Add New Table or Add Fields.  If prompted, specify the database name and table name.

The database name must be a single word not exceeding eight (8) characters and must  conform to the DOS file naming convention.  The database name must include the full directory path, for example C:\MYDIR\MYDB.MDB.  The table name can be longer and may include spaces, but a single word without spaces is recommended.  If you use Megatype, the table name should not exceed 18 characters.

Choose OK.  The Database Design window is displayed.

3.	In the Field column, enter the name of the fields.  Avoid spaces, if possible.

4.	In the Type column, select the data type from the pop-up list.

5.	If the data type is 10 (text), enter the size of the field in the Size column.

6	Repeat steps 3 - 5 for each field.

7.	Choose Create.

8.	Optionally,  create the indexes.  From the Options menu, choose Create Table Index.

See Inex for Database.

9.	Optionally, specify the field formats, masks and data integrity checks (Not available for all database products).  From the Options menu, choose Field Format.

10.	Optionally, review the new database.  From the Options menu, choose View Database Structure.  See View Database Design

11.	Press the Close button.

To delete a table

1.	In the Database Selection window, select the database and the table that you wish to delete.

2.	From the Options menu, choose Delete Table.

3.	In the prompt, enter the table name.

4.	Choose Yes.

To delete a database

Like any DOS file, a database file can be deleted with a Delete command in the Windows File Manager.

See also Repair Database, Compact Database.


View Database Design

To view the database design

1.	In the Database Selection window, select the database.

2.	From Options menu, choose View Database Structure.

3.	Use the scroll bar to find the topic.

4.	To print, press the Print button.

5.	When done, choose the Close button.


Create Index
Create Index for Database

A good index  can dramatically improve the system's response time, at the expense of some additional disk space and some slower database updating.

You can create several indexes for a table; each one must have a unique name.  The Create Index program must be run for each index.

The 'Primary' flag makes the index the primary index for the table.  One (but only one) of the indexes is designated the 'Primary' Index.  The primary index determines the 'uniqueness' of the records.  Primary indexes do not permit duplicate entries (as defined by the index).  Secondary indexes, however,  permit duplicates (as defined by the index) unless the 'Unique' flag is set.

To create an index

1.	In the Database Selection window, select the database and the table.

2.	From the Options  menu, choose Create Table Index.  The Create Index window is displayed.

3.	In the Index Name field, specify the Index Name.  Spaces in the index name are permitted, but not recommended.

4.	Check the Primary and/or Unique check boxes, if appropriate.

5.	From the Field List, select the most significant index field.  Press the Ascending button or the Descending button depending on the desired sorting order.

6.	Repeat step 3 for each field in the index.

7.	If you made a mistake, press the Clear button and start over.

8.	Press the Create button.

9.	From the Options menu, choose View Database Structure and review the new index.  See View Database Structure

To delete an Index

1.	In the Database Selection window, select the database and the table.

2.	From the Options menu, choose Delete Index.

3.	Confirm.


Select Database and Table
Select the Database and Table

Before you modify a database table you must select the database and the table.

To select the database and the table

1.	Choose Database/Table Selection from the Options menu.

2.	In the Database Selection window:

-	Verify the drive.  Change it, if necessary.

-	Choose the directory from the Directories listing (double-click).

-	From the Databases listing, choose the database.

-	From the Tables listing, choose the table.

3.	From the Options menu, select your option.


Repair Database, Compact Database

If your database is damaged, you should run the repair program.  Damage to a database can occur if the database is not shut down properly (for example a power failure).

Furthermore, after extensive and extended use of the database, or after running the repair program,  you should compact (re-organize) the database file in order to recover discarded disk space.

Before you run the compaction programs, ensure that you have good tape backups.  The database must be closed when you run the programs.

To repair the database

1.	In the Database Selection window, select the database.

2.	From the Options menu, choose Repair Database.

2.	Choose OK to confirm.

To compact the database

1.	Make a good tape back up.

2.	In Database Selection, select the database.

3.	From the Options menu, choose Compact Database.  The Compact Database window is displayed.

4.	Select the name of the temporary destination file from the list of five preset files.  If the file already exists, you must delete it (in the Windows File Manager) or select another file name.

5.	Press OK.

6.	After completion, if the compaction is successful, copy the temporary destination file back to the original database file.  Use the 'copy' or 'rename' functions of the Microsoft Windows File Manager.  The temporary destination file can now be deleted.


Restructure Database

You can transfer the records from one database table to another database table.  This method will allow you to restructure the database.  The source table and the destination table may be differently structured.  Before you can make a transfer of records, you may need to create the destination database.  Be mindful of index requirements.  The records in the source table must be compatible with the structure of the destination table.  For example, if the destination table features a unique index, it cannot accept duplicate entries. Records that contain a blank in an indexed field are also rejected.  .

The transferred records are APPENDED to the destination table as new records, they do not update existing records.

The system provides you with an error list of rejected records.  The DB_ERROR.TXT error file can be viewed with any text editor, for example the Microsoft Windows Notepad. The error file can be found in the application's directory.  If you cannot find the file, use the Windows Database Manager search function.

If you transfer records into an existing table, it is very important that you make a good tape backup of the destination database before you run  the restructuring program.  Many things can go wrong, and you may need to restore the database.

To restructure the database

1.	Ensure that the destination database and table exist.  Make sure you have good backup tapes of the destination database.

2.	In Database Selection, select the source database and the table.

3.	From the Options menu, select Restructure Database.

4.	In the Destination panel,  select the destination database and table.

5.	In the 'Transfer FROM' column of the grid of field source names,  select (click) the first field that you wish to transfer.

6.	Select the corresponding field from the Field List (Destination).

The data types of the corresponding fields must be compatible.  The system will advise you if the data types are not compatible or are likely to create incompatibility.  For example, you should normally not transfer a text field into a numeric field.

7.	Repeat the above step for each field that you want to transfer.

Some cells in the 'Transfer TO' column may be left blank.

8.	Review the entries, then press the Execute button.

9.	If the error file already exists, you will be prompted to clear it.

10.	Review the transferred records.  Do not delete the source database until you are satisfied that the transfer was executed correctly.


Global Replace

You can replace field values in a table globally.  Be mindful of index requirements, field types and field sizes. The data value cannot be replaced if the field types and sizes are incompatible.  If the system encounters an error, the program aborts.

When specifying the original field value you may use wildcard characters.  Use an asterisk (*) to indicate one, several or zero contiguous characters.  Use the question mark (?)  to specify one single character, and use the pound sign (#) to indicate one single digit.

Make sure that you have a good tape backup before you run the Global Replace program.

To replace field values

1.	In Database Selection, select the database and the table.

2.	From the Options menu, select Restructure Database.

3.	From the Field List, select the record field.

4.	In the Global Change panel, in the From and To boxes, specify the word(s) that you wish to replace and the replacing word(s).

5.	Choose Execute.

6.	To abort the process, press the Stop button.


Manipulation of Data

The 'Manipulation of Data' screen allows experienced users to change any field value based on specified filter criteria.

To change the values in the database

1.	In Database Selection, select the database and the table.

2.	From the Options menu, select Manipulate Data.

3.	From the Field List, select the record field that you want to change.

4.	In the WHERE Statement box, specify the filter.  The entry must be a complete SQL WHERE statement (but the word WHERE is not required).  Make sure that you include the filter operator and that you enclose strings in single quotation marks.

5.	In the New Value Expression, enter the new value or the expression that will create the new value.  Make sure that strings are enclosed in single quotation marks.

5.	Choose Execute.

Examples:

The following statement creates a 7+4 zip code:

WHERE [zip4field] > 0

[zipfield] + "-" + [zip4field]

The following statement changes the customer name from J Doe Inc. to Doe Corporation

WHERE [customerfield] = 'J Doe Inc.'

[customerfield] = 'Doe Corporation'