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:

• Bring up the "Insert Database Worksheet" dialog by right-clicking on a worksheet tab (to insert one before that worksheet) or by selecting the option from the worksheet menu.
• If you want to use an existing ODBC Data Source that has been set up on your computer (perhaps by a system administrator), then just select it from the list in the top left of the dialog. Enter a username and password if the database needs one.
• If, instead, you want to specify your own ODBC connection string, enter it into 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;
• Next, hit the Connect >>> button. This will attempt to connect to the database and get a list of all of the tables in it. If successful, the tables will be displayed in the "Table or View" list in the top right of the dialog (this feature is currently supported by Microsoft SQL Server 7 and up; MySQL 5 and up; PostgreSQL 7.4 and up). If you want to display everything from a particular table or view, select it in this list. If your database software is not supported, or you want to enter your own SQL query, enter it in the text box just below that list.
• If you want Resolver to make the first row of your Database Worksheet contain the table's, view's or query results' column names, check the box with the obvious name. What may not be quite so obvious is that checking this makes the first row a Header Row.
• Finally, you will notice the checkbox and list marked "Update on Changes to". This feature allows you to have Resolver One recalculate the spreadsheet when the particular selected table is updated, or has data inserted or deleted from it. See Database-Triggered Recalculation for more information.
• Once all of these fields are filled in, click "OK" and a new worksheet will be inserted. 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.

Have fun!