Access 2013 data type Attachment

Topic

New Reply

When I acquired my limited Access knowledge a decade ago, the advice strongly recommended keeping files [eg images, PDFs] in the file system rather than in the DB.

I’m putting a small accdb DB together atm, and see the Attachment data type [which I don’t remember from Access 2003]–if I understand correctly, this is to contain the actual file(s) and not just a link to it/them. Has something changed re recommended general practice recently, or is it still better to use the file system? The old reason iirc was to reduce DB size, and that calls to the DB should be minimized–eg keeping files out of it.

The DB won’t be big–less than 10 tables, maybe 100,00 records in one table, less than 1,500 in others, around 60 fields all told, 10-20MB in total filesize without Attachment data–and will be used locally by 2-3 people, ie not from server. I don’t foresee a need to be able to integrate files with reports or output forms, which is my guess re where the Attachment type is useful.

The disadvantage I see with using the file system is that the DB will then be dependent on paths remaining identical across different computers, which of course is a dependency just asking to be broken.

Thanks for any education 🙂

Lugh.
~
Alienware Aurora R6; Win10 Home x64 1803; Office 365 x32
i7-7700; GeForce GTX 1060; 16GB DDR4 2400; 1TB SSD, 256GB SSD, 4TB HD

Reply | Quote

Replies

As long as multiple users will be using a database, and you want them all to see the same content, the back-end of the database needs to have a common file path, so you will already have the file path issue, although it may be simpler than what you might have with images or PDFS. The attachment data type was introduced in Access 2007 in the .accdb format, and does provide a nice way of storing that type of data, as long as you don’t have a huge number of them. You still have the 2GB file size limit, and any database that exceeds 1GB in size makes me nervous. Image and PDF files can get pretty large, so you might want to think about the upper limit on the number of them before you make your choice.

Reply | Quote

You are not wrong, the mantra used to by “Don’t store images in Access”!

My understanding is that Access changed how it stored those attachments, so that it does it much more efficiently now. For example, I think in the past Access stored everything internally using the .bmp format (even if, for example, you were saving a .jpg image); which is why storing any images cause the database to bloat significantly. Now, I believe it stores files in their native format.

That being said, Access does still have a 2GB max file size. So saving a lot of images could cause that to be a factor.
But you should be better able to now calculate how much your attachments within Access would affect this.

Reply | Quote

Thank you both. I think I’ll keep routine files [the work is image-heavy] in the file system, and only use the Attachment type for more ad-hoc stuff like explanatory or instructional text files etc.

Lugh.
~
Alienware Aurora R6; Win10 Home x64 1803; Office 365 x32
i7-7700; GeForce GTX 1060; 16GB DDR4 2400; 1TB SSD, 256GB SSD, 4TB HD

Reply | Quote