Why does adding attachments to access DB via script generate errors

Question

I'm writing a script that brings emails from an O365 mailbox into MS Access. Accessing the emails, and exporting the attachments all works fine. Records can be created in the DB with sender, date/time, subject, body etc.

However, I am unable to add the attachments into the DB's attachment field.

This line always generated a type mismatch error:

Set rsAttach = rstDocs.Fields("Attachments").Value

rstDocs already was used to update the other fields in the record - no issue.

But I need the rsAttach object to load the file attachment.

Already tried numerous variations of creating and defining the rsAttach object. I Tried the online examples and I'm basically as per the book from MSDN and other examples from this web site.

omEmail is the email object from Outlook. Its already saved in TmpPath when we get to here.

Set cn = CreateObject("ADODB.Connection")
Set rstDocs = CreateObject("ADODB.Recordset")
Set rsAttach= CreateObject("ADODB.Recordset")
cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='C:\data\CustService.accdb';Persist Security Info=False;"
cn.Open
rstDocs.Open "Documents;", cn, adOpenKeyset, adLockPessimistic, adCmdTable
rstDocs.AddNew
rstDocs("Sender").Value = omEmail.SenderEmailAddress
For Each Attachment In omEmail.Attachments
    Set rsAttach = rstDocs.Fields("Attachments").Value    ' ERROR HERE
    rsAttach.AddNew
    rsAttach.Fields("FileData").LoadFromFile TmpPath + Attachment.FileName
    rsAttach.Update
Next
rstDocs.Update
rstDocs.Close

Two thoughts: Are you sure that ADOBO is the right way? Many samples use DAO instead. And because you don't show the declaration of the variable `rsAttach` I just can suggest to not use it at all but a `With` block: `With rstDocs.Fields("Attachments")` `.AddNew` and so on... `End With`. — AHeyne, Oct 04 '19 at 06:59
@UnhandledException DAO is the older technology, ADO is what is commonly used nowadays ([related](http://www.databasedev.co.uk/ado_vs_dao.html)). — Ansgar Wiechers, Oct 04 '19 at 07:46
@Hardy Whenever you get a type error, the first debugging step is to check the actual type of a varaible/value (`WScript.Echo TypeName(rstDocs.Fields("Attachments").Value)`). The value probably isn't an object. In that case you must not use the `Set` keyword for assigning the value. — Ansgar Wiechers, Oct 04 '19 at 07:48

score 0 · Answer 1 · answered Oct 05 '19 at 18:45

rstDocs.Fields("Attachments").Value returns an ADO Field object, not a Recordset like you seem to be expecting, so you can't add a record to that field (rsAttach.AddNew).

If you want to store attachments as individual records, you can create a separate Attachments table in which you have an ID field that is the same as the ID of the e-mail record in rstDocs. Please take a look at this very similar question: Handling fields of Attachment type in MS Access using ADO

Documents table:

EmailID as Number
Sender as Text
Subject as Text
Date as Date/Time
...

Attachments table:

EmailID as Number
FileData as Number

Also, the ADO Field object does not have a LoadFromFile method. You will have do load the data and set it to the Field object's Value property.

Why does adding attachments to access DB via script generate errors

1 Answers1