Good evening, all! Up front, any help...at all...would be greatly appreciated. Thank you in advance. Other than the "Reader's Digest Version" piece I provide below, I apologize for the length of this post. I just wanted to make sure whoever decides to help has all the pertinent information up front and I don't want to waste anyone's valuable time by leaving out something important.
READER'S DIGEST VERSION: Is it possible to send an OLEObject .txt/.csv file that is embedded in a Word 2010 document (contained in a legacy bookmark) to a field in an Access 2010 table that is identified as an attachment field, or any other field type that can store and display the OLEObject in its native form within Access?
CAVEAT: I do not fancy myself a VBA expert, or even adept for that matter, but through much research and persistence I have taught myself how to do quite a bit to reach my automation/coding goals...mostly by cobbling together answers related to others' posts; however, I am frequently humbled by the immense complexity of VBA and the incredible brain-trust often found within these forums, and I have searched high-and-low for a solution to this problem with no success. Perhaps there is none. So, if my question or approach seem stupid, it's probably because I am
BACKGROUND: I've been working on a fairly complicated Word 2010 document template designed to capture specific end-user content in many plain-text contentcontrols throughout said document (which is a user Request Form)(.docx). When that completed document (.docx) is sent to the record manager (via email and who has a duplicate of the original template to be able to utilize macros), he/she reviews it for accuracy and completeness and then selects a macro button I've included in a custom QAT that sends all the information contained within the contentcontrols to corresponding fields in an Access 2010 table (.accdb) where the requests are ultimately managed. All of that works, after almost 1 year of trial and error (and spending huge amounts of time in various Word VBA forums and Superman Greg Maxey's site) figuring out the VBA macros (all digitally signed with a verified PKI certificate) needed to accomplish what I wanted done. This is when my nightmare began. Because of limited physical space on the Request Form, I needed a way for the user to embed either a plain-text file or .csv file into the document itself in lieu of entering text in the form's prescribed, but limited, space (assuming they might need more space then what is available). I have figured out the manner in which to embed the .txt or .csv file as an OLEObject into a bookmark titled "bkmAttachmentPoint", and I have figured out how to declare all the contentcontrols for regular text fields in the document, and I have figured out how to pass the text contained within the plain-text contentcontrols to the Access database (using INSERT INTO). What I cannot figure out is how to declare the embedded .txt/.csv files (the OLEObjects) embedded into the bookmark and pass them to the Access database.
As a side note, I am using a bookmark to "house" the OLEObjects, because when I had it as a plain text contentcontrol I was able to embed the txt/csv files but Word complained of not being able to read the content when subsequently opening the document. The bookmark approach seems to avoid that.
One other note, this form and accompanying database will eventually be on a closed network, so I have no concerns with regards to SQL injection attacks, macro malware, or anything else. Just thought I'd throw that out there.
I am including a sampling of the code I currently have that passes all the plain text-based contentcontrol data from the document to the database in an effort to help make any assistance easier. I say "sampling" because the number of fields within the document that must be passed to the database are many and results in over 100 lines of code and I don't want to blow up the Internet. Again, everything currently written to date does exactly as I want it to. I have no error messages to offer regarding passing the OLEObject embedded .txt/.csv files to the database as I don't even know where to begin.
Code:
Sub SubmitRequestForm()
'This sub-routine will transfer deconfliction request data from this form to
'the tblReviewHolding table in the Deconfliction database.
'Declare all relevant variables from the Request Form
Dim cc As ContentControl
Dim rng As Word.Range
Dim cnn As ADODB.Connection
Dim strConnection As String
Dim strSQL As String
Dim strPath As String
Dim strClassForm As String
Dim strReqAgency As String
Dim bytContinue As Byte
Dim lngSuccess As Long
'Identify all the relevant ContentControl data fields on the Request Form
strClassForm = ActiveDocument.SelectContentControlsByTitle("ClassForm").Item(1).Range.Text
strReqAgency = ActiveDocument.SelectContentControlsByTitle("ReqAgency").Item(1).Range.Text
'Confirm new record.
bytContinue = MsgBox("By selecting YES you will submit this deconfliction request" _
& " to the database request queue. Please be sure you have properly reviewed" _
& " this request prior to submission. Once submitted, changes to this data can only" _
& " be made by a database administrator.", vbYesNo + vbExclamation, "Submit Deconfliction Request to Database")
Debug.Print bytContinue
'Process input values and send the results to the database in the appropriate fields
If bytContinue = vbYes Then
strSQL = "INSERT INTO tblReviewHolding([Classification], [RequestingAgency]) VALUES(""" _
& strClassForm & """, """ _
& strReqAgency & """)"
Debug.Print strSQL
strPath = "{Placeholder for actual location of database}.accdb" 'Location masked for this posting
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source = " & strPath
Debug.Print strConnection
Set cnn = New ADODB.Connection
cnn.Open strConnection
cnn.Execute strSQL, lngSuccess
cnn.Close
End Sub
Thank you.
~Eric