Issue when adding attachments to Access 2016 database

Question

I started learning Access this past year, realizing the potential I had for keeping track of things around home (rolling stock on my model railroad, books, CDs, DVDs, etc.), making databases for each.  One thing that I've been working on is getting pictures/scans of everything to attach to the records in the databases, something that attracted me to Access when I had to recreate a database from my old laptop that kept track of my rolling stock on my model railroad layout.

Everything has been going smoothly so far until this evening.  I was working on the database of issues of magazines that I have in my collection (Model Railroader, Trains, Railroad Model Craftsman, etc.).  This past summer, I scanned the covers of each issue and recently finished trimming and resizing each scanned image to attach to the records in the database that I created for my magazine issues, which I imported from an Excel document the other night.  It came out to 1100 records.  Today, I was going through and adding the attachments, making really good progress, but after about 150, I started getting the error "Cannot open database.  It may not be a database that your application recognizes, or the file may be corrupt."

I know that the attachments are okay.  They're opening in other applications.  Searching the error, it sounds like there is a limit to the total size of the attachments that I can attach to my database.  Is there any way to get around this, or do I need to resize my attachments again and possibly divide up my database into multiple databases?

Answer 1

Because of various issues, mainly database bloating and database for size limitation, attachments should be only used in very limited applications (icons, background, ...).  When it comes to attachments relating to records, then attachments (within a table) are a horrible idea and should not be used.  Instead a simple text field should be employed to store the path and filename of the attachments, but leaving them on your hard drive.

See http://www.devhut.net/2016/10/03/adding-attachments-to-an-access-database/ for more details and a demo database with everything you need.

Answer 2

A more efficient way to associate images with records is to store only the path to the image file in a text column in the relevant table.  The image is then loaded into an image control in a form or report at runtime.  You might like to take a look at Images.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

The zip archive incudes a number of files of various degrees of complexity.  For associating a single image with a record the basic Images_Simple.accdb file would suffice.  The other two files illustrate how multiple images can be associated with each record.