Some years ago I wrote a function to save a DAO memo field in a Jet [Access] database. And in May 2001 I revisited the code and encapsulated it in a class module. I recently used the code in an application I'm writing so I decided to make the method the March 2000 tip-of-the-month.
Background - I usually use parameter lists with query definitions to insert or update a database. This works well with most data types but fails with DAO memo fields. So I needed a method to perform that task. Function 'SaveMemoField' was the result.
2003/01/23 - Updated to support ADO. See below to download the VB 6 code.
I created a VB 6 demo program to show how to use the encapsulated 'clsSaveMemo.bas' in an application. The demo program first creates a DAO 3.6, Jet 4 database [SampleSaveMemoField.mdb] with one table [tblMemoField] containing one ID field [GUID] and one memo field [sMemoField]. For more info about DAO 3.6 and Jet 4 databases see 'What I learned about DAO 3.6 and Jet 4'.
The demo program:
Text Box - Enter memo field data in the text box. Update Memo Field - Click this button to save the text box data. Get Memo Field's Data - Click this button to read the stored data.
Private Sub Command1_Click()
Dim objSaveMemo As New clsSaveMemo
On Local Error GoTo Command1_ClickEH
If .SaveMemoField(mdb, "tblMemoField", "sMemoField", Me.Text1.Text, "[GUID]=" & mGUID) Then
' Error if data contains a pipe character [|], changed to colon [:]
MsgBox Err.Description & vbCrLf & Err.Source, vbExclamation, "Error " & Hex(Err.Number)
You can't pass a single quote ['] in a string to a SQL statement. So the method 'SaveMemoField' first checks the data for single quotes and changes them to a pair of single quotes. The data is then properly stored in the database.
Data containing a pipe [|] character can't be saved using this facility. So pipe characters are changed to colons [:] before being saved. An error is raised if the data contains a pipe character. The demo issues the following warning message if a pipe character is found in the data:
Object Browser - Methods and Properties
|SaveMemoField||Save 'sValue' data in a DAO memo field.||If .SaveMemoField(mdb, "tblMemoField", "sMemoField", Me.Text1.Text, "[GUID]=" & mGUID) Then|
|mdb||An Open Database.||Set mdb = CreateDatabase(msDB, dbLangGeneral, dbVersion40)|
|sTableName||Name of the DAO table.||.sTableName = "tblMemoField"|
|sValue||The memo field data.||.sValue = "Some Long Text..........And More Text."|
|sCondition||The 'where' clause without 'Where="||.sCondition = "[GUID] = " & sGUID|
sTableName, sValue and sCondition can be passed while calling the method.
Add To Your Application
Add the following module to your application if you want to add 'Save Memo Field' support to your DAO application:
Module Description clsSaveMemo.cls Add support to your application for this tip.
The Demo Program Uses These Modules
Download VB 6 Source Code
Click here to download the DAO version of the VB 6 source code [9k]
Click here to download the ADO version of the VB 6 source code [2k] - no demo, just the class module. See: 'Create a VB Class to Process an Access Table using ADO' for a project that uses the class module.
Previous Tip Tip Index Next Tip First Tip