Question: One of my changes to the table failed when trying to commit to the database.  Why can't I just undo that one change?  Why do I have to revert changes to clear the bad one?  

Answer: It is correct that the only way to eliminate the failing change is to revert changes and get the data from the database.  The CONNX Excel Add In tracks each change as it is made, it does not save a before and after value for each cell.  So there is no data held to revert the change without refreshing the data from the source table.  Solutions were studied to see if this was possible, however due to limitations in the ability to track cell changes and the memory available to store copies of each cells values this was not practical.

 

Question: Why are large numbers are being rounded before being put into the source table?

Answer: Excel has a maximum of 15 digits of precision, after that it rounds to zero.  For example if you put the number 9223372036854775807 when input into a cell, when Excel Add In tracks the change, the number Excel gives to the Add In is 92233720368547700000.

 

Question: After I open a connection for editing a table on an Excel sheet, in the File -> Info tab of Excel it shows that the sheet is protected.  When I press the unprotect link it asks for a password, why?

Answer: When a sheet is being used to edit a database table most of the cells are protected.  This is necessary to be able to track changes and stop loss of data.  The protection cannot be removed, it is used for editing.

 

Question: I am editing a table and the last row is "cnxrowid" but when I change the values, why are they not saved?

Answer: The "cnxrowid" is a way for the CONNX to apply a row identifier to a table that has been imported into a data dictionary.  This is a virtual column, it does not physically exist on the source table.  By checking "Show CNXROWID" in the data dictionary, this field will show up with the table, however it cannot be edited and changes to that column are not allowed.

 

Question: When I try to use Replace in the Excel Edit features it says that it cannot be used when the sheet is locked, why can't I use the replace function?

Answer: The primary goal of the CONNX Data Task pane is to allow editing of data in a database table.  To accomplish this some of the excel functions that are normally available have been eliminated by locking the sheet.  For example filtering, sorting, adding or moving columns all would make it impossible to track changes, to disallow these functions the sheet must be locked.  The sheet being locked stops the replace function from working.