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
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:
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
MSysResources? We don’t want to append data to the
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)
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!
Good article. And yes, I tell my students to avoid both MVFs and attachments like the plague, but this is good to know!
Compare “However, the Name column is not unique and is not the primary key of the table.” with
“we should export only the unique list of the Name”
So is the Name column unique or not?
The point was that because the
MSysResourcestable does not enforce uniqueness on the
Namecolumn which is strange because we use the name of the image when we use it in our forms or reports. Therefore, if it contained two rows with name of
blue, which one should be used? How do we know? For that reason, you need to be careful to not add records with duplicate names and if there are, to clean it up yourself. You cannot use
DISTINCTin your exporting query because that is not allowed when selecting attachment fields. Even if you could, it doesn’t help because if you have two records with name of
blueand each has a different image, which should be selected? For those reasons, it will have to be your responsibility to make sure that you are not accidentally including duplicates in your exporting query to avoid problems when using the images from the shared gallery.
I hope that help clarifies. Thanks for asking!