Database Connection Wizard

Database Connection Wizard

The Database Connection Wizard allows connecting to tables, views, stored procedures, and table functions.

You can use the wizard to connect initially and reconnect later.

The add-in uses the wizard in other modes also, for example, to configure connections or query parameters.

The wizard inserts Excel tables and creates the required infrastructure to save changes.

Use the wizard to connect if your current tables have the disabled Save button.

You can connect to SQL Server, Azure SQL Database, Oracle Database, IBM DB2, PostgreSQL, MySQL, MariaDB, NuoDB, Snowflake, SQL Server Compact, and SQLite.

The wizard includes the following steps:

  1. Selecting a provider
  2. Connecting to a database
  3. Selecting an object to connect
  4. Customizing a query
  5. Inserting a data table into Microsoft Excel

Step 1. Selecting Provider

Select the desired data provider as described in the Database Connections topic.

Database Connection Wizard - Select Provider

Step 2. Connecting to Database

Connect to a database as described in the Database Connections topic.

Database Connection Wizard - Connect to Database

Step 3. Selecting Object to Connect

Use this step to select an object to connect and configure its properties.

Here is a page sample:

Database Connection Wizard - Select Object

Below are important comments about the page controls:

Query List
Query lists contain objects available to connect.
The default list, All tables, views, and stored procedures, selects all objects.
You can select a database schema to have a shorter list.
Database developers can configure query lists to show only desired objects.
Enable SaveToDB in this workbook
Check this checkbox to support advanced SaveToDB features like saving changes.
The add-in will add hidden worksheets to store the required metadata.
The checkbox is disabled if the workbook already contains the required worksheets.
Enable the ribbon query list
Check this checkbox to allow connecting to new objects using the ribbon query list.
Use this button to customize an SQL query.

Also, use the textbox under the Enable the ribbon query list checkbox to filter objects typing several characters.

Step 4. Customizing Query

When you connect to tables and views, this step allows configuring fields to select and use as WHERE filters.

For example:

Database Connection Wizard - Choose Fields

Check the fields to select in the leftmost column.

Check the fields to use as WHERE filters in the rightmost W (Where) column.

The add-in places such fields on the ribbon. So, you can easily filter data.

You can use the button with three dots to select multiple values. The add-in builds the WHERE ... IN clause in this case.

When you connect to stored procedures or table functions, the wizard shows parameters.

For example:

Database Connection Wizard - Choose Parameters

Check the parameters to place on the ribbon in the rightmost W (Where) column.

You can set parameters values here.

Use the button with three dots to edit a parameter value list.

This option is not available if developers have configured parameter values.

Step 5. Inserting Query

If you started the wizard from an existing table, the wizard inserts the new query in place.

This option is useful to reconnect existing queries. For example, to restore the source query column order.

Otherwise, the wizard asks for an address to insert a table.

We recommend inserting tables starting cell B3 to use the row above the table to change filters.

Take a look at the screenshot:

Database Connection Wizard - Final Workbook

The screenshot shows the Database tab with the following features:

  • You can use the Save button to save changes (if the object supports it)
  • You can use the Reload button to reload data
  • You can use the Query list to connect to new objects
  • You can use the ribbon Parameters to change query parameters
  • You can use the View list to change table views

When you connect to an object for the first time, the add-in tries to load Excel table format, including table views from a database.
If it cannot find the format, it applies the default formatting.

See details in the Table Format Wizard and Options Dialog Box articles.

When you reconnect to an object, the add-in applies the format used before.