Excel Database Tasks - Version 2.5 Released
Stable & blazing fast!
- New features
- Merge formatted value
- Automated upgrade
- Key option when no primary key available
- MySQL & MS Access: Edit and Insert support
- Other changes
- Bug fixes
- Conclusion & Next version
Merge formatted value
A new feature has been added to enable the merging of the Excel number format applied, using the EDT option 'Merge formatted value'.
Previously EDT would merge the underlying value, rather than the displayed value.
This feature is useful, for example, when you wish to control decimal places, display big integer values or handle different date formats.
To merge the displayed value, check the Merge formatted value option, available for both column values and parameter values:
New version upgrade options :
- upgrade check can be via LeanSoftware.net or by comparing to a network version
- multiple client workstations can be kept up to date from a central network folder
- upgrade either automatically (no prompt) or on user confirmation.
- EDT will check for upgrade availability upon start-up
Key option when no primary key available
Certain ODBC drivers do not provide any primary key information, MySQL for example.
The generate Edit SQL functionality requires at least one primary key column.
For this reason a new 'Key' checkbox is used to define the primary key when the source table primary key could not be detected:
Screenshot, showing key selection and generated WHERE clause:
MySQL & MS Access - Edit and Insert table support
MySQL and Access will only support one command at a time, and does not support procedural type script statements outside of stored procedures, for example IF <clause> THEN <do this>.
An Update or Insert template (as shown in the above example) requires two statements: firstly an UPDATE, followed by an INSERT (in MySQL the IGNORE keyword ensures the statement does nothing if the row exists).
We have added a new keyword that can be used in any merge script, this is '#GO#', and essentially splits the SQL into separate commands. The command are sent to the database one after the other.
The same principle is used for the SQL generated for Microsoft Access, although the syntax used for MS Access has to be a little more convoluted, as shown below:
Screenshot: Microsoft access update/insert syntax
- Button layout improved to maximize available work space
- Button spacing equal when Clear/Script/Help options enabled/disabled
- Performance improvements - in tests load and validate 20,000 rows in approx 4 seconds
- 'Hide Excel menus' workstation option now functions for all versions of Excel
- Could not edit or paste into connection string when initially creating a task
- If user cancelled Configuration form, then re-loaded the selected task - message 'could not connect to database' was received
- If a data source returned no data, validation would not trigger when manually pasting in new data
- Button layout problems with certain screen resolutions
- Microsoft bug 'buttons re-size on resolution change' further addressed
- Standard SQL security (user name and password) connections would caused 'connection error'
- When a calculated columns value changes depending on another column, it was not automatically re-sized when manually entering or pasting data
- 'Test SQL Merge' button within the Configuration Worksheet tab button was not visible
- If the connection string contained Task parameters - the merged connection string was incorrect
- Pasting a value into a task parameter would lock the task parameter cell
Conclusion & Next version
In conclusion we believe Excel Database Tasks version 2.5 to be stable, function rich and optimized for speed. We hope you find the new features useful.
We are currently completing the SQL Lookup column functionality - allowing easy integration with existing relational / normalized data.
Following the release of SQL Lookup functionality, we will be completing the online help and generally improving the web site with examples and case studies.
Available from the downloads area of the web site.
Regards to everyone
Lean Software team