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:
- Selecting a provider
- Connecting to a database
- Selecting an object to connect
- Customizing a query
- Inserting a data table into Microsoft Excel
Step 1. Selecting Provider
Select the desired data provider as described in the Database Connections topic.
Step 2. Connecting to Database
Connect to a database as described in the Database Connections topic.
Step 3. Selecting Object to Connect
Use this step to select an object to connect and configure its properties.
Here is a page sample:
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.
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.
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:
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.
When you reconnect to an object, the add-in applies the format used before.