Intro to the Advanced Setup
There are two ways in SQL Spreads to connect a spreadsheet to SQL Server so that the data in the SQL Server tables can be Updated, Inserted and Deleted from Excel; the SQL Spreads Designer and the Advanced Setup.
NOTE: We always recommend to use the SQL Spreads Designer as a first choice and only switch over to the Advanced Setup when necessary.
The SQL Spreads Designer
The SQL Spreads Designer is an easy to use way to select a table in SQL Server and configure how the table is updated from Excel:
Click here to read more about how to use the SQL Spreads Designer.
The Advanced Setup
The Advanced Setup menu let you create a Database Import to fetch data from SQL Server and a Database Mapping to write data back to SQL Server:
FAQ
When do I have to use the Advanced Setup?
In most cases, you can use the SQL Spreads Designer, but there are a few features that are only available through the Advanced Setup. Those features are:
- Data driven Drop-Down lists
- Formula columns - columns in Excel containing formulas where the value of the formula is written to the database table.
Note: If you have created a setup using the SQL Spreads Designer, you can always switch over to the Advanced Setup and continuing configuring your setup there.Can I switch from the Designer to the Advanced Setup?
Yes, you can always start creating a setup in the Designer and then switch over to the Advanced Setup and continuing configuring your setup there.
The easiest way to get started with the Advanced Setup is to first select the table in the SQL Spreads Designer. The Designer will create one Database Import and one Database mapping that you can go in and adjust using the Advanced Setup.
Note: Once you have edited a setup in the Advanced mode you cannot go back and do changes using the SQL Spreads Designer.Can I really do this from the Designer?
You can do most of the setups using the Designer.
Can I use custom SQL queries or quires with JOINs from the Designer:
Yes, the SQL Spreads Designer supports updating data in SQL Server Views. If you need to use a custom SQL query in the SQL Spreads Designer, consider creating a SQL Server View which contains the custom SQL query and then use that View from within the SQL Spreads Designer.
Can I use the Database Import filters from within the Designer:
In SQL Spreads, you can create tree-like filters to let your users select which data to load into Excel.
These filters must be created from the Advanced Setup menu, but you can still use the selected filter items to select which rows to load in a setup using the SQL Spreads Designer. Please see this article for how to add a filter in the SQL Spreads Designer that filters the data dependent on the selected Database Import filter item.
Are there any other differences between the Advanced Setup and the SQL Spreads Designer?
There are a few differences:
- The SQL Spreads Designer connects one SQL Server table per Excel worksheet. You can have multiple SQL Server tables in one Excel document, but you will have to put each table in a separate worksheet. In the Advanced Setup you can have several SQL Server tables in one Excel worksheet.
- When you refresh or save the data in a worksheet connected to SQL Server using the SQL Spreads Designer, only the data in that particular worksheet will be refreshed/saved to the database. Whenever you refresh or save to the database in an Excel document, all connections that are created using the Advanced Setup will refresh and save.