how to link files to records without using attachments?

[svdoerga]

I have a database where I want to add pdf files to each record. Should be 3 pdf files (scanned documents) per record on average.

So far I have found 2 ways of handling attachments:
- Using linked OLE objects
- The access 2007 attachment feature

The application will reach more than thousand records easily, and with 3 pdf files per record, it will probably bloat too much using either option.

Is there a way to do this differently? And if so, how should I do it?

I can imagine having a linked table like:
Attachment(AttachmentID, ForeignKeyToMainTable, AttachmentFileName, AttachmentPath)

But I don't know how to implement this on my forms where I edit or add records.

What I want basically:

- attach links to pdf files to records and rename the files to the primary key with an autoincrement (file 1: PK_1, file 2: PK_2, etc.)
- Have links to the pdf files and be able to click them from forms so they open in Acrobat reader

Anyone have advice? Thanks!

 

[MajP]

Attachment fields do not bloat the database. PDF are already in a compressed state so there will not be any savings, but you should expect the database to grow at the same amount of data you put into it. OLE fields bloat. If you put a 1meg file in an attachment field the database grows by 1 meg or less. Less if the format can be compressed. If you put 1 meg into an ole the database can grow from ~ 3-5 meg.

you can simply use a hyperlink field if you do not want an attachment field. You can also simply save the path as text, but then you will need a little more code for browsing and then rendering. There are plenty of examples on the web for code with either option.

 

[petermeachem]

I don't know if things have changed, but I've always understood that inserting things like pictures and pdf's into a database was a bad idea. I display documents from our document imaging system which in folders on a server on some Access forms. I just use shellexecute to display them.
We've got 300,000 documents, no way would I ever insert them into sql server let alone access!

 

[MajP]

I don't know if things have changed, but I've always understood that inserting things like pictures and pdf's into a database was a bad idea. I display documents from our document imaging system which in folders on a server on some Access forms. I just use shellexecute to display them. 
We've got 300,000 documents, no way would I ever insert them into sql server let alone access!

Yes things have changed considerably in my opinion and it is not a bad idea to put images/documents into a database, but you have to understand the limits and the type of fields.

Access 2007 and beyond will not bloat the database if using attachment fields, it will actually compress depending on the file type. If you put in a gig of data the database should grow by about a gig if those are jpgs. It will grow less than that if they are bmp because they will get stored compressed.

So if you are dealing with a few hundred maybe a thousand images (less than 2G total for db) it may be easier to store with the db now a days.

If bigger, than store either a path (text field) or hyperlink (hyperlink field). In 2007 there is a bound image control that requires no code. If the control source is a path the image displays. This is a HUGE improvement, and little known.

Attachments also store data more efficiently. Earlier versions of Access used a technology called Object Linking and Embedding (OLE) to store images and documents. By default, OLE created a bitmap equivalent of the image or document. Those bitmap files could become quite large — as much as 10 times larger than the original file. When you viewed an image or a document from your database, OLE showed you the bitmap image, not the original file. By using attachments, you open documents and other non-image files in their parent programs, so from within Access, you can search and edit those files.

In addition, OLE requires programs called OLE servers to function. For example, if you store JPEG image files in an Access database, every computer that runs that database requires another program that is registered as an OLE server for JPEG images. In contrast, Office Access 2007 stores the attached files in their native formats with no supporting images, and you do not need to install additional software in order to view the images from within your database.

If the file isn't a compressed format, Office Access 2007 automatically compresses it for you, saving hard disk space
[/code]

 

[svdoerga]

I will look into getting it done with a hyperlink field.

Thanks for your replies guys.

 

[MajP]

Although these techniques use just the path as text and not the hyperlink field you may find the code contained in these posts very useful.

Thread181-1646795
Thread705-1647315

If you choose to just use a hyperlink, Access makes it very easy to add and follow hyperlinks. However, if you want to manipulate programmatically hyperlink fields it is a little tricky. Each hyperlink stores 3 parts

http://www.everythingaccess.com/tutorials.asp?ID=Introduction-to-Hyperlink-fields

So bottom line if you are happy with the native hyperlink features I would stick with that. If you want to add and do a lot of manipulations you may find it simpler to just store the path and file name.

 

[svdoerga]

Thanks for the links MajP. I will look into it once I have fixed some other problems with my app.