Forum Discussion
A Problem with attached images in Access, Windows 10, Office 365
I have built an ACCDB database for my comics. I haven't yet made the DB relational. I was mostly working on getting my records into the database, and the basic record, which is long but almost all related to individual issues, consists of Publisher Code, Issue Name, Volume Number, Issue Number, copyright year, month of publication (like 01, 02, 03), Box number (where it's stored) and, as an attachment (not an OLE object) a picture of the cover. The database is large.
Yesterday, I noticed a problem when I tried to attach an image to a record. I can almost complete the process, but at the "commit," I get a popup "Cannot open database ''. It may not be a database that your application recognizes, or the file may be corrupt."
I have tried to "Compact and Repair" the database with miserable results. It creates a table (an MDB table, no less) with several system errors.
I spent some time with a Microsoft Tech Support person a few minutes ago, but we didn't accomplish anything useful. To be complete, we opened MSAccess from the CMD prompt using "MSACCESS.EXE /decompile" (no problem), then opened the actual corrupt ACCDB file from the command prompt (no evident problem), but when I tried to Save As (new file name), the Save As process locked up after about 1,800,000 KB of processing, and I had to kill it.
Any suggestions welcome. I have used OneDrive to try and roll back the corrupt file to a previous version (not too far back, because I've put in a lot of work this week!), but those older versions still seem to have the Compact and Repair problem.
https://support.microsoft.com/en-us/office/access-specifications-0cf3c66f-9cf2-4e32-9568-98c1025bb47c
If you put the pictures on a file system (as you should do for BLOBS > 4 KiB), be aware that NTFS has a soft ca. 10000 files limit per folder. (Hard limit is 4294967295 files per folder, but you can't open the folder in either Explorer or Access.)
Thomas Sowell — 'There are no solutions. There are only trade-offs.'
7 Replies
- Well Access and Images is a tough case
For start you could check my article on working with Images and Access :
https://www.experts-exchange.com/articles/33716/Defeating-the-device-independent-bitmap-dib-format.html
(for the time being 32bit)
Other than that since it seems you quickly filling the space two options :
1. If its single user you could switch your BE to Sqlite
2. If its going to be multi user then Firebird is your friend
I had a case with 1000s of images populating a MsFlexGrid without issues with Access as FE and Firebird as BE - A handy solution has been found by using Excel to import the largest table. Of course, the PNG's were not imported as a column, but, more usefully, the names of the PNG's were imported as a text column. I saved the spreadsheet, made a new Access database, along with a Locations table that specifies the file path and physical location of a particular box (or notebook), and I can use Box ID as a foreign key for the Comics table to determine the location of a particular issue. At some point I'm going to have to write some VB script to display the JPG, but I need to get everything scanned first.
That error message is, unfortunately, usually accurate, "... the file
may beis corrupt"Right now, what you can do is start over with the most recent usable back up.
Next, stop importing data and enforce Referential Integrity on your tables. This is fundamental. Without proper relationships being enforced, your data clean up task may be extremely difficult. It's the equivalent of putting up walls and a roof and then trying to put a foundation underneath the structure. It might be possible, but, oh, the extra work and complications required!
Finally, do not put the attachments inside the tables. Granted Access supports doing so, but it is also a risky thing to do. First, it bloats the Access acddb significantly. You wrote, "The database is large" and that is a signal you are headed for unusability, regardless of whether it is corrupted. You didn't state how large, "large" is, but the maxium size of an Access accdb is 2 GB. If your access is anything over 1 GB, though, it's fragile (as you've now learned).
Store the images in a shared network folder. Save the path to the image in a Text field in the table.
In the meantime, learn from this problem and avoid more difficult problems down the road.
George_Hepworth "Importing data" is a strong word. I was manually typing information directly into the table, and I was not aware there was a 2GB limit on Access files. I would not have started this in Access had I known that. Frankly, it removes the reason to use ACCDB files in preference to MDB files, where you had to do what you suggest.
The trouble with using text fields to point to a network location is that network locations can go down or can move; if the image is contained in the table, everything is self-contained so the database file itself can go anywhere. However, it does point to needing some referential tables (Box X is physically located at Y and uses network path Z for its image files, so you now need a Box table.)- I agree that the word "import" might be misleading. You physically added the binary image files into tables in some manner, and that's what I was driving at.
The file size limit for accdbs is the same as mdbs, 2 GB. That's been the same since at least Access 2.0 (it may have been 1GB in the original version of Access.) Over the years, a lot of discussions about that file size limit have all led to the same conclusion: Microsoft is not going to change that limit.
If your organization maintains an unstable network where locations can "go down" or can be moved without notice, that's a different kind of problem. It's not within the scope of this process. I understand that an organization can't guarantee that IT will not take a notion to rearrange the network, but that's probably not going to happen without some advance notice in any responsible organization, I don't think. In other words, that does not strike me as a strong reason to take the even higher probability risk of putting image files inside an accdb.
I agree that if you want to add a reference to the physical locations of the physical media. That's wise anyway.
However, if you really want to store image files in fields in tables, look into using SQL Server Express for the back end. While bloat is still a problem, the maximum size of SSE databases is 10GB and that not only would give you more space, but it would be far less likely to be corrupted.
It may not be a database that your application recognizes, or the file may be corrupt."
I guess the database should be corrupt.
if the size of your database is large,it looks easier to be corrupt.
So if possible,only save the path rather than the whole picture file in the database.
