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.
34 Answers
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 FunctionIn 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.
5If you used the same concept but upsized to SQL Server- storing PDFs inside of an Image datatype (or varbinary(max)) then you could SEARCH INSIDE THE PDFs using Full Text Search.
I show that Microsoft says you can do this for any file type where you can register an IFILTER product.. and I just was at the Adobe website the other day and say that their Acrobat IFILTER is indeed FREE.
4Maybe 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.
1A 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.
