Saving PDF file as an OLE object in a database

[Hutch]

I have been trying to figure this out for some time now.

Currently i have a code that changes the default printer to a PDF printer, it then takes the name of a field in my form and changes the name of a report to that field then prints, i get the PDF file so i can email out BUT i want to save that PDF file as an Ole object on one of my tabels for later referance but i dont know how to go about this.

thanks in advance for your help.

[Denburt]

I have mentioned this in several articles recently and thought I would let you know that if you are storing OLE objects in a field in your table you could be asking for serious trouble. MS Access has a 2 gig limit so if you take this approach then keep this in mind, OLE objects tend to increase the size of your DB in a major way. I did find the following thread this thread has a link which should guide you in the direction you want.

http://www.thescripts.com/forum/thread205446.html

[Hutch]

how would i go about saving these "PDF" files in a seperate folder on the server then creating a link between a field in my table to that particular file inside the folder?

[pks00]

How many pdf's are you likely to store. I dont see a problem too much so as long as its not too many. But I dont store as ole objects, instead I store them as blobs.
One app of mine has a few wave files used when certain thresholds are hit. The way I store this is in a table in Access as blobs. I then use code to read and save onto the user's PC

If shedloads then u should as already suggested, store a path to the file.

Where are you storing these pdf's?
What u need to do is each time u create a pdf, store that name in some table
eg
tblPDFs
ID (autonumber)
Name (name of pdf)

assume pdf's are all in one directory, if not then u need to store the path as well

now in your other table, u can store the ID. That is the link to the pdf

[Hutch]

I use a form to pull several different types of information from several tables and then record that information onto another table to creat a "quote" i then print it as a PDF i have about 2000 PDF's right now. and currently i have to go out onto the server through folder to find the PDF quote to email out, i want a field in my "QUOTE" table that links the PDF to a command button that will bring it up. i have it all figured out but the actuall link. i dont know how to set that value.

[Denburt]

I have been trying to figure this out for some time now.

Currently i have a code that changes the default printer to a PDF printer, it then takes the name of a field in my form and changes the name of a report to that field then prints, i get the PDF file so i can email out BUT i want to save that PDF file as an Ole object on one of my tabels for later referance but i dont know how to go about this.

thanks in advance for your help.

Can we see some of this code? Does the code specify where to put the PDF's? Do the PDF's print to some random location each time? You should be able to tell the PDF's where to print to, and once you do that then you can just store the path and off ya go.

I have a contract database that stores PDF's as hyperlinks so they can click on it and actually see the contract. My version uses a dialog box to locate the contracts then store the path.

[Hutch]

Set Application.Printer = Application.Printers("Quote - PDF")

this is the code to get the printer to switch over, the printer is specified to print to a certain folder on the server. another funny thing is all the files get printed in black and white even through all the settings on the printer are correct, althoguh i'm not stressing over this part i thought you might be able to shed some light on the subject. thanks

[Denburt]

the printer is specified to print to a certain folder on the server

Since you know the path, and you probably know the file name simply store that info in a hyperlink field and off you go.

Me!SomeHyperField = "\\ServerName\Folder\FileName.pdf

I will see if I can find anything on the black and white issue.

[Hutch]

Since you know the path, and you probably know the file name simply store that info in a hyperlink field and off you go.

Me!SomeHyperField = "\\ServerName\Folder\FileName.pdf

I will see if I can find anything on the black and white issue.

should i take it the "somehyperfield" is the name of the field on my table

[Denburt]

Yes sir once you set it as a Hyperlink field in your table then set the path/filename they can click on it and it will open the pdf so they can view it.

[Hutch]

Since you know the path, and you probably know the file name simply store that info in a hyperlink field and off you go.

Me!SomeHyperField = "\\ServerName\Folder\FileName.pdf

I will see if I can find anything on the black and white issue.

i recieve an "Invalid procedure call" on this

[Denburt]

i recieve an "Invalid procedure call" on this

Ah yes brain isn't what it used to be my appologies:

Hyperlink

Private Sub cmdFollowLink_Click()
    CreateHyperlink Me!cmdFollowLink, Me!txtSubAddress, _
         Me!txtAddress
End Sub
 
Sub CreateHyperlink(ctlSelected As Control, _
     strSubAddress As String, Optional strAddress As String)
    Dim hlk As Hyperlink
    Select Case ctlSelected.ControlType
'I added textBox because thats what I use (storing it in a table this makes it easier)
        Case acLabel, acImage, acCommandButton,TextBox
            Set hlk = ctlSelected.Hyperlink
            With hlk
                If Not IsMissing(strAddress) Then
                    .Address = strAddress
                Else
                    .Address = ""
                End If
                .SubAddress = strSubAddress
                .Follow
                .Address = ""
                .SubAddress = ""
            End With
        Case Else
            MsgBox "The control '" & ctlSelected.Name _
                 & "' does not support hyperlinks."
    End Select
End Sub
 

[Hutch]

Could you help me understand the first part of this Code? I'm having difficulty understanding the "ME" commands.

Thanks in advance

[Denburt]

Could you help me understand the first part of this Code? I'm having difficulty understanding the "ME" commands.

Thanks in advance

Me is a reference to the form or report you are currently coding in.
Simply two text boxes storing information about the link and a button that directs you. If you check the link I posted it should help you understand the hyperlink code I posted a little better.

[AVA75]

I know it's a little too late to ask something about this subject, but it's kinda related.

I would like to know if there's any easy way to change a field (OLE OBJECT) in a table to HYPERLINK.

We have a database that has PDF embedded and of course, the size increased upto the 2 gigas limit.

Is there any way to change that or we just have to migrate everything to SQL Server?

Thank you very much!