UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
3 Pages V  1 2 3 >  (Go to first unread post)
   Reply to this topicStart new topic
> Storing Pictures In Access - How Big Can They Be?, Access 2016    
 
   
dflak
post Aug 31 2019, 08:36 AM
Post#1


Utter Access VIP
Posts: 6,286
Joined: 22-June 04
From: North Carolina


I have a potential client who is an art dealer. He has several hundred JPEGs of pictures in his collection and has them organized in a "flat file" in Access 2000. I've convinced him to upgrade to an Office 365 subscription which will at least give him 2016 or 2019 or whatever.

He wants to know: what is the maximum size picture he can store? How big can the database grow? I know in the past that there was a 1 GB limitation.

I want to know, do you have an option to import the pictures or "link" them?

I'm hoping to hear that it is limited only by disk size.

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
isladogs
post Aug 31 2019, 08:53 AM
Post#2


UtterAccess VIP
Posts: 1,707
Joined: 4-June 18
From: Somerset, UK


The maximum size is 2GB but adding lots of images to Access will rapidly bloat the file size.
Strongly recommend your client does not attach the files.
Instead store the file path for each in Access and use an image control to view them.

Also JPEG files do not always display well in Access due to their lossy nature. PNG files work better.

Another reason to avoid attachment fields - it will be impossible to upsize to SQL Server or similar in the future

--------------------
Go to the top of the page
 
dflak
post Aug 31 2019, 07:21 PM
Post#3


Utter Access VIP
Posts: 6,286
Joined: 22-June 04
From: North Carolina


I was afraid that would be the answer. Now what do you mean by "use an image control?"


Do you mean that I should write some VB code to launch a command line for irfanview?

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
June7
post Aug 31 2019, 08:07 PM
Post#4



Posts: 891
Joined: 25-January 16



Access has an Image control that can be bound to Attachment field or reference external file location to display images in form or report. Review https://www.accessforums.net/showthread.php?t=73766

This post has been edited by June7: Aug 31 2019, 08:08 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
JohnvanSomeren
post Sep 5 2019, 09:54 AM
Post#5



Posts: 669
Joined: 31-August 12
From: London, England


Hi,
I have been using DBPix from http://www.ammara.com/ in a big Access 2010 program for several years. It is designed to store huge numbers of pictures in OLE fields of an Access database without bloating. Our ID photos are usually around 70k to 80k jpegs and we have around 5,000 of them. It is a no-brainer to implement and their support team is really reactive. Plus they have lots of Access VBA example code.

I have no commercial interest in the company except that any company which provides software that does what I want, without advertisements and at a good price deserves to be better known.

John van Someren
Go to the top of the page
 
ADezii
post Sep 5 2019, 10:14 AM
Post#6



Posts: 2,679
Joined: 4-February 07
From: USA, Florida, Delray Beach


Have you considered storing the Images as BLOBs (Binary Large Objects)? In this manner the Picture Data is stored Byte for Byte, exact size, with the Images reflecting their true sizes. When BLOBs are stored in the Database with other data, BLOB and tabular data are backed up and recovered together and are synchronized, there are no File Paths to contend with, and no resultant Database bloating. Data consistency is ensured because INSERT, UPDATE, and DELETE operations occur in the same transaction. Separate security measures need not be created since BLOB and regular data coexist.
Go to the top of the page
 
ADezii
post Sep 5 2019, 11:15 AM
Post#7



Posts: 2,679
Joined: 4-February 07
From: USA, Florida, Delray Beach


P.S. - I haven't worked with BLOBs in awhile, although I did see the original movie 'The Blob' with Steve McQueen recently, so I did a short experiment. I loaded 600 *.jpgs ranging in size from 18Kb to 1.82Mb into an OLE Object Field via an ADODB Stream Object, i.e. BLOB. The size of the original Database, which was empty, increased by only the Total Size of the *.jpgs loaded into it indicating no DB Bloat.
Go to the top of the page
 
DanielPineault
post Sep 5 2019, 11:22 AM
Post#8


UtterAccess VIP
Posts: 6,905
Joined: 30-June 11



You shouldn't use attachments, especially in this context, with this quantity of images.

Better allow him to save the files as he sees fit (hopefully he is well organised on that front) and simply store the path/filename and dynamically display the image on the form/report.

I have a very old sample available at http://www.devhut.net/2010/11/20/ms-access...ng-with-images/ that could be a starting point for you. Also relevant http://www.devhut.net/2016/10/03/adding-at...ccess-database/

As a side note, the typical way around the 2GB limit is to split the data amongst multiple BEs. If you truly are going to store the images in the BE, for this type of scenario, I wouldn't even consider Access and would go straight to SQL Server Express, MySQL, PostgreSQL ... something that can handle the quantity of data you are looking at having to juggle.

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
dflak
post Sep 5 2019, 03:46 PM
Post#9


Utter Access VIP
Posts: 6,286
Joined: 22-June 04
From: North Carolina


My client is married to Access. In fact, I am dragging him, kicking and screaming into the 21st century to get him to upgrade from Access 2000. The pictures will be 1200 x 900 each (~1 MB) 72 DPI. There will be hundreds (not thousands) of them. I think I am safe.

Now I have to do some research on how to "push" them to a browser.

The idea is that he can take his laptop to a show where there is no internet, do a key word search, get a list of pictures that match and then display them on his screen. I don't think I even need to put the pictures in the database. I'm still mulling the design over in my mind.

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
MadPiet
post Sep 5 2019, 03:53 PM
Post#10



Posts: 3,334
Joined: 27-February 09



and the nice thing about SQL Server Express is that it's free. =)
But given that you have a customer on Access 2000, that could be a really hard sell. (Maybe you could make your database play "Living in the Past" when it opens?)
Go to the top of the page
 
ordnance1
post Sep 12 2019, 08:46 AM
Post#11



Posts: 684
Joined: 7-May 11



Sorry for jumping in on this thread but I am in a similar position where I would like to store short PDF,s and JPEG in my database. I use SQL Server as a back end to Access, and an interested in using BLOB. What I am wondering what the process would be to convert files in a a temp folder, convert them to BLOG and drop the data into an SQL server.
Go to the top of the page
 
ADezii
post Sep 12 2019, 11:55 AM
Post#12



Posts: 2,679
Joined: 4-February 07
From: USA, Florida, Delray Beach


QUOTE
what the process would be to convert files in a a temp folder, convert them to BLOG and drop the data into an SQL server.

  1. You should be able to:
    1. Perform a Bulk Import of Graphic Files into an Access OLE Object Field as BLOB Data.
    2. Use DTS (Data Transformation Services) in SQL Server to Copy the Data into a New Database. The BLOB Data will go into an Image Field.
  2. I can easily create a Demo that will pro-grammatically Import the Graphics into an OLE Object Field as BLOBs.
  3. The second part I'll leave to you.
  4. Just let me know if you are interested.
Go to the top of the page
 
ordnance1
post Sep 15 2019, 09:53 PM
Post#13



Posts: 684
Joined: 7-May 11



That would be greatly appreciated.
Go to the top of the page
 
ADezii
post Sep 16 2019, 07:40 AM
Post#14



Posts: 2,679
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. The one, and only purpose of the attached Demo, is to perform a Bulk Import on a large number of Graphic Files into an OLE Object Field as a Stream of Binary Data (BLOB).
  2. Simply click on the only Command Button then select a Folder to Import From. For now, it will only Import .jpgs, .bmps, and .icos but this can be modified by changing the acceptable Formats in the Form's Private Function named fIsGraphicFile().
  3. The Base Code comes from Alan Warren but I made some modifications to it.
  4. As a test, I Imported 1,018 Graphic Files consisting of .jpgs, .bmps, and .icos.
  5. The size of the Empty DB prior to Import was 464Kb.
  6. The Total Size of the Graphic Files prior to Import was 184Mb.
  7. The size of the DB after the Import process was still 184Mb with very little variation (bloat).
  8. This actually makes me wonder if you need SQL Server as a Back End strictly for the storage of these Images?
  9. In any event, I'll stop rambling on, simply look at the Attachment and see if it suits your needs.
  10. Should you wish to keep the Graphics in Access, this is an entirely different animal since you will need algorithms to retrieve, store, and even modify these Images. Should you decide to take this approach, I can provide another Demo that will show you how to do this.

Attached File(s)
Attached File  Import_Graphics_For_BLOB_Storage.zip ( 38.85K )Number of downloads: 5
 
Go to the top of the page
 
ordnance1
post Sep 16 2019, 10:06 AM
Post#15



Posts: 684
Joined: 7-May 11



Thanks for the demo file. I will spend some time testing if out.
Go to the top of the page
 
ordnance1
post Sep 16 2019, 10:11 AM
Post#16



Posts: 684
Joined: 7-May 11



Ran into a compile error that the code needs to be updated for 64-bit.

I will do some investigation into how to update the code.

Solved: Added PtrSafe after declare
This post has been edited by ordnance1: Sep 16 2019, 10:15 AM
Go to the top of the page
 
ordnance1
post Sep 16 2019, 10:57 AM
Post#17



Posts: 684
Joined: 7-May 11



Cannot get it to work.

When I click on the button I get the hour glass then the db closes and prompts me to save a backup copy of the db.
Go to the top of the page
 
ADezii
post Sep 16 2019, 05:37 PM
Post#18



Posts: 2,679
Joined: 4-February 07
From: USA, Florida, Delray Beach


Note sure exactly what is going since I tried it on different Windows Versions as well as Access Versions 2007, 2010, and 2016. All worked fine. Did you change anything in the Code?
Go to the top of the page
 
ADezii
post Sep 17 2019, 10:44 AM
Post#19



Posts: 2,679
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. I am willing to guess that the problems that you are having are related to your 64-bit environment and the API Calls. I have completely eliminated any API reference and substituted it with a simple InputBox for the Folder Input. This is not the ideal solution, but it is only a Demo, and it should correct the problem. When prompted for a Folder simply enter a valid Folder location with/without a Trailing Backslash ('\'). The Code will compensate either way. There is minimal validation, basically to insure that a Folder location is valid. You will still need the DAO, ADO, and Scripting Runtime References.
  2. I do have a work-in-progress that touches upon all aspects of the BLOB approach, namely: Bulk Loading of Graphics, single Graphic File Loading, retrieving, saving, and editing of BLOB Images, displaying BLOBs in Image Controls, displaying Graphic File information in a Text Box Control, etc. It consists of a single Form (displayed below), a Parent & related Child Form, and a Standard Code Module. All functionality is contained within the Access DB. I'll only Upload the DB if you, or someone else, is actually interested.

Attached File(s)
Attached File  BLOB_Demo.JPG ( 69.09K )Number of downloads: 7
 
Go to the top of the page
 
ordnance1
post Sep 18 2019, 03:38 AM
Post#20



Posts: 684
Joined: 7-May 11



Only change to the code I made was adding PtrSafe in the declare statements so it will run (at least it does not error) on 64 bit Access, but no luck. So if you are willing to attach the newer version it would be appreciated.
Go to the top of the page
 
3 Pages V  1 2 3 >


Custom Search


RSSSearch   Top   Lo-Fi    23rd October 2019 - 08:12 AM