Save Memo Field - March 2002

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.

The Demo Program

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.

Sample Calling Routine

Private Sub Command1_Click()
    Dim objSaveMemo As New clsSaveMemo
    On Local Error GoTo Command1_ClickEH
    With objSaveMemo
        If .SaveMemoField(mdb, "tblMemoField", "sMemoField", Me.Text1.Text, "[GUID]=" & mGUID) Then
            ' Success
        End If
    End With
    Exit Sub
    ' Error if data contains a pipe character [|], changed to colon [:]
    MsgBox Err.Description & vbCrLf & Err.Source, vbExclamation, "Error " & Hex(Err.Number)
End Sub

Single Quote In Data

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.

Pipe Character

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

Methods Description Example
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

Module - Click for More Info Description Used in the Demo To
modCreateGUID Create a GUID Create an ID for the one database record.
modConnectToWebSite.bas Connect to a web site Show this info when Help/Info is
selected from the demo.

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


Updated 2003/01/23