Embed PDF file in bound control
- Thread starter tokoloshi
- Start date
Mike375
The core of your solution lies in your FileToBlob function, which also calls your ReadToBinary function or ReadToText function, depending on the type of data being transferred.
The relevant portion of the code is:
======
Case adLongVarBinary
If FileSize > Threshold Then
ReadToBinary f, fld, FileSize
Else
Data = InputB(FileSize, f)
fld.Value = Data
End If
======
along with the fld.AppendChunk Data command in the ReadToBinary procedure.
This does not work at all since the ole field is left empty after the routine has run.
Perhaps this has to do with Access 2007, I am not certain. I probably could spend time debugging it, but it does not meet my needs at this point in time.
There are quite a few things I liked about your approach, but the major problem is that I need to insert the document as an Acrobat Exchange document that will allow me to display the Adobe icon and will automatically launch it when double clicked.
What I have done is to test that the following T-SQL code works from within my SQL Server backend:
============
INSERT INTO dbo.tbl_Documents
(
DivisionID,
PersonID,
DocTypeID,
DocDate,
DocSubject,
newDocument
)
select
dbo.dcd() as DivisionID,
285 as PersonID,
13 as DocTypeID,
getdate() as DocDate,
'This is a test' as DocSubject,
(SELECT *
From OPENROWSET
(BULK N'C:\Documents\Document285.pdf', SINGLE_BLOB) as EmbedDoc)
AS newDocument
============
It successfully places the document with all fields correctly updated into the DB. This is very similar to your solution in that it is a bulk upload of the binary file into the DB.
The problem then is that the field, when double-clicked reports an error with the OLE server or activex control. The binary content is then seen as "unknown".
The solution seems to point to the correct use of the ROWSET command in that it will take a driver or connection string that will point at the application that owns the data being inserted.
You can for example tell SQL Server that you are placing information from Excel into a given column through the following :
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Zip Code1.xls;HDR=YES',
'SELECT ZipCode,CITY,Address FROM [Sheet1$]')
The important bit being the reference to:
'Microsoft.Jet.OLEDB.4.0','Excel 8.0;
My thinking is therefore that there must be a similar reference to AcroExchange or Adobe or something like that which can be used in the RowSet command.
The other obvious question is how to push the full sql string using the rowset command into Access using the docmd.runSQL command. It currently doesn't like me much when I try it
Perhaps you can think about how the appendchunk method can take a similar parameter? Maybe there is a way to also let Access know that the stored document is of a specific type?
Sean
============================================
So, onwards, upwards and always moving ahead
The core of your solution lies in your FileToBlob function, which also calls your ReadToBinary function or ReadToText function, depending on the type of data being transferred.
The relevant portion of the code is:
======
Case adLongVarBinary
If FileSize > Threshold Then
ReadToBinary f, fld, FileSize
Else
Data = InputB(FileSize, f)
fld.Value = Data
End If
======
along with the fld.AppendChunk Data command in the ReadToBinary procedure.
This does not work at all since the ole field is left empty after the routine has run.
Perhaps this has to do with Access 2007, I am not certain. I probably could spend time debugging it, but it does not meet my needs at this point in time.
There are quite a few things I liked about your approach, but the major problem is that I need to insert the document as an Acrobat Exchange document that will allow me to display the Adobe icon and will automatically launch it when double clicked.
What I have done is to test that the following T-SQL code works from within my SQL Server backend:
============
INSERT INTO dbo.tbl_Documents
(
DivisionID,
PersonID,
DocTypeID,
DocDate,
DocSubject,
newDocument
)
select
dbo.dcd() as DivisionID,
285 as PersonID,
13 as DocTypeID,
getdate() as DocDate,
'This is a test' as DocSubject,
(SELECT *
From OPENROWSET
(BULK N'C:\Documents\Document285.pdf', SINGLE_BLOB) as EmbedDoc)
AS newDocument
============
It successfully places the document with all fields correctly updated into the DB. This is very similar to your solution in that it is a bulk upload of the binary file into the DB.
The problem then is that the field, when double-clicked reports an error with the OLE server or activex control. The binary content is then seen as "unknown".
The solution seems to point to the correct use of the ROWSET command in that it will take a driver or connection string that will point at the application that owns the data being inserted.
You can for example tell SQL Server that you are placing information from Excel into a given column through the following :
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Zip Code1.xls;HDR=YES',
'SELECT ZipCode,CITY,Address FROM [Sheet1$]')
The important bit being the reference to:
'Microsoft.Jet.OLEDB.4.0','Excel 8.0;
My thinking is therefore that there must be a similar reference to AcroExchange or Adobe or something like that which can be used in the RowSet command.
The other obvious question is how to push the full sql string using the rowset command into Access using the docmd.runSQL command. It currently doesn't like me much when I try it
Perhaps you can think about how the appendchunk method can take a similar parameter? Maybe there is a way to also let Access know that the stored document is of a specific type?
Sean
============================================
So, onwards, upwards and always moving ahead
It embeds for me (A2003) with no problem. But when I open the file I can't close acrobat. But I have that same problem if I do manual, that is, Insert Object or Copy/Paste. No problems with Word or Excel files.
DCrake
Remembered
- Local time
- Today, 14:17
- Joined
- Jun 8, 2005
- Messages
- 8,626
The solution I posted was untested in Access 2007 and as this was a solution for 2003 there was no need at the time to test in 2007. I would have thought that the actual tables did not differ in 2007, apparantly they do.
David
David
Has anyone tried the code that I posted originally and looked at the .Class = "AcroExch.Document" section?
The problem as I mentioned was with regards to the class of document, which confirms what I found out with the RowSet() exercise mentioned earlier - that I need to push some kind of class definition across to the DB when calling the acOLECreateEmbed action.
As I mentioned in that post, I receive the following errors:
---------------------------------------
?cstr(err): 2777
?Err.Description
The class argument in the CreateObject function of the Visual Basic
procedure you're trying to run is invalid.
---------------------------------------
I had posted a reasonably complete code set at the start of this thread and I needed help on how to debug this particular error.
The code again, with comments, is as follows:
*** Scenario ***
1. Autonum field
I have a form bound to a table that has 4 fields. The ID field is autonumber so I do not need to put a value therein.
2. DocumentTypeID
The second field is a documentTypeID that is a foreign key to a Document Type table. I place the value of 13 into that field as follows:
Forms![PDFForm]![DocTypeID] = 13
3. Document Subject
I then place a value into the subject field. In production this will be a concatenation of several fields on the current form.
Forms![PDFForm]![DocSubject] = "My new PDF document"
4. Embedded Object
Okay, so this is where the fun starts ...
I have to set several values of the embedded document before creating it.
To do this I reference the embeddedDoc field on my form as follows:
With Forms![PDFForm]![embeddedDoc]
I then have to set the enabled & locked values - just in case these values may have been changed by another piece of code or another developer in the team. The embedded bound control requires that it must be enabled and not locked if I wish to update it so...
.Enabled = True
.Locked = False
Next, since I intend for this object to be embedded and not linked, I set the OLETypeAllowed to acOLEEmbedded instead of acOLEEither or acOLELinked
.OLETypeAllowed = acOLEEmbedded
This now at last brings me to the reason for asking for help
.
If this was to be a purely binary storing process where I would want to write the contents out of the field back to the file system then no problem, but I actually need to specify the class of the document here because I want to use it exactly as I would have used it manually... so...
.Class = "AcroExch.Document"
Okay, so all that is left is for me to tell it where my file is. I have passed the variable varFile into this procedure for the calling process. I now associate the .SourceDoc property with the variable like so:
.SourceDoc = varFile
Cool. So far no errors. Now let's dance.
.Action = acOLECreateEmbed <-- Error generated here
The problem as I mentioned was with regards to the class of document, which confirms what I found out with the RowSet() exercise mentioned earlier - that I need to push some kind of class definition across to the DB when calling the acOLECreateEmbed action.
As I mentioned in that post, I receive the following errors:
---------------------------------------
?cstr(err): 2777
?Err.Description
The class argument in the CreateObject function of the Visual Basic
procedure you're trying to run is invalid.
---------------------------------------
I had posted a reasonably complete code set at the start of this thread and I needed help on how to debug this particular error.
The code again, with comments, is as follows:
*** Scenario ***
1. Autonum field
I have a form bound to a table that has 4 fields. The ID field is autonumber so I do not need to put a value therein.
2. DocumentTypeID
The second field is a documentTypeID that is a foreign key to a Document Type table. I place the value of 13 into that field as follows:
Forms![PDFForm]![DocTypeID] = 13
3. Document Subject
I then place a value into the subject field. In production this will be a concatenation of several fields on the current form.
Forms![PDFForm]![DocSubject] = "My new PDF document"
4. Embedded Object
Okay, so this is where the fun starts ...
I have to set several values of the embedded document before creating it.
To do this I reference the embeddedDoc field on my form as follows:
With Forms![PDFForm]![embeddedDoc]
I then have to set the enabled & locked values - just in case these values may have been changed by another piece of code or another developer in the team. The embedded bound control requires that it must be enabled and not locked if I wish to update it so...
.Enabled = True
.Locked = False
Next, since I intend for this object to be embedded and not linked, I set the OLETypeAllowed to acOLEEmbedded instead of acOLEEither or acOLELinked
.OLETypeAllowed = acOLEEmbedded
This now at last brings me to the reason for asking for help
.If this was to be a purely binary storing process where I would want to write the contents out of the field back to the file system then no problem, but I actually need to specify the class of the document here because I want to use it exactly as I would have used it manually... so...
.Class = "AcroExch.Document"
Okay, so all that is left is for me to tell it where my file is. I have passed the variable varFile into this procedure for the calling process. I now associate the .SourceDoc property with the variable like so:
.SourceDoc = varFile
Cool. So far no errors. Now let's dance.
.Action = acOLECreateEmbed <-- Error generated here
I did not try it because you said it did not work. Refer to your post.
I think you are over complicating
The simple code I put up will embed a file. Hopefully tomorrow I can try it on someone else's computers as I obviously have a problem with PDF......I have problems closing Acrobat from opening in OLE, no matter how it was put there.
What would be good is if David could post up the code he would use as distinct from a DB.
The embedding is simple and if David puts up code to embed in OLE it will be as simple as I have posted.
DCrake
Remembered
- Local time
- Today, 14:17
- Joined
- Jun 8, 2005
- Messages
- 8,626
Mike
As you arre aware I use the File To Blob technique derived from a Microsoft article. It may be well seing if the KB has a version for A2007.
here is the link
Filt To Blob
As you arre aware I use the File To Blob technique derived from a Microsoft article. It may be well seing if the KB has a version for A2007.
here is the link
Filt To Blob
Yep, I went through all this a few weeks ago. I will try and find the thread where the Mailman was being my sounding board....and of course with a few tips for direction
But this embeds
With Forms!formolelink![OLEBound7]
.Enabled = True
.Locked = False
.OLETypeAllowed = acOLEEmbedded
.SourceDoc = ("C:\Letters\" & Format([Forms]![formolelink]![LNameNoDoc]) + ".pdf")
.Action = acOLECreateEmbed
.SizeMode = acOLESizeZoom
End With
Exit Sub
Try it.
The above embeds and Yes you will see it in the field
This will open it.......embedded or linked
Forms![formolelink]!OLEBound7.Verb = -2
Forms![formolelink]!OLEBound7.Action = 7
But the embedding is not so it can be seen, it is for installing.
See my attached DB as example for Word. The function is called as a form moves from record to record and is installing Word.docs. The key to embedding is that the data base can install everything and is self reliant.
But I normally just do Insert Object. But a few weeks ago I was able to do "code to link" as a by product of something else and then do embed For Word it was easy because "copy" was in the code used for bookmarks/printing and so just a "paste" in the code.
If I knew how to open a PDF with VBA and so I could copy the opened document (as I do with Word) then the embedding would just be a "paste". Although with my computers, for some reason, I would still be unable to close Acrobat when opened from OLE.
This will open it.......embedded or linked
Forms![formolelink]!OLEBound7.Verb = -2
Forms![formolelink]!OLEBound7.Action = 7
But the embedding is not so it can be seen, it is for installing.
See my attached DB as example for Word. The function is called as a form moves from record to record and is installing Word.docs. The key to embedding is that the data base can install everything and is self reliant.
But I normally just do Insert Object. But a few weeks ago I was able to do "code to link" as a by product of something else and then do embed
For Word it was easy because "copy" was in the code used for bookmarks/printing and so just a "paste" in the code.If I knew how to open a PDF with VBA and so I could copy the opened document (as I do with Word) then the embedding would just be a "paste". Although with my computers, for some reason, I would still be unable to close Acrobat when opened from OLE.
PC User
Registered User.
- Local time
- Today, 06:17
- Joined
- Jul 28, 2002
- Messages
- 193
I asked a similar question and I have some examples in the post, if you would like some additional information. See this posting.
Sample embedded application
Okay, this is awkward.
All of the code works. In other words, the sample code that I first posted all works
The problem is that the varFile variable I created was not well formed and so broke the app.
I have put together a quick sample app with the complete picture of what I had wanted to do. This is obviously a subset of my app without any dependencies.
I have also saved it in Access 97/2k3 format.
With Office 2007 you have the opportunity to install a Save-As PDF option, which is what the application uses, you may need to amend that to point at a different method of creating a PDF if you are not a fan of Office 2007.
Extract the MDB file to a C:\debtman\EmbedMe\ folder and it should work immediately.
Take a look at this app and then I will continue figuring out what my actual problem is.
I did this because I wanted to test my code outside of my current application. My application is still broken, so it is obviously not this code
I am therefore still where I was a couple of days ago, just now I need to figure out what the heck is happening between the app, SQL Server and the .Class property.
It seems like the .Class property is "sticky" in that it seems to "remember" a setting or something. I don't know yet. - will keep you all posted.
Thanks for the effort & excellent advice from everyone though.
Sean
Okay, this is awkward.
All of the code works. In other words, the sample code that I first posted all works
The problem is that the varFile variable I created was not well formed and so broke the app.
I have put together a quick sample app with the complete picture of what I had wanted to do. This is obviously a subset of my app without any dependencies.
I have also saved it in Access 97/2k3 format.
With Office 2007 you have the opportunity to install a Save-As PDF option, which is what the application uses, you may need to amend that to point at a different method of creating a PDF if you are not a fan of Office 2007.
Extract the MDB file to a C:\debtman\EmbedMe\ folder and it should work immediately.
Take a look at this app and then I will continue figuring out what my actual problem is.
I did this because I wanted to test my code outside of my current application. My application is still broken, so it is obviously not this code
I am therefore still where I was a couple of days ago, just now I need to figure out what the heck is happening between the app, SQL Server and the .Class property.
It seems like the .Class property is "sticky" in that it seems to "remember" a setting or something. I don't know yet. - will keep you all posted.
Thanks for the effort & excellent advice from everyone though.
Sean
Re: Embed PDF file in bound control - Final
Okay, after a week of frustration I have worked out all of the possible reasons for my code not working and have a documented process for moving forward.
To recap:
The scenario is that I want to embed a document (could be RTF, PDF, DOC, etc) directly in my database.
I understand the wisdom of storing a pointer to a location of the file outside of the DB and, having considered all of the pros and cons, have still opted to keep the document in the DB as opposed to outside of the DB.
The actual form that is used is fairly complex in that it is a compound form. The "host" form is the container for 2 other forms, unimaginatively called A & B for this discussion. I will refer to the host form as H.
So, H contains A & B.
Form H is bound to my FACT TABLE, maintaining the absolute minimum information required to track how the data is to be read. This form will have the current user, security status, session information and current record being viewed.
Form A will then be linked to Form H as a child form. Form A is bound to the database linked to the primary key of the current record currently being viewed from Form H.
Form A also contains a ]TAB CONTROL that has multiple forms within it. One of these subforms contained within the TAB CONTROL is bound to a related table that has a bound OLE control.
Form B is a administrative form presenting the user with various status details as well as options to select from. The information in Form B is based on a query of data in a subform contained in form A.
There is a button on Form B that launches a process that does the following:
The wall I walked into came about because of the levels that the forms are all nested.
The code as originally mentioned actually works on its own, but as soon as you start referencing the forms too deeply the code starts showing wobblies.
The range of problems experienced went from errors mentioned in the start of the post through to some really frustrating ones.
The solution that I have opted for eventually has been to create a form that has only the fields required for the operation. I provide values for all of the fields via VBA.
On a click of the button in FORM B I launch the form in a hidden form, set the values, commit the data to the DB, do a REQUERY of the embedded subforms within Form A, close the hidden form and everything works!
The code for the entire operation is as follows:
'============ Code Starts ==============================
'Note: Sub requires a parameters that contains the full name & path
' of the file to be embedded.
'====================================================
Public Sub EmbedPDF(varFile)
On Error Resume Next
Dim frmName As String
frmName = "frm_AddDocument"
DoCmd.OpenForm frmName, acNormal, , , acFormAdd, acHidden
With Forms![frm_AddDocument]
!DocTypeID = 13
!DocSubject = "PDF Document Image"
With !Document
.Enabled = True
.Locked = False
.OLETypeAllowed = acOLEEmbedded
.Class = "AcroExch.Document"
.SourceDoc = (varFile)
.Action = acOLECreateEmbed
End With
DoCmd.RunCommand acCmdSaveRecord
End With
DoCmd.Close acForm, frmName
Forms![FORMH]![frmFORMA].Form![sfrm_Documents].Form.SetFocus
Forms![FORMH]![frmFORMA].Form![sfrm_Documents].Form.Requery
End Sub
'=========== Cod Ends ===============================
That took much longer than I anticipated
Now I can go to bed, knowing that I will not be breaking out in a cold sweat in the middle of the night because this darn OLE/PDF thing has gotten me again
Hope this helps some other sucker to sleep well one day.
Sean
There's a tokoloshi in the net - & he's loving it
Okay, after a week of frustration I have worked out all of the possible reasons for my code not working and have a documented process for moving forward.
To recap:
The scenario is that I want to embed a document (could be RTF, PDF, DOC, etc) directly in my database.
I understand the wisdom of storing a pointer to a location of the file outside of the DB and, having considered all of the pros and cons, have still opted to keep the document in the DB as opposed to outside of the DB.
The actual form that is used is fairly complex in that it is a compound form. The "host" form is the container for 2 other forms, unimaginatively called A & B for this discussion. I will refer to the host form as H.
So, H contains A & B.
Form H is bound to my FACT TABLE, maintaining the absolute minimum information required to track how the data is to be read. This form will have the current user, security status, session information and current record being viewed.
Form A will then be linked to Form H as a child form. Form A is bound to the database linked to the primary key of the current record currently being viewed from Form H.
Form A also contains a ]TAB CONTROL that has multiple forms within it. One of these subforms contained within the TAB CONTROL is bound to a related table that has a bound OLE control.
Form B is a administrative form presenting the user with various status details as well as options to select from. The information in Form B is based on a query of data in a subform contained in form A.
There is a button on Form B that launches a process that does the following:
- Creates a compound PDF made up of several documents generated from selections in Form A
- Stores this compound document in the database, viewable through the subform embedded within the TAB CONTROL on Form A
- Formulats an Email with the compound PDF as an attachment, taking the [TO], [SUBJECT] & [BODY] information from subforms contained with the TAB CONTROL
- Sends the Email, deleting the item from the SENT ITEMS of the OUTLOOK client
- Returns control to the user with an "Operation Successful" msgbox
The wall I walked into came about because of the levels that the forms are all nested.
The code as originally mentioned actually works on its own, but as soon as you start referencing the forms too deeply the code starts showing wobblies.
The range of problems experienced went from errors mentioned in the start of the post through to some really frustrating ones.
The solution that I have opted for eventually has been to create a form that has only the fields required for the operation. I provide values for all of the fields via VBA.
On a click of the button in FORM B I launch the form in a hidden form, set the values, commit the data to the DB, do a REQUERY of the embedded subforms within Form A, close the hidden form and everything works!
The code for the entire operation is as follows:
'============ Code Starts ==============================
'Note: Sub requires a parameters that contains the full name & path
' of the file to be embedded.
'====================================================
Public Sub EmbedPDF(varFile)
On Error Resume Next
Dim frmName As String
frmName = "frm_AddDocument"
DoCmd.OpenForm frmName, acNormal, , , acFormAdd, acHidden
With Forms![frm_AddDocument]
!DocTypeID = 13
!DocSubject = "PDF Document Image"
With !Document
.Enabled = True
.Locked = False
.OLETypeAllowed = acOLEEmbedded
.Class = "AcroExch.Document"
.SourceDoc = (varFile)
.Action = acOLECreateEmbed
End With
DoCmd.RunCommand acCmdSaveRecord
End With
DoCmd.Close acForm, frmName
Forms![FORMH]![frmFORMA].Form![sfrm_Documents].Form.SetFocus
Forms![FORMH]![frmFORMA].Form![sfrm_Documents].Form.Requery
End Sub
'=========== Cod Ends ===============================
That took much longer than I anticipated
Now I can go to bed, knowing that I will not be breaking out in a cold sweat in the middle of the night because this darn OLE/PDF thing has gotten me again
Hope this helps some other sucker to sleep well one day.
Sean
There's a tokoloshi in the net - & he's loving it
PC User
Registered User.
- Local time
- Today, 06:17
- Joined
- Jul 28, 2002
- Messages
- 193
Great!! Sounds like you found your answer. I hope the samples that I reference for you were helpful. They are in the postings that I mentioned. Do you have a small demo of your project that you'd like to share with us?
Thanks,
PC
Thanks,
PC
Similar threads
