Attaching an Attachment in ACCESS SQL

[ChkOnByts]

Hi!

I'm new here - I've been searching through the web and other ACCESS DBs for a solution and believe there probably is not one (in the way I want it).

Small Non-Profit, using ACCESS 2013 - DB purpose is to store scanned receipts (already battled with Financial to have receipts stored on a drive).
Each new year, the Receipt table will be wiped and new receipts will be stored table.

Working on an INSERT statement, which normally works well, this is the first time I have add an attachment (as most times I've used links to get to the attached file).

Most controls on the form are unbound so I can load, update and error check them as needed. The problem - is that I cannot find how to use SQL to store the receipt file to the table.

This is the INSERT statement I'm using:

sMySQL = "INSERT INTO Receipts "
        sMySQL = sMySQL & "([EntryDate], [Purchaser], [ReceiptDate], [ReceiptVendor], ReceiptAmt, ItemList, Reason, Company, InternalCode, ReceiptPayment, receipt) "
        sMySQL = sMySQL & "Values "
        sMySQL = sMySQL & "('" & sEntDate & "', '" & sEmp & "', '" & sRctDate & "', '" & sVendor & "', " & Me.txtAmt & ", '" & sItems & "', '" & sReason & "', " & iCompany & ", " & cmbChrtAccts.Column(1) & ", '" & Me.cmbPay & "', " & Me.Receipt & ");"
 

Then I perform a:
DoCmd.RunSQL sMySQL

Little to my surprise, I receive a "Run-time error '3134': Syntax error in INSERT INTO statement", the SQL statement doesn't see the file. There's a blank at the end of the data and hence, the syntax error.

This is what 'sMySQL' equals at run time:
INSERT INTO Receipts ([EntryDate], [Purchaser], [ReceiptDate], [ReceiptVendor], ReceiptAmt, ItemList, Reason, Company, InternalCode, ReceiptPayment, receipt)
Values
('20151007', 'Vance, Susan', '20150912', 'See' & Chr(39) & 's Candies', 16.2, 'Nuts', 'Wanted to bring something home as a treat', 0, 2003, 'AMEX1003', );

I appreciate any and all help or suggestions on how this can be done if not by SQL.

Kind regards,
Mike

[zmbd]

AFAIK: It is not possible to use SQL to add an file to the attachment field. The last time I played with these, the INSERT action query choked on Lookup/MultiValue/Attachment fields if listed.

IMHO: It is usually not a good idea to store attachments within an Access Database (table capacity>Post#3 ).

You either have to use the attachment control on the form or use VBA and LoadFromFIle and SaveToFile methods.
You might find this thread interesting: How To Manipulate Attachment Fields > Post #6 and the follow-up post#8

You indicate that in the past you've simply used links to the file - IMHO, this would still be the best method to handle the scanned receipts. Once scanned, have a few people with user accounts that can move the scanned file to a directory where only these individuals have read/write/modify security privileges via the operating system. Give the "everyone" user read only privileges.

You could also store a SHA2 or MD5 digest of the file along with the URL to the file. This way, if the file is changed, you have a way to prove this fact (provided of course that no-one replaces the digest of the old with the new :) )