How do I concatenate Memo fields in Access using VBA?

Question

I am migrating data from one data model to another and in doing so I need to concatenate a number of different Memo fields into one in order to prevent any data loss.

I'm not very familiar with VBA but I understand that using an Update query limits my Memo fields to 255 characters.

Can someone please provide some insight into how I should tackle this using VBA.

Example of what I'd like to achieve:

The comments for each record will be a result of concatenating 3 Memo fields: [Comments] = [Memo1] & [Memo2] & [Memo3]

Answer 1

Load your table as a DAO.Recordset and update the Comments field in each row.

Although this is a RBAR (row by agonizing row) approach, since it's for a migration, hopefully it will be something you need do only once. The main benefit is it won't choke with text amounts greater than 255 characters.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("YourTable", dbOpenTable)
With rs
    Do While Not .EOF
        .Edit
        !Comments.Value = !Memo1.Value & !Memo2.Value & !Memo3.Value
        .Update
        .MoveNext
    Loop
    .Close
End With