6.9.2 Building and Saving a Query

To open the SQL Editor:

  • Click the Open SQL Editor button Button Open SQL Editer.png on the Database Access toolbar.
  • From the Origin menu, select Data: Connect to Database: New and supply a connection string or specify Data Link Properties.

For a detailed discussion of Building and Saving Queries, see these blog posts:

Connecting to a Datasource

If you have not previously connected to your datasource, you will need to set up a connection:

  1. In the SQL Editor, choose File: New... menu. This opens the Data Link Properties dialog box.
  2. On the Provider tab, select an OLE DB Provider, then click Next to go to Connection tab.
  3. Select or enter a server name (If the server is not listed, simply type its name).
  4. Enter User name and Password.
  5. Click OK (other tab controls are optional) to connect to your data source.

Building a query

Once you have established a connection to your data source, you will see a list of objects on the left panel of SQL Editor dialog.

Double-clicking the a table node will add the table node to the SQL Edit box on right panel. Or you can directly paste the SQL query into the SQL statement edit box.

Saving a query

You can save a query to a workbook or an ODQ file by selecting either File: Save to Active Worksheet or File : Save Connection and Query as.... In addition, clicking the Import button in the SQL Editor dialog box will automatically save the query in the target worksheet.

Saving a query to workbook allows you to preview the data on the workbook and import the data at any time you want. The workbook can be saved as a template, which can also store information on the formatting of the workbook. Next time you open the template, you will have a formatted workbook with a customized query saved in it. Merely clicking the Import Data Button db Import Data.png button on the Database Access toolbar will fill the formatted workbook with data acquired from the data source. To view or modify a query saved in active worksheet, click the Open SQL Editor button on Database Access toolbar.

ODQ files can be loaded by clicking the Load ODQ File button Button db Load ODQ File.png on the Database Access toolbar. All recently saved *.ODQ files are also listed under Data: Connect to Database on the main menu. After an *.ODQ file is loaded, you can preview or import it to the active workbook. This is useful when you want to apply the query to many different workbooks.