Bulk Inserts or Update for tables with Attachment fields

Since Access 2010, Access has supported Attachments data type which on the surface seems like a convenient feature for storing small images or files. However, a quick google search will usually show that they are best avoided. This all boils down to the fact that an Attachments data type is actually a Multi-Valued Field (MVF), and these come with several problems. For one, you’d be unable to use queries to insert or update several records in one go. Indeed, any tables that contains such data type force you to do a lot of code and for that reason alone, we avoid using such data types normally.

However, there’s a problem. We love using the image gallery and themes, both of which depend on a system table, MSysResources which unfortunately uses the attachment data types. This has created a problem for managing resources in our standard library because we want to use the MSysResources but we can’t easily update or insert them in bulk.

The attachment data type (as well as MVFs) forces you to use “row-by-agonizing-row” programming when dealing with a MVF field, it’s a twofer with Attachments field because you would have to use the LoadFromFile or SaveToFile methods. Microsoft has an article with examples about those methods. Thus, you must interact with the filesystem when adding new records. Not always desirable in all situations. Now, if we’re copying from one table to another table, we can avoid bouncing over the filesystem by doing something like:

Dim SourceParentRs As DAO.Recordset2
Dim SourceChildRs As DAO.Recordset2
Dim TargetParentRs As DAO.Recordset2
Dim TargetChildRs As DAO.Recordset2
Dim SourceField As DAO.Field2

Set SourceParentRs = db.OpenRecordset("TableWithAttachmentField", dbOpenDynaset)
Set TargetParentRs = db.OpenRecordset("AnotherTableWithAttachmentField", dbOpenDynaset, dbAppendOnly)

Do Until SourceParentRs.EOF
  TargetParentRs.AddNew
  For Each SourceField In SourceParentRs.Fields
    If SourceField.Type <> dbAttachment Then
      TargetParentRs.Fields(SourceField.Name).Value = SourceField.Value
    End If
  Next

  TargetParentRs.Update 'Must save record first before can edit MVF fields
  TargetParentRs.Bookmark = TargetParentRs.LastModified
  Set SourceChildRs = SourceParentRs.Fields("Data").Value
  Set TargetChildRs = TargetParentRs.Fields("Data").Value
  Do Until SourcechildRs.EOF
    TargetChildRs.AddNew
    Const ChunkSize As Long = 32768
    Dim TotalSize As Long
    Dim Offset As Long

    TotalSize = SourceChildRs.Fields("FileData").FieldSize
    Offset = TotalSize Mod ChunkSize
    TargetChildRs.Fields("FileData").AppendChunk(SourceChildRs.GetChunk(0, Offset)
    Do Until Offset > TotalSize
      TargetChildRs.Fields("FileData").AppendChunk(SourceChildRs.GetChunk(Offset, ChunkSize)
      Offset = Offset + ChunkSize
    Loop
    TargetChildRs.Update
    SourceChildRs.MoveNext
  Loop
  TargetParentRs.Update
  SourceParentRs.MoveNext
Loop

Holy looping, batman! That’s a lot of code, all just to copy attachments from one table to another. Even though we don’t bounce over the filesystem, it’s also very slow. In our experience, a table with 1000 records containing a single attachment can take minutes just to process. Now, this is quite outsized when you consider the size. The table with the attachments is not that big. In fact, let’s do an experiment. Let’s see what happens if I copy and paste via datasheet:

So copying and pasting is practically instantaneous. Obviously the code used by pasting is not the same code that we would use in VBA. However, we’re big believer that if we can do it interactively, we can do it in VBA, too. Can we replicate the speed of interactive pasting in VBA? The answer turns out to be yes, we can!

Speed up with …. XML?

Surprisingly the method that provides the fastest way to copy data, including attachments is via XML files. I will admit I don’t reach for XML files except as a workaround for limitations. On average, XML files are relatively slow to other file formats but in this case, XML has one huge advantage; it has no problem describing MVFs. Let’s create an XML file and investigate the capabilities we get with importing/exporting an XML file.

After the usual export wizard dialog to set the path to save the XML file, we will get a dialog like this:

If we then click “More Option…” button, we get this dialog instead:

From this dialog, we see few more clues about what is possible; namely:

• We have the option to export the entire table or only a subset of the table by applying a filter
• We can transform the XML output.
• We can describe the schema in addition to the contents of the table.

I find that it’s best to embed the schema; the default is to export it but as a separate file. However, that can be error-prone and they can forget to include the XSD file with the XML file. This can be changed via the schema tab shown:

Let’s finish exporting it and take a quick look at the resulting XML file’s data.

Note that the attachments are described within the Data subtree and file contents is base-64 encoded. Let’s try importing the XML file. After going through the import wizard, we will get this dialog:

Take note of following features:

• As with export, we have the option to transform the XML.
• We can control whether to import the structure, the data or both

If we then finish importing the XML file, we find that it’s just as fast as the copy’n’paste operation we did.

We now know there’s a better path to copying several records with attachments. But in this situation, we want to do this programmatically, rather than interactively. Can we do the same thing we just did? Again, the answer is yes. There are multiple ways to do the same thing but I think the easiest method is to use the 3 new methods that were added to the Application object since Access 2010:

• ExportXML method
• TransformXML method
• ImportXML method

Note that that the ExportXML method supports exporting from various objects. However, because the objective here is to be able to copy or update en masse the records of a table with an attachment fields, the best object type for us to use is a saved query. With a saved query, we can control which rows should be inserted or updated and we can also shape the output. If you look at the schema design of the MSysResources table below:

There’s a potential problem. Whenever we use themes or images, we reference the item by name, not by the ID. However, the Name column is not unique and is not the primary key of the table. Therefore, when we add or update records, we want to match on the Name column, not the Id column. This means when we export, we probably should not include the Id column and we should export only the unique list of the Name to ensure that the resources don’t suddenly go from “Open.png” to “Close.png” or something silly.

We’ll then create a query to act as the source for the records we want to import into the MSysResources table. Let’s start with this SQL just to demonstrate the filtering down to a subset of records:

SELECT e.Data, e.Extension, e.Name, e.Type
FROM Example AS e
WHERE e.Name In ("blue","red","green");

We will then save it as qryResourcesExport. We can then write VBA code to export XML:

Application.ExportXML _
  ObjectType:=acExportQuery, _
  DataSource:="qryResourcesExport", _
  DataTarget:="C:\Path\to\Resources.xml", _
  OtherFlags:=acEmbedSchema

This emulates the export we originally did interactively.

However, if we then import the resulting XML, we only have the option of appending data to an existing table. We cannot control which table it will append into; it will find a table or query table by the same name (e.g. qryResourcesExport and append records into that query. If the query is updatable, then there is no problem and it will insert into the Example which the query is based on. But what if the source query we use is non-updatable or may not exist? In either cases, we would be unable to import the XML file as-is. It could either fail to import or end up creating a new table named qryResourcesExport which doesn’t help us. And what about the case of copying data from Example to MSysResources? We don’t want to append data to the Example table.

That’s where the TransformXML method comes to rescue. A full discussion about how to write an XML transformation is beyond the scope but you should be able to find ample resources on how to write an XSLT stylesheet to describe the transformation. There are several online tools you can use for validating your XSLT as well. Here’s one. For the simple case where we just want to control which table the XML file should append the records into, you can get started with this XSLT file. You can then run the following VBA code:

Application.TransformXML _
  DataSource:="C:\Path\to\Resources.xml", _
  TransformSource:="C:\Path\to\ResourcesTransform.xslt", _
  OutputTarget:="C:\Path\to\Resources.xml", _
  WellFormedXMLOutput:=True, _
  ScriptOption:=acEnableScript

We can replace the original XML file with the transformed XML file, which will now insert into the MSysResources table rather than into (possibly non-existent query/table) qryResourcesExport.

We then need to handle the updates. Because we are actually appending new records, and the MSysResources table does not have any constraints on the duplicate names, we need to ensure that any existing records with same names are first deleted. This can be accomplished by writing an equivalent query like so:

DELETE FROM MSysResources AS r
WHERE r.Name In ("blue","red","green");

then running it first before running the VBA code:

Application.ImportXML DataSource:="C:\Path\to\Resources.xml", ImportOptions:=acAppendData

Because the XML file was transformed, the ImportXML method will now insert the data into the MSysResources table rather than the original query that we used with the ExportXML method. We specify that it should append data into an existing table. However, if the table does not exist, it will be created.

And with that, we’ve achieved a mass update/insert of the table with an attachment field which is much faster in comparison to the original recordset-and-child-recordset VBA code. Hope that helps! Also, if you need help with developing Access applications, feel free to contact us!