Loading data from a database with Database Worksheets

Resolver One's Database Worksheets are a user-friendly, simple way of getting data from a database into your spreadsheets. The values are not stored in the sheet, but are refreshed every time you recalculate the sheet, so they are always up-to-date.

In order to set up a Database Worksheet, follow these steps:

1. From the Worksheet menu, select up the "Insert Database Worksheet".

   The Insert Database Worksheet window opens.

2. Decide how you want to arrive at the ODBC connection string:

   • To use an existing ODBC Data Source already set up on your computer (perhaps by a system administrator), select it from the list in the top left of the dialog. Enter a username and password if the database needs one.
   • To specify your own ODBC connection string, enter it in the text box in the bottom left of the dialog. The ODBC connection string syntax is defined on the Microsoft MSDN site; here is a simple example: DRIVER={SQL Server};SERVER=myserver;DATABASE=mydb;UID=testuser;PWD=testpassword;

3. Press Connect >>>

   Resolver One attempts to connect to the database. If successful, the "Table or View" list in the top right of the dialog fills with a list of tables and views contained by the database. (This feature is currently supported by Microsoft SQL Server 7 and up; MySQL 5 and up; PostgreSQL 7.4 and up.)

4. Specify the data that you want to load from the database:

   • To display all of the data from a specific table or view in the list, just click on it.
   • For a table or view that is not in the list, or to specify your own SQL expression (e.g. to load part of a table, or to join several tables together) enter it into the text box just below the Table or View list.

5. Check the Show Column Names box to have the database column names inserted into your worksheet.  (This will also set the header row for the new database worksheet.)

6. If required, use "Update on Changes to" to specify what happens when the selected table or view is updated in the database. See Database-Triggered Recalculation for more information.

7. Click OK

   Resolver One inserts the new worksheet. It will be non-editable, but data in it can be referenced from other sheets and manipulated by user code just like data anywhere else in Resolver One.