Embed PDF file in bound control

[tokoloshi]

I need to embed a PDF file into my DB. Based on the client's business rules I cannot maintain the document outside of the DB. I cannot therefore link to a given location outside of the database.

The code that I am using, which I have confirmed from several different sites, is as follows:

------- Code used ----------------------------------------
varFile = "C:\PDFForms\ImportedFile.pdf"

On Error GoTo Error_cmdOleAuto_Click
Forms![PDFForm]![DocTypeID] = 13
Forms![PDFForm]![DocSubject] = "My new PDF document"
With Forms![PDFForm]![embeddedDoc]
.Enabled = True
.Locked = False
.OLETypeAllowed = acOLEEmbedded
.Class = "AcroExch.Document"
.SourceDoc = varFile
.Action = acOLECreateEmbed <-- Error generated here
End With
DoCmd.RunCommand acCmdSaveRecord

----------- Errors received --------------
?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 receive the listed errors when running this code.

All of the examples listed on all of the searches I have performed so far point me to the use of a .JPG or other image. These work comfortably, but the PDF file gives problems.

I have tried ignoring the .CLASS argument, but it still returns errors.

I have searched the current forums and find that similar questions to this have been posed by other members, but have also gone unanswered.

Please refer to:

1. http://www.access-programmers.co.uk/forums/showthread.php?t=104988
2. http://www.access-programmers.co.uk/forums/showthread.php?t=103410&highlight=ole+pdf

There are a heap of other discussions, but most tend to recommend saving the document outside of the DB.

Has anyone solved this problem yet?

Can someone please advise me on this one?

 

[Mike375]

I can give you what I use for linking to OLE but you want embedded.

However, to embed you only need to copy/paste.

In the case of Word.docs I sometimes embed as part of a block of code use to open and print Word. Part of that code copies the Word.doc while open then

DoCmd.GoToControl "OLEBound1"
DoCmd.RunCommand acCmdPaste

The following will open an OLE oject for both linked and embedded.

Forms![formolelink]!OLEBound1.Verb = -2
Forms![formolelink]!OLEBound1.Action = 7

 

[Mike375]

I had just had a fiddle with this and I could not find a way to open a PDF whereby I could copy it. Shell and batch will open PDF but you can't do anything with it or at least I can't

However, I played with the code I use to link Word and the following worked to embed the PDF

With Forms!formolelink![OLEBound1]
.Enabled = True
.Locked = False

.OLETypeAllowed = acOLEEmbedded



.SourceDoc = ("C:\Letters\" & Format([Forms]![formolelink]![LNameNoDoc]) + ".pdf")


.Action = acOLECreateEmbed

.SizeMode = acOLESizeZoom
End With
Exit Sub

The code in bold is the field where the file name is entered. As you can see only the file name is entered, not the extension. I just do it that way becasue of how I handle the storing and opening of saved Word.docs.

As a side note, I did check it was embedded as I could open it after deleting the PDF file.

Edit: I just checked in another computer with XP Professional and I had closing the PDF file when I opened it but no problem on this Dell/Vista. Perhpaps the version of PDF, don't know. All references were the same as I copied the DB across.

 

[Mike375]

I just tried another computer this time XP Home and again, troubling closing the PDF. On both machine I am getting a message OLE server not register, reinstall.

More work to be done but too late in Sydney Australia for this sort of stuff

Having said that, if I needed this for my own use I would try and track down code that would open the PDF and then copy it so it could be pasted in to the OLE field.

 

[tokoloshi]

Thanks Mate. Appreciate the effort.

Problem is I have spent the better half of this day trying to track down a working example.

Working within Word, Excel, JPGs and GIFs do not represent problems. Doing it manually is also a no-brainer.

Getting the darn thing to work with VBA is a pain that seems to be common to many developers. I do not wish to use a third party solution since it seems so obvious. I am hoping someone has already solved this problem and can post their experience here.

 

[Mike375]

Try this link

http://support.microsoft.com/kb/120262

I just had a quick go using that link but again I can't close the PDF when it is opened on the two XP computers.

The Vista is OK as long as I close the form after the embed and reopen the form and the open the embedded file.

 

[DCrake]

Hi

Find attached a sample mdb on how to embed files into access databases.

As there is a seperate backend for the mdb. Open up the EmbeddedObjects.mdb using the shift key then relink the backen mdb (Documentation.mdb) to the front end demo.

Let me know how you get on

David

 

[Mike375]

David,

I relinked the table and also ran macro1 with new path. But I am getting broken links/debug. I saw a couple of spots in the cose I changed the path on. But I will play with it tomorrow as I am too tired and are making mistakes

 

[DCrake]

Revisited the mdb and all you need to do prior to opening the wizard is to make sure that the Documentation.mdb is in the same folder as the EmbeddedObjects.mdb.

David

 

[Mike375]

I am getting the message

Broken link or reference to file COMDC632.OCX Version 1.2

I think I saw that file mentioned a while ago when I was searching on Install OLE server.

 

[DCrake]

Try checking your references. Here is a copy of what I have attached for this mdb.

Also at what point is it giving you the error

David

 

[Mike375]

When it opens the message comes up and when I close the box it goes to the code

If UCase(Left(tdf.Name, 3)) = "TBL" Then and is from Function RefreshLinks() As Boolean

Can't project ior library. Click OK to end DeBug and form opens.

For some reason the following works perfectly for me with Word, it embeds a PDF but I can't close the PDF when I back to open it from the OLE field. I might go and try what I use for Linking/Word and see how that goes with PDF. I wish I had never opened this thread

With Forms!formolelink![OLEBound7]
.Enabled = True
.Locked = False

.OLETypeAllowed = acOLEEmbedded



.SourceDoc = ("C:\Letters\" & Format([Forms]![formolelink]![LNameNoDoc]) + ".doc")


.Action = acOLECreateEmbed

.SizeMode = acOLESizeZoom
End With

Exit Sub

 

[DCrake]

Do you want me to continue helping you or not?

If you design the FrmWizard On load event and rem out the Relink tables line then open the form it will bypass the routine. But make sure theat both mdb/s are in the same location.

 

[Mike375]

What I have works fine for both link and embed for Excel an on Vista, XP Home and XP Pro.

But PDF, it is embedding and linking but can't close the file on the XPs.

I also have the same problem with the PDF if go manually for Insert Object etc. and that is on the XPs.

Might be the version of Acrobat or the my scanner that produces the PDF files.

 

[Mike375]

Do you want me to continue helping you or not?

Check my last post on PDF and Insert Object...also a problem.

So it looks like the code I am using is OK but I have a problem with OLE/PDF on the two XPs.

I will try an find some PDF files that have not been made by my scanner, although they are OK on the Dell/Vista.

Any ideas?

 

[DCrake]

I am using Windows XP Pro and adobe acrobat 7.0

 

[Mike375]

I just noticed it 5 hours since I first posted on this thread But I usually find I learn something else with all the poking about

 

[Mike375]

I am using Windows XP Pro and adobe acrobat 7.0

That is exact combo I am having trouble with.

I just upgraded acrobat 9 and isntalled Active X but same problem. I tried a couple of PDF files I have justr received rom insurance companies just in case it was scanner related but same problem. Whether I use my code, Insert object or Copy and Paste same deal, can't close acrobat and the message is the same for all 3, about OLE server.

My Vista is also now giving trouble and with code, Insert object or Copy and Paste. It is inconsistent and I guess just trying it more means the problem shows up but the XPs are 100% failure rate.

There must be something I have on these computers that is causing a problem

I think I might sleep on this one as it is 1.40am down here in Australia.

 

[tokoloshi]

@DCrake

Find attached a sample mdb on how to embed files into access databases.
David

The application.filesearch method used in the [FrmFilesIn] form is no longer used in Access 2007. I have modified the code to read as follows:

'=============== Amended FindAllFilesInFolder Code ==========
'Amended By: S. Achim
'Amended Date: 17 Dec 2008
' Comment: application.filesearch replaced by file system object
'====================================================
Sub FindAllFilesInFolder(StrPath As String)
'Perform simple search using filesearch object
'Note: Unneeded variables removed - SAchim
Dim objDB As Database
Dim bFlag As Boolean
Dim StrListItems As String
Dim FolderLength As Integer
Dim fName As String

DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * from tblFoundFiles"
DoCmd.SetWarnings True
Set objDB = CurrentDb

'Variables for file system object declared - SAchim
Dim objFSO As FileSystemObject
Dim objFolder As Folder
Dim objFile As File

Set objFSO = New FileSystemObject
Set objFolder = objFSO.GetFolder(StrPath)

'Amended iteration - SAchim
For Each objFile In objFolder.Files
DoCmd.SetWarnings False
FolderLength = Len(StrPath) + 1
fName = Mid(objFile, FolderLength)
StrListItems = StrListItems & fName & "','" & dblocation & "','"
DoCmd.RunSQL "INSERT INTO tblFoundFiles ( FilePath , FileName ) SELECT '" & dblocation & "\" & "' AS A, '" & fName & "' AS B;"
bFlag = True
DoCmd.SetWarnings True
Next objFile

'Amendments completed - SAchim

objDB.Close
Set objDB = Nothing
If bFlag = True Then
'Drop the last seperators
'pass the results back to the list box on the screen

Me.LstFoundFiles.RowSource = "QryFoundFiles"

If Me.LstFoundFiles.ListCount > 0 Then
Me.LstFoundFiles.Enabled = True
Me.LstFoundFiles.Locked = False
Else
Me.LstFoundFiles.Enabled = False
Me.LstFoundFiles.Locked = True
End If

End If
End Sub
===========================

 

[tokoloshi]

Hmmm. That was stupid, I should just have posted a text file that could have been downloaded instead of placing the code here to read through.

Sorry guys