Using the CONNX Excel Add-In
Installing the CONNX Excel Add-In adds a CONNX tab in the Excel ribbon menu.
1. To import data, click Use Existing Connection or Create New Connection Wizard.
The data can be edited and the spreadsheet may be saved, however those changes do not affect the database table.
2. To Edit data in Excel and send those changes directly to the database a table connection must be made through the data task pane.
In the CONNX Ribbon, press the CONNX Data Pane button and the CONNX Data Task pane appears.
3. To make a connection to a table in a database, select Edit Data to import the data, then track and send the changes back to the database.
In order to track and send the changes correctly, the connection here must be made to only one table. Joins and aggregates are not allowed in these connections. The dropdown list will show the friendly name of the existing connections that specify a single table only. If there are no existing connections, a new table connection can be made by pressing the Create Table Connection button.
a. Select the Data Dictionary with the data source in it and specify the user name and password to log into the data dictionary.
b. Select the table you want to edit.
When importing data only, there is an additional tab for using a SQL Statement as a source. A single table must be selected in order to enable data editing and allow changes to be sent to the database.
c. To use a select statement for the source, select the Source SQL Statement tab.
This is useful for when the user wants to limit the amount of rows returned to Excel for edting.
When using an SQL statement as a source for editing data the following rules apply:
Select statements cannot contain table joins, aggregates, sql functions or column alias', or any other SQL grammer that will make it impossible for the correct row in the source table to be determined while moving changes to the database.
Select statements must contain enough columns to make the data being edited unique, if not, the changes will fail because the correct row in the table will not be found.
d. The final screen allows customizing how the new data connection information is saved.
You can specify a File Name, Description, and Friendly Name. The Friendly Name is the name shown in the CONNX Data Task pane list. Uncheck Save password in file to not have the Data Dictionary password written to the odc file. A registry entry can be used to disable this checkbox permanently. Then, the user must log in every time they load or refresh data. The registry value can be added in the Configuration manager under the CONNX key, if EXCELADDINNOSAVEPASSWORD is added and set to 1, no passwords will be able to be saved. If set to 0 or no value is given, users can save passwords to odc files.
Click Finish to load the data into the spreadsheet.
This sheet is now enabled for editing and tracking changes to be sent to the database. The Commit and Revert changes buttons are enabled. After changes have been made in the spreadsheet, pressing Commit changes invokes a screen with a list of all the changes in the order they happened. From there, the changes can be applied to the database or skipped. Revert Changes undoes all changes made to the data in the spreadsheet and reloads the most recent data from the database table.
4. After you make the desired changes to the data, click Commit Changes to invoke a screen that lists the changes and the order in which they happened.
Review the changes and if correct, click Commit Changes to send the changes to the database. You can select how the changes are applied to the database. Select Apply all changes, if there are errors do not commit any changes when you don't want to commit any of the changes in the case of an error in any of them. This option is useful if later changes are dependant on earlier changes going into the table. If the changes are independent of each other, you can continue sending and committing the changes even if one fails. To do so, select Apply all changes, continue trying to apply the changes in the event of a failed change.
It is possible that data is changed in the table from another source and that could cause an error when trying to change data in that row. In addition, a quick check is performed to ensure the data going into the table is the correct type of data for that column in the table.
5. Click Commit Changes.
All the changes that succeed are removed from the list of changes.
If any errors with certain changes occur but you want the rest of the changes go to the database, check the Skip checkbox.
Clicking Revert Uncommitted Changes removes any changes that had errors or were not successfully sent to the database.
6. To write the changes to a file at any point before or after sending them to the database, click Write Changes.
This action writes each row in the display to either a tab-delimited or comma-separated file. This can be done for auditing purposes or saving an error to be looked into later.