At present I’m working with the new version of my commercial add-in to Excel – SQL Tester. I took the decision to completely re-write it in VB.NET, i e it will be a managed COM Add-ins for Excel 2000 and later. Hopefully it will be available by the end of June.
Anyway, in some services it offer it requires to handling strings variables that exceed 255 characters. In general large text data is not an issue but it becomes an issue when we want to write (save) them to tables in Microsoft Databases (aka Access Databases).
To handle large text data we need to add fields of the type ‘Memo’ (some developers call it “Blob”) to the targeting tables. Compared with the Text field type, which can only store <=255 characters, the Memo field type can store up to about 60,000 characters.
Next we need to consider it when creating the code that writes the large text data to the database. By using a name for the parameter in the SQL statement and by adding a Parameter Object it’s solved in an easily way.
The following example shows how it can be solved and here I use a class that contains the relevant function (one of many functions in the class).
The function requires 4 strings variables where the last one – ‘Code’ – contains the large textdata.
The SQL statement includes 4 fields where the ‘Code_Text’ represents the Memo field and the statement ends with the name ‘@Code_Text’. In the final step the Parameter object is created and the command is executed.
Most controls that offer databinding can handle large text data well. In the present solution I use a control that is first populated with the large text data and it’s easy to get the large text data from that control. However, if it turns out that I need to retrieve the large text data directly from the table I will publish the solution here.
However, if someone already has a solution for it then please posts it here.
If You want a copy of the code it can be downloaded here.