Introduction

Besides basic data import/export, it is possible to work with 3rd part database (Access, Excel or Lotus) using a script. This may be quite useful if it is not possible to find the exact correspondence between your DB fields and fields of the DB you need to export to.

You can also use SQL queries to work with external database. For more information review the "External DB SQL Query" and "For each record from SQL query (External DB)" cycle articles.

Example

The easy way to understand script usage is to give an example. For instance, there is an Excel-based DB stored at C:\example.xls" Suppose the main sheet of this Excel document has the following cells:

Col 0	Col 1	Col 2
Report Date:	-	-
Currency Rate:	1.4	-
Title	Price	Discout
-	-	-

We need to get currency rate from it and fill in this document using data from our database.

1. Open a database or create a new one;
2. Create a button on a toolbar or on a form with the following script:
   
   
   As you can see, only three actions can be used for work with an external database:
   • Connect to External DB - allows connecting to an external database. If you have successfully connected to the database, you can perform the following commands:
   • External DB Operation - perform a standard operation with an external database (pass to a specific line, get the cell value, set the cell value, add a record). Note that cell numbering starts with 0.
   • Close External DB - closes an external database.

   In the 11th line we create a recordset variable [$records] to export records to the opened example.xls in the cycle (lines 12-17).

3. Save the script and click the created button

If everything was done correctly, the file example.xls should contain the following data:

Col 0	Col 1	Col 2
Report Date:	10.11.2008	-
Currency Rate:	1.4	-
Title	Price	Discout
-	-	-
Apple	$16	5%
Lemon	$4	7%