Storing PDFs in MS Access Database using Forms

Question

I need to store PDF files in an Access database on a shared drive using a form. I figured out how to do this in tables (using the OLE Object field, then just drag-and-drop) but I would like to do this on a Form that has a Save button. Clicking the save button would store the file (not just a link) in the database. Any ideas on how to do this?

EDIT: I am using Access 2003, and the DB will be stored on a share drive, so I'm not sure linking to the files will solve the problem.

Answer 1

Maybe this will help: ACC2000: Reading, Storing, and Writing Binary Large Objects (BLOBs).

What they do: Read a file in chunks and add it to a blob using a VBA function.

Answer 2

A field of OLE Object, by default would use a Bound Object Frame on the form. Right click on it and you can Insert an object. It comes complete with browsing for the file. Double-click on the field and the actual document will open.

I recommend going with David's advice and link. Unless you have a need to transfer a single file and want all the PDF's included. Size and performance will be an issue.

If security is an issue and the Access file is the only control you have (You are unable to set security on the folder containing all the linked files.), then you would have to embed.

Answer 3

We have several databases that contain 10's of thousands of documents (pdf, doc, jpg, ...), no problem at all. In Access, we use the following code to upload a binary object to a binary field:

Function LoadFileFromDisk(Bestand, Optional FileName As String = "")
  Dim imgByte() As Byte
  If FileName = "" Then FileName = strFileName
  Open FileName For Binary Lock Read As #1
  ReDim imgByte(1 To LOF(1))
  Get #1, , imgByte
  Close #1
  If Not IsEmpty(imgByte) Then Bestand.Value = imgByte
End Function

In this case, Bestand is the field that contains the binary data. We use MS SQL Server as a backend, but the same should work on an Access backend.