Query to Display Picture from File System (not Database)

[OldBirdman]

What a mess this question is. I have spent 2 weeks trying to make it concise and clear, and I can't. I do not have the vocabulary for the question. I cannot even TITLE it correctly. Here is my best effort.

Conditions:
80,000+ pictures (These are pictures of birds, ID by Species, Sex, Age, etc. (If known))
Hierarchy of folders:
Continent->Country->State(if India, Mexico, USA, Canada, & Australia) (No state for other countries)

I am developing an Access program to manage these. Of course, the main table will contain 80,000+ records, 1 per picture, with fields for all properties mentioned above. It would also contain the [Drive:\Path\] filename to the picture.

I am writing a "Filter" routine to select a subset of the pictures, i.e. Ravens in USA; or all birds of Romania, or Genus=Passer (House Sparrow, etc).

If I put the pictures in the table within Access, it becomes very large and slow, and it is difficult to manage. I would like to have the pictures in the hierarchy of folders mentioned above, and let Microsoft manage them. This also allows me to get to them with various photo programs, i.e. PhotoShop. So I reject any solution that has the pictures imbedded in Access.

If the result of my FilterQuery is to be 1 picture at a time, I can do that. But if I want a form with continuous records, each showing the correct picture, I don't know how to do that. Any ideas?

To state the problem another way, I need a query that returns the subset requested (I can do that OK), but has another field, PictureOfBird, that is an image. Then form would display all pictures found. The SQL Statement would have to be something like "SELECT Key, PictureFileName, PictureOfBird AS xxx AS Image FROM TablePictures;" This is not a valid SQL statement. I am using the keyword "AS" incorrectly. In SQL, "AS" means alias, within AccessVB, "AS" means DataType. My point is that I want a table with 3 fields. Field 1=Record Key; Field 2=PictureFileName(Drive&Path&FileName to Image); Field 3=Image Field with picture.

This is not clear. If my table stored the image, I could query "Select Key, PictureName, PictureOfBird FROM TablePictures WHERE Genus='Corvus'" and have a continuous form showing all my pictures of Crows, and Ravens.

I hope some of this makes sense, as I don't have the vocabulary to really define what I want. Thank you.

OldBirdman

[NeoPa]

I can't look at this now old-buddy, but I think I get the question at least.
Let me see what I can find out later.

[NeoPa]

It comes down to being able to upload a picture on to the form as you go.
The information as to where the picture is stored can be retrieved easily enough for each record. From there the question is :-
Is it possible to go off, get and display the picture, on a record by record basis, as the query results are shown?

[Minion]

Sorry if this is a bit crude in the logic department as I'm more or less teaching myself as I go on things, so pleae bear with me.

Here it goes:

It seems to me that the best way to do this would be generate a recordset with the values you desire (which seem to be primarily the Key and the path). From there the recordset would be bound to a form with two text boxes (one for the Key, one for the path info) and an image object. The trick is making the image object linked and setting the path dynamically from path returned by the recordset.

Unfortunately, I am unable to provide the necessary coding here as I'm not sure of each step involved. However, this is the direction I would look as it seems that it would provide a dynamic solution without having to produce a permanent table.

Hope this helps.

Minion

[OldBirdman]

Minion defines the problem more clearly than I did. NeoPa asks questions I don't know.

When a control on a continuous form has properties other than the data in the underlying table, is it possible to have different records have different properties. For instance, could I set background color to yellow if the value were positive, and to violet if the value were negative? Or alternate ForeColor on every other record. Seems similar.

If I put an unbound image field in each record, Access wants a picture name (i.e. Directory location). If I try to delete the name in design mode, Access deletes the image field, No Picture, No Control. If I give an empty string for the picture, Access says "Can't open file".

I can put the image field in the Footer or Header, and when I select a record in the Detail Section (Continuous), display the picture. But that doesn't allow me to grab the scrollbar and visually scan faster than clicking each record.

I know that there are commercial picture management programs out there. The ones I have looked at don't easily and quickly allow complex filtering. Most of the filtering is either on the file name, or if they have other fields within the database, they must be filled individually. So Slow.

If I don't find an answer in a week, I'm going to abandon the attempt with Access as it currently is.

OldBirdman

[NeoPa]

Minion defines the problem more clearly than I did. NeoPa asks questions I don't know.

I was just asking the question that, as I see it, is at the heart of your question.

When a control on a continuous form has properties other than the data in the underlying table, is it possible to have different records have different properties. For instance, could I set background color to yellow if the value were positive, and to violet if the value were negative? Or alternate ForeColor on every other record. Seems similar.

No! This is a fairly frequently asked question here at TSDN.
However, using conditional formatting you can produce similar results.

If I put an unbound image field in each record, Access wants a picture name (i.e. Directory location). If I try to delete the name in design mode, Access deletes the image field, No Picture, No Control. If I give an empty string for the picture, Access says "Can't open file".

This concept may be worth progressing with. Is it possible to have a low-overhead picture in here which is changed on-the-fly using the OnCurrent event procedure perhaps?

I can put the image field in the Footer or Header, and when I select a record in the Detail Section (Continuous), display the picture. But that doesn't allow me to grab the scrollbar and visually scan faster than clicking each record.

This may have to be the fallback option if we can find no better way to do it.

I have asked some of the other Access experts to give this question a look. Some of them certainly understand graphics issues better than I do.

[ADezii]

What a mess this question is. I have spent 2 weeks trying to make it concise and clear, and I can't. I do not have the vocabulary for the question. I cannot even TITLE it correctly. Here is my best effort.

Conditions:
80,000+ pictures (These are pictures of birds, ID by Species, Sex, Age, etc. (If known))
Hierarchy of folders:
Continent->Country->State(if India, Mexico, USA, Canada, & Australia) (No state for other countries)

I am developing an Access program to manage these. Of course, the main table will contain 80,000+ records, 1 per picture, with fields for all properties mentioned above. It would also contain the [Drive:\Path\] filename to the picture.

I am writing a "Filter" routine to select a subset of the pictures, i.e. Ravens in USA; or all birds of Romania, or Genus=Passer (House Sparrow, etc).

If I put the pictures in the table within Access, it becomes very large and slow, and it is difficult to manage. I would like to have the pictures in the hierarchy of folders mentioned above, and let Microsoft manage them. This also allows me to get to them with various photo programs, i.e. PhotoShop. So I reject any solution that has the pictures imbedded in Access.

If the result of my FilterQuery is to be 1 picture at a time, I can do that. But if I want a form with continuous records, each showing the correct picture, I don't know how to do that. Any ideas?

To state the problem another way, I need a query that returns the subset requested (I can do that OK), but has another field, PictureOfBird, that is an image. Then form would display all pictures found. The SQL Statement would have to be something like "SELECT Key, PictureFileName, PictureOfBird AS xxx AS Image FROM TablePictures;" This is not a valid SQL statement. I am using the keyword "AS" incorrectly. In SQL, "AS" means alias, within AccessVB, "AS" means DataType. My point is that I want a table with 3 fields. Field 1=Record Key; Field 2=PictureFileName(Drive&Path&FileName to Image); Field 3=Image Field with picture.

This is not clear. If my table stored the image, I could query "Select Key, PictureName, PictureOfBird FROM TablePictures WHERE Genus='Corvus'" and have a continuous form showing all my pictures of Crows, and Ravens.

I hope some of this makes sense, as I don't have the vocabulary to really define what I want. Thank you.

OldBirdman

This would be a simple matter if your were in Single Form View, since each Graphic could dynamically be loaded into an Unbound Image Control at run time via the Form's Current() Event. This would involve a simple DLookup() to the PictureFileName for the current [Record Key]. But you wish to use a Continuous Form View which will never allow the previous logic, because the Current() Event is not fired for every single Record in Continuous View. In my opinion, as long as you insist on this View in the current context, you will continue to run into a host of problems.

[NeoPa]

There appears to be a fundamental issue with this concept.
An image control can only be bound to a field in a table if the "Picture Type" is embedded (makes sense really). If a control is unbound then it is not a separate entity for each (per) record. That means you could display an unbound image control for every record, but the picture would be the same in all of them (I know - I tried it).
Otherwise, setting the "Image Address" property for the Image control for each record (cycling through then returning to top at end) in the form's "Load" event would work nicely. In practice, it simply ends up with them all showing the picture for the last record of the recordset.
This is a real shame because I thought I had the whole thing down until I discovered that :(

[NeoPa]

To confirm that, have a look at technique #2 in Handling Images with Microsoft® Access. This was something given to me by one of the other experts (puppydogbuddy) when I asked for help in this thread.
You may find the whole article interesting.

[FishVal]

Hi, everyone.

Just as an alternative the following technique may be used.

• VBA function opens the query and put shortcuts to picture files to a temporary folder
• shell command opens the folder in explorer
• moreover win explorer ActiveX control (Class: Shell32.ShellFolderView) may be placed to an Access form

Regards,
Fish

P.S. ShellFolderView supports automation. If you find this promising I'll give you some code snippets when get acquainted a little with its object model. ;) You may do the same. MSDN: ShellFolderView class

P.S.S. Or you may use WebBrowser ActiveX control to explore folder contents on Access form. Programming of this control appears to be quite simpler.

[sierra7]

Hello
I take it from your first post that the crux of your problem is how to display your requirement as continous forms where, obviously, each record must meet a common criteria; yet you wish to select using variable criteria.

Could I therefore venture to suggest that you add a new field to your main table. It could be an Integer of a Yes/No field, call it 'Disp' or some such, then all you have to do is update it in accordance with your criteria.

So you would have an unbound master form with texts or combo controls to set the selection criteria. You will need a Public String variable for each attribute you wish to filter on and you set these each to empty string "" in the form Open event.

On the after update event of each of the controls you have something like follows to set the filter;

If Me.Control1 = "" Then
   stString1 =""
Else
  stString1 = "[Field1]=" Me.Control1
EndIf
 
Call DisplayPics

The criteria in the DisplayPics subroutine would just be the addition of all these 'string' you have setup, be they blank or not. The routine will not display the pics, just flag them to meet the display criteria (resetting them first) e.g

Private Sub DisplayPics()
 
'setup criteria string from adjusting controls
strCrit = stString1 + stString2 + stString3 + stString4 + stString5  'etc
 
'turn warning messages off
Application.SetOption "Confirm Action Queries", False
 
'reset to clear previous slection
DoCmd.RunSQL "UPDATE MainTable SET MainTable.Disp = False "
 
'set field Disp to affirmatory setting
DoCmd.RunSQL "UPDATE MainTable SET MainTable.Disp = True" & _
                  "  WHERE "  & stCrit  & ";"
 
'turn warnings back on
Application.SetOption "Confirm Action Queries", True

You will have to design you sub-form with it's picure control and there seems plenty of advise on how to do that.

You then have a choice of either filtering the data for the sub-form by using a query with the condition [Disp] = True, or just basing the sub-form on the main table and using Master/Child links to select the appropriate records. To do the latter you will need to include the field [Disp] in the sub-form (it can be hidden i.e. Visible = False), and a dummy [Disp] field on the main form which you would have to set True in the OnOpen event.

In my view that should do it, although on re-reading some of the posts it seems there may be an issue that the picure control needs a poke from the On Current event to go and find the right graphic file. That's not my recollection but I will check that out.

[sierra7]

My unreserved apologfies for the last post. I obviously missed the problem with graphics in continuous forms.

The truth is I had just come off Tesco's web site ordering whiskies for worthy clients where pictures of bottles are displayed in a continuous form (I hadn't been sampling them.. honest!) and thought "Ah this is easy, I can help here!"

On the bright side, the advice is still valid for the single form scenario but having just completed my ordering exercise I can see there may be some advantage to the user to view continuous forms, although the 'thumb-nails' are barely worth having.

I am going to try another attack where instead of having one sub-form I have four (you can't have many more on a screen because the pictures would too small to be useful), then see if I can synchronise them to seem as if they are scrolling.

Can anyone get there first !!

[NeoPa]

My unreserved apologfies for the last post. I obviously missed the problem with graphics in continuous forms.
...

Apologies not required!
I'm very pleased to see you're still offering help from your experience. That way leads to expert status if you're not careful ;)

Personally I've checked this out in my own test-bed and checked the link supplied and both lead me to believe I've reached the end of the road on this. If you manage to produce a solution though, I'll be very interested to read about it.

[ADezii]

What a mess this question is. I have spent 2 weeks trying to make it concise and clear, and I can't. I do not have the vocabulary for the question. I cannot even TITLE it correctly. Here is my best effort.

Conditions:
80,000+ pictures (These are pictures of birds, ID by Species, Sex, Age, etc. (If known))
Hierarchy of folders:
Continent->Country->State(if India, Mexico, USA, Canada, & Australia) (No state for other countries)

I am developing an Access program to manage these. Of course, the main table will contain 80,000+ records, 1 per picture, with fields for all properties mentioned above. It would also contain the [Drive:\Path\] filename to the picture.

I am writing a "Filter" routine to select a subset of the pictures, i.e. Ravens in USA; or all birds of Romania, or Genus=Passer (House Sparrow, etc).

If I put the pictures in the table within Access, it becomes very large and slow, and it is difficult to manage. I would like to have the pictures in the hierarchy of folders mentioned above, and let Microsoft manage them. This also allows me to get to them with various photo programs, i.e. PhotoShop. So I reject any solution that has the pictures imbedded in Access.

If the result of my FilterQuery is to be 1 picture at a time, I can do that. But if I want a form with continuous records, each showing the correct picture, I don't know how to do that. Any ideas?

To state the problem another way, I need a query that returns the subset requested (I can do that OK), but has another field, PictureOfBird, that is an image. Then form would display all pictures found. The SQL Statement would have to be something like "SELECT Key, PictureFileName, PictureOfBird AS xxx AS Image FROM TablePictures;" This is not a valid SQL statement. I am using the keyword "AS" incorrectly. In SQL, "AS" means alias, within AccessVB, "AS" means DataType. My point is that I want a table with 3 fields. Field 1=Record Key; Field 2=PictureFileName(Drive&Path&FileName to Image); Field 3=Image Field with picture.

This is not clear. If my table stored the image, I could query "Select Key, PictureName, PictureOfBird FROM TablePictures WHERE Genus='Corvus'" and have a continuous form showing all my pictures of Crows, and Ravens.

I hope some of this makes sense, as I don't have the vocabulary to really define what I want. Thank you.

OldBirdman

How about a Single Form consisting of a series of Image Controls (either generated dynamically or statically placed) into which the results of your Filter (various Paths to filtered Graphics) would be placed, e.g. all Ravens in Boise, Idaho? All related Graphics would be loaded into multiple Image Controls on a single Form (up to a Maximum number of course). I will not even attempt to try to implement this logic unless I know you are truly interested.

[sierra7]

OK, this works (sort of)

In the attached example there is an unbound MainForm (not filtering this time, just testing the multi pictures) which has 3 instances of the same sub-form.

There are three unbound textboxes on the MainForm called F1, F2 & F3. The Form Open event stores 1,2 & 3 in these boxes.

I've added a field called FrameNo to the main table and numbered the 5 test records 1 thro 5. The first subform has Master/Child links set as F1/FrameNo; similarly the second subform is set F2/FrameNo etc.

The control buttons at the side of the form (no scroll bars) either add or subtract 1 from F1, F2, F3 so that the form does the synchronising and the pictures change.

The main problem is that as each picture load Access gives a 'Helpful' message which I can't turn off, so the scrolling is not smooth but flickers.

How to implement this?? You will have to use the criteria you set in the earlier post to creat a recordset of the selected images then loop through these and populate FrameNo from 1 to whatever. (Obviously re-setting all to zero with an SQL statement before opening the recordset)

Is it worth it?? I would probably be biased to setting up a Report and Previewing that on screen. The OnFormat event would allow you to synchronise the graphic (I believe!!) A report would scroll smoother and you may zoom into the picture by clicking the mouse but obviously it would be paged.

But if you want the info on screen and to be interactive (so you can click on an image and see full screen etc) then this is a step in that direction.

Best of 'twiching!

[sierra7]

OK

I've hit the 100kb file size limit so had to exclude my jpegs from the zip

That means you will have to edit the data in the table to adjust paths and filenames to real images.

[sierra7]

Hi OldBirdman

I see that speed of scrolling was a part of the original requirement and my one click per new image would not be acceptable so after testing you might consider adjusting the code for the buttons to add 2 , 3 or 4 each time you clicked (depending on your final screen sizinga nd layout) to scroll faster than one image per click (or even add a speed control!)

I'm wondering whether this thread should be refered to the ASP experts? The data could be held in Access tables and then displayed in HTML/ASP forms.

I did a lttle ASP work a few years ago, then they changed it to dot.NET, then dot.NET 2 or something and I did not have time to keep up. Basically it involved retrieving a recordset then looping through it writing the data elements into a table. A picture is just a text (HTML) reference to a jpeg an it is the browser that renders the image so I can't see that there should be a problem (other than pixels and image size etc)

NeoPa
Thanks for your comments. I have no intention of becoming an 'expert'. I have to continue to make a living of my own but unfortunately find other peoples problems more interesting than mine and I get sucked in!

[NeoPa]

...NeoPa
Thanks for your comments. I have no intention of becoming an 'expert'. I have to continue to make a living of my own but unfortunately find other peoples problems more interesting than mine and I get sucked in!

We'll just have to wait a while then and see if we can't suck you in further when your guard's down ;)
Seriously though, we appreciate any and all help that you give but the level and amount will always be entirely down to you.

[FishVal]

Hi, everyone.

Just as an alternative the following technique may be used.

• VBA function opens the query and put shortcuts to picture files to a temporary folder
• shell command opens the folder in explorer
• moreover win explorer ActiveX control (Class: Shell32.ShellFolderView) may be placed to an Access form

Regards,
Fish

P.S. ShellFolderView supports automation. If you find this promising I'll give you some code snippets when get acquainted a little with its object model. ;) You may do the same. MSDN: ShellFolderView class

P.S.S. Or you may use WebBrowser ActiveX control to explore folder contents on Access form. Programming of this control appears to be quite simpler.

Hi, guys.

Here is a simple implementation of my suggestion.
A reference to "Microsoft Script Host Object Model" (..\WINDOWS\system32\wshom.ocx) has to be set.
BTW a very useful ActiveX providing tools for

• working with file system (the same as in "Microsoft Runtime Scripting")
• working with Windows Registry
• networking
• run/terminate application
• and, you will not beleive, message box autoclosing after predefined time interval ;)

Table: tblPictures
keyPictureID Autonumber(Long), PK
txtPath Text(255) full path to a picture file

Code module:

Option Compare Database
Const ViewFolder = "C:\Temp"
 
Public Sub CreateShortCut(strTarget As String)
 
    Dim objShell As New IWshRuntimeLibrary.wshShell
    Dim objShortCut As IWshRuntimeLibrary.WshShortcut
 
    Set objShortCut = objShell.CreateShortCut(ViewFolder & "\" & _
        GetFileName(strTarget) & ".lnk")
 
    With objShortCut
        .TargetPath = strTarget
        .Save
    End With
 
    Set objShortCut = Nothing
    Set objShell = Nothing
 
End Sub
 
Public Function GetFileName(strPath As String) As String
    GetFileName = Right(strPath, Len(strPath) - InStrRev(strPath, "\"))
End Function
 
Public Sub FillFolder()
 
    Dim RS As New ADODB.Recordset
    Dim fso As New IWshRuntimeLibrary.FileSystemObject
 
    For Each f In fso.GetFolder(ViewFolder).Files
        f.Delete
    Next
 
    With RS
        .ActiveConnection = CurrentProject.Connection
        .LockType = adLockReadOnly
        .CursorType = adOpenForwardOnly
        .Open "tblPictures"
        While Not .EOF
            CreateShortCut !txtPath
            .MoveNext
        Wend
        .Close
    End With
 
    FollowHyperlink ViewFolder
 
    Set fso = Nothing
    Set RS = Nothing
 
End Sub
 

The target folder ("C:\Temp" in the example) must exist (just left this to let manually change the appearance of the folder).

Certainly, though the code is working, its purpose is just to demonstrate that kind of approach. The mdb file with empty [tblPictures] will be attached to the post.

Regards,
Fish

[ADezii]

What a mess this question is. I have spent 2 weeks trying to make it concise and clear, and I can't. I do not have the vocabulary for the question. I cannot even TITLE it correctly. Here is my best effort.

Conditions:
80,000+ pictures (These are pictures of birds, ID by Species, Sex, Age, etc. (If known))
Hierarchy of folders:
Continent->Country->State(if India, Mexico, USA, Canada, & Australia) (No state for other countries)

I am developing an Access program to manage these. Of course, the main table will contain 80,000+ records, 1 per picture, with fields for all properties mentioned above. It would also contain the [Drive:\Path\] filename to the picture.

I am writing a "Filter" routine to select a subset of the pictures, i.e. Ravens in USA; or all birds of Romania, or Genus=Passer (House Sparrow, etc).

If I put the pictures in the table within Access, it becomes very large and slow, and it is difficult to manage. I would like to have the pictures in the hierarchy of folders mentioned above, and let Microsoft manage them. This also allows me to get to them with various photo programs, i.e. PhotoShop. So I reject any solution that has the pictures imbedded in Access.

If the result of my FilterQuery is to be 1 picture at a time, I can do that. But if I want a form with continuous records, each showing the correct picture, I don't know how to do that. Any ideas?

To state the problem another way, I need a query that returns the subset requested (I can do that OK), but has another field, PictureOfBird, that is an image. Then form would display all pictures found. The SQL Statement would have to be something like "SELECT Key, PictureFileName, PictureOfBird AS xxx AS Image FROM TablePictures;" This is not a valid SQL statement. I am using the keyword "AS" incorrectly. In SQL, "AS" means alias, within AccessVB, "AS" means DataType. My point is that I want a table with 3 fields. Field 1=Record Key; Field 2=PictureFileName(Drive&Path&FileName to Image); Field 3=Image Field with picture.

This is not clear. If my table stored the image, I could query "Select Key, PictureName, PictureOfBird FROM TablePictures WHERE Genus='Corvus'" and have a continuous form showing all my pictures of Crows, and Ravens.

I hope some of this makes sense, as I don't have the vocabulary to really define what I want. Thank you.

OldBirdman

OldBirdman, your plight has touched me. I am currently working on a simple interface that will allow Graphic Images to be stored internally in a Table which contains an OLE Object Field to store the Binary Data that comprises the Image itself (BLOBs). BLOBs are by far the most efficient mechanism for storing Graphic Images since they are stored internally on a Byte-by-Byte basis, there is no problem with synchronizing with File Paths, the overhead is minimal when compared to Linking or Embedding, etc. Since they are 'Bound', you could probably implement your Continuous Form approach (can't see why not). Please be patient, assuming all goes well, "The Return of the BLOB" should be next week's Tip.

P.S. This is TOP SECRET information that I am passing on to you, even Mary and NeoPa are unaware of this. After you read this post, kindly destroy it since eating it may prove a little distasteful. (LOL).

[NeoPa]

A BLOB article sounds like an excellent idea ADezii.
I'm not sure (as the OP explicitly states he doesn't want the data stored in his database) that it will be the answer here, but suggestions never hurt anyway.
In similar vein, I would make a suggestion that the images (in whichever form is best - I'm no expert there) could possibly be stored in a linked Back-End database. I appreciate that this is not exactly what was requested, but may be an acceptable compromise, depending on OldBirdman's requirements.
The code to upload the images from pre-existing text fields should be quite straightforward if required.

[sierra7]

Obi was against BLOBs which was probably why he put (not Database) in the title but it's good to know how to make them work anyway.

The multi-subform solution does work so I have reduced the image sizes so all can be zipped below 100kb to let you try without fiddling with changing filenames.

I'd like to kow how to suppres the instantaneous warning messages each time a graphic loads as 'DoCmd.SetWarnings False' does not seem to influence it.

Any Ideas?

[ADezii]

Obi was against BLOBs which was probably why he put (not Database) in the title but it's good to know how to make them work anyway.

The multi-subform solution does work so I have reduced the image sizes so all can be zipped below 100kb to let you try without fiddling with changing filenames.

I'd like to kow how to suppres the instantaneous warning messages each time a graphic loads as 'DoCmd.SetWarnings False' does not seem to influence it.

Any Ideas?

1. Have you tried turning OFF/ON Screen Updating?
   

   Application.Echo False
   Application.Echo True

2. If you are referring to the Graphic Loading Status Message that appears each time a Graphic is loaded, I had this pesky problem when I was working on an Inventory Application which dynamically loaded thousands of Graphic Images. I converted all the *.jpg Images to *.bmp and never had the problem again. Please don't ask my why this worked, because I don't have the slightest idea.

[ADezii]

A BLOB article sounds like an excellent idea ADezii.
I'm not sure (as the OP explicitly states he doesn't want the data stored in his database) that it will be the answer here, but suggestions never hurt anyway.
In similar vein, I would make a suggestion that the images (in whichever form is best - I'm no expert there) could possibly be stored in a linked Back-End database. I appreciate that this is not exactly what was requested, but may be an acceptable compromise, depending on OldBirdman's requirements.
The code to upload the images from pre-existing text fields should be quite straightforward if required.

In my twisted way of thinking, I was under the impression that the OP did not want the Image Data stored internally because of the following reason:

If I put the pictures in the table within Access, it becomes very large and slow, and it is difficult to manage.

The storing of these Images as BLOBs will greatly reduce the DB bloat, he may then wish to pursue this path, which should definitely be the most efficient solution in my mind. Just tell me if I'm ranting and raving again. (LOL).

[NeoPa]

I'm pretty sure it's not something he would have envisaged at the start ADezii, but as the original idea (though neat) turned up nothing usable, he may well want to compromise, and your solution is certainly worth considering. If not for the OP (OldBirdman) then possibly for others who stumble (google) across this thread later on. As I said, no suggestions are a problem. The worst they can be is little extra help.

[sierra7]

1. Have you tried turning OFF/ON Screen Updating?
   

   Application.Echo False
   Application.Echo True

2. If you are referring to the Graphic Loading Status Message that appears each time a Graphic is loaded, I had this pesky problem when I was working on an Inventory Application which dynamically loaded thousands of Graphic Images. I converted all the *.jpg Images to *.bmp and never had the problem again. Please don't ask my why this worked, because I don't have the slightest idea.

Application.Echo did not suppress the Graphics Loading Status message, but converting to *.bmp did. Brilliant !

The jpeg file type is a format that compresses the file depending upon content(e.g. my sample *.jpg sizes varied from 4 to 10kb, but the *.bmp are all 79kb) so I'm guessing that the message is attributable to a process that expands the image prior to painting the screen.

I still think the multi-subforms solution has potential and involves less changes to the database than importing 80,000 images. I would imagine that ideally, these should all be converted to thumb-nails as if the pictures are of any quality they will be megapixels each, but that is another aspect outside the present scope.

I have one more idea before chucking the towel in on this one!

[sierra7]

Right, I think the attached works fine! And only one line of code!

A window showing multiple images and a scroll bar down the side, that behaves like a scroll bar should. The images are left outside the database although I have reduced them to miniscule .bmp's for the demo.

OK, the underlying form is currently limited to 5 'hits' but that's just a matter of copy and paste to extend it to whatever the limit of a form length has. I can envisage constructing the form 'on the fly' depending upon number of records retrieved by the 'select' but that would involve a fair bit more coding.

I guess it it too easy a solution to satisfy you experts but I think it meets OldBirdman's criteria.

[ADezii]

Application.Echo did not suppress the Graphics Loading Status message, but converting to *.bmp did. Brilliant !

The jpeg file type is a format that compresses the file depending upon content(e.g. my sample *.jpg sizes varied from 4 to 10kb, but the *.bmp are all 79kb) so I'm guessing that the message is attributable to a process that expands the image prior to painting the screen.

I still think the multi-subforms solution has potential and involves less changes to the database than importing 80,000 images. I would imagine that ideally, these should all be converted to thumb-nails as if the pictures are of any quality they will be megapixels each, but that is another aspect outside the present scope.

I have one more idea before chucking the towel in on this one!

I'm guessing that the message is attributable to a process that expands the image prior to painting the screen.

I agree with you on this one - must be the decompression Algorithm causing the problem. It literally drove me crazy for months!

[NeoPa]

I agree with you on this one - must be the decompression Algorithm causing the problem. It literally drove me crazy for months!

I'm afraid the proof of that is visible now for all to see (LOL).
I'm sorry ADezii - I couldn't resist such a tempting prompt :D

[OldBirdman]

Hi ALL,

I am absolutely overwhelmed with the responses to this question. For that I thank you. I can't keep up with this much information. It has taken me 4 days beyond the last post to begin to understand all you have written.

I can't say I understand everything proposed. I seem to use a very limited subset of Access, the part that 1) I understand, and 2) doesn't require me to check something in Tools->References... The reason for 2 is that I never know what to check, and don't know how to find out. Don't answer on this thread.

The subform solution, as per zip file by sierra7 looks like what I was after. I have no idea how to make thumbnails, how to develop a form 'on the fly' and other issues, but heh! Also, I believe I will run up against a Form Size Limit of 22". (1440 twips * 22 inches = 31680, the size of an integer. Did Microsoft only use 2 bytes for the form length? Or is this a coincidence?) As shown is sierra7's demo, the section that contains the subform w/picture is about 9/10", allowing 24 pictures, max.

Solutions mentioning Shell, Shortcut, etc are beyond me, altho they look interesting, I don't understand how to implement. Past attempts by me have sometimes worked, but then I got messages about libraries not found, and don't know how to resolve.

The images I want to deal with are .jpg, from 70K to 400K. Anything over 400K I have reduced the resolution. I also convert from .bmp if I get them that way. I have reduced flicker by changing the registry, but I don't like to do that as I don't know how to save the registry and reload it if something goes wrong. In this case, I found a reasonably simple article on Microsoft's website.

I am currently using the single picture solution with a continuous form and an unbound image of the chosen row. Thanks all for the idea, but it is tooo sloow and cumbersome, as it requires too much work for my tired mouse (scroll bar to record to scroll bar to record to . . . .). I do better with my own 'Next' & 'Previous' buttons that are large, conveniently placed, and will allow an 'auto-next' when held down.

So much energy went into this question that I feel it must be something others want to do also, So, at the risk of keeping a thread open well beyond its expiration date, I want to suggest this:

Dim rst as Recordset
Dim strSQL as String
Dim strWHERE as String
 
strWHERE = BuildWhereClause(a,b,c) 'Assume returns "Genus='Corvid'"
 
'SQL Statement for Pictures to Display
strSQL = "ALTER TABLE (SELECT Desc, Filename FROM tbl WHERE & strWHERE & ") ADD imgPic Image"
'             ~~ or ~~
strSQL = "SELECT Desc, Filename, imgPic FROM (ALTER TABLE tbl ADD imgPic Image) WHERE " & strWHERE
 
Me.RecordSource = strSQL
Set rst = Me.RecordsetClone
rst.MoveFirst
Do Until rst.EOF
    rst!imgPic = rst!filename 'This won't work either
Loop
 

Above code is pseudocode, as I don't really know how to write it. I get syntax errors or JOIN errors or it doesn't work, no matter how I try to write it. This should give a temporary recordset, with included pictures, but not 80,000, and then the image control on the form can be bound. All done on the fly. Any solution that creates an actual table, or adds an image to an existing table causes the database to balloon. Even though I can write code to clean things up, a daily 'Compact Database' is required.

Is the above code possible? I haven't tested the code, as I can't write the SQL Statement, and if an SQL statement can't be written, I have no use for the code. The rst!imgPic = rst!filename won't work, as filename is text, not an image. There I would need to assign the file contents, not the file name. But the query has to work first.

Can this logic go anywhere?

OldBirdman
P.S. I will not be able to check responses until after Christmas.

[sierra7]

Hi OBi
Yes I guessed there might be a limit to the number of sub forms you could include on a 'page' but that would depend how big you wanted your pics and how many hits you get per query, as to how big the problem would be. I'm thinking along the lines of 20 pics with buttons at the bottom of the scrolled page to say 'Next 20', 'Prev 20' etc (I would probaly have duplicate buttons at the top of the page and a reset button too) I discussed the code behind similar buttons in a previous post. Forget any ideas about building the form in code at run-time (i.e. on-the-fly, I must remember to speak English, not Anglo-Saxon!) it's too much of an added complication.

I have to say I cannot get my head round your script. The ALTER TABLE is throwing me. This is a command to alter the structure of a table (e.g. add column or index) If you wanted to extract a sub-set of the data (matching your query conditions) to a temporary table then I would expect a CREATE TABLE but personally would leave any 'temporary' table intact and just DELETE or INSERT INTO it as necessary.

However, I would not contemplate using temporary tables unless the performance proved particularly bad. I can see that if there were 20 sub-forms all based on the full database of 80,000 records then there may be some performance issues. But the forms are identical and Access might just cope. Only one record is shown in each sub-form and that is the one where the FrameNo matches the F-No field in the Main form.

I have posted how I would tackle the 'filtering' but if you needed further clarification I could could prepare a demo version.

I did not write an example of code that loops through the selected recordset and numbers the 'FrameNo' field as they will appear in the 'scroll window'. If you want to go this route and need help let me know.

Sorry about confusing you with 'thumb-nails'. It's a general term for describing a minature version of a full size picture, that's used to give quicker response particulary over the internet. It means you need two files for each picture (and two locations of where they are stored) It may be something you need to look at to speed things up in the future but is off-topic at present.

Best of luck

[OldBirdman]

OK - I give up. I can use Windows File Manager (View->Thumbnails) for the overview, and the one-at-a-time for the Access program. Windows is slow (Flashlight searches for many seconds for 500 files), and then is quite slow generating the thumbnails for display.

It just seemed that if I could "SELECT [txt1], [txt2]" and I could "SELECT [txt1], [txt2], [txt1]&[txt2] AS [txt3]" (txt3 cannot be changed) then why can't I either 1) create an [img1] without a value (but I would need to specify that it was an image), or 2) add [img1] to the recordset created by "SELECT [txt1], [txt2]" (using some keyword such as ALTER). Another approach might be to have a table with one record, an image (probably a key also), and SELECT with a JOIN. This would give a recordset with an image field, but I have been unable to change the image in the recordset. Seems to be "Read Only", because Access cannot save multiple images in a single record in the underlying tables.

Many thanks to all who contributed to this question. I mostly understood what was being suggested, and tried all the attached programs. Sure, I'm a little slow to digest all the responses and reply, but I do understand.

To Sierra7, you put a lot of effort into this. I didn't notice your speaking Anglo-Saxon vs. English. Thank you for all your effort. I do know what a thumbnail is, but said I did not know how to create one. Not important now, however.

I'm going to go on to other questions, hopefully simplier.

OldBirdman

[sierra7]

Hi again OBi
Don't give up you are nearly there!

I'm interested in your problem because my wife keeps taking pictures on her digital camera and expects me to find particular frames when she want reprints for her freinds. It was ok when there were only 36 on a film but she now gets over 500 on a chip and the resolution has gone from 2 to 7 Megapixels

As far as creating thumbnails (sorry if I came accross condecendingly) you can do them one at a time by 'Resizing' in a graphics program. While this is impractical for your volume of work Photoshop may be able to do this as a batch process. I use Paintshop Pro 7 which does not seem to have a batch facility for resizing, so I looked on the Internet and found a freebee 'Easy Graphics Converter'.

This converted a whole folder of 480 files at 2.5Mb (7 Megapixel jpg's) to 88kb bmp files (200 x 150) in 3 minutes, retained their existing file name while adding a suffix, in a new folder. Your portfolio should take maybe an hour.

If you really have overcome the 'flicker' problem with jpg's (the MS reference would be nice) then the same program produces 6 to 12kb jpg thumbnail files which might be quicker to access.

The reason for all this thumbnail business is to speed-up the searching process to identify the frames you want then to presumably view the selected picture full size. You would not need to reduce your original source material (other than to produce the thumbnail) and this becomes more of a problem as digital cameras reach higher resolutions.

As a final thought, perhaps storing your thumbnails as BLOBs in the database would yield a hybrid solution not restricted to the 20 frames per page of the sub-form solution, and which would allow you to keep your source material external to the database as per your original requirement.

Happy New Year!

[Megalog]

This has been an interesting thread!
At my job, we work with tons of graphics, so it became quite a task to try to make a contact sheet form that would display all the graphics that are currently marked as 'active', from our library (All graphics are stored externally, none in the db itself).
We quickly found out that a continuous view wouldnt do it, for the same reasons mentioned above. So we ended up putting multiple image controls on a form, and then dynamically setting the image path for each control. The page numbering is handled by the total number of images divided by how many controls we have per form page.
At first this worked, but then we had hundreds of WMF images, and the way the code worked, it tried to preload all the graphics into memory first... which resulted in the machines freezing up as the virtual memory started to exceed a few gigabytes in size..
Then we went with the activeX thumbnail control, which generated thumbnail sized images on the fly, and it cut down the load time by a lot. The only problem with the thumbnail control, is we cant get it to show up on printing =/
We ended up abandoning the project since it was just too much of a pain figuring it out... and our time right now is spent updating our project to 2007.

Also, I'm not sure what the 'flicker' you guys are referring to is... if it's the jpg/gif/png 'loading' progress bar (which might flicker in and out of view), it can be supressed with the right registry entries. If you guys want it, I have it in a .reg file I use for client machines. (the loading indicator was crashing machines when they tried to scroll too quickly through graphic records, so I disabled it)

[sierra7]

Hi Megalog
You are right that the 'flicker' referred to is the 'progress loading bar'. OldBirdman said he had a registery fix but I have spent an hour or more searching the MS site without finding anything, so the heads up on this topic would be good.

This thread has certainly made me realise that any serious solution (for rapid access to and browsing of graphic material) must involve 'thumbnails'. Most graphics browsing packages manage to produce these on the fly but this adds to the waiting time. A database solution must have these pre-stored, either internally or externally.

I believe that the selection/filtering process and the on-screen display has been adequately covered above. The more interesting aspect would be how to simplify the addition of data to such a system to start with. The mention of an ActiveX control to create thumbnails is of interest here as this could be made a sub-function of the data addition process (i.e. populating the search fields)

Obviously, one of the strengths of Access is it's Relational Database facilities which can be put to use to create look-up fields but there is also the case where multiple 'frames' have resulted from a particular 'shoot' and will therefore carry common description, which would result in a one-to-many relationship.

If thumbnails are saved internally then I believe the 'flicker' issue should not be a problem but I would still like to evaluate a comparison of storing them externally. I find that if they are less than 200x150 pixels they lose too much detail so would prefer to store them as JPG, so would still like to remove the 'flicker'/'progress loading bar' problem.

Many thanks.

[OldBirdman]

I got rid of the 'flicker', the message that said "Importing . . . ", or something similar. I Googled the actual message, and got several websites describing how to change the registry to eliminate the problem.

I don't remember the web address I actually used, but "http://www.ammara.com/access_image_faq/access_loading_image_dialog.html" appears to be a site I looked at then. If not, it is a clue to what you need, and why. Hope this helps.

OldBirdman

[OldBirdman]

This thread has gone well beyond the initial question. That is good, as it brings to the forefront certain issues that we all encounter.

The "Flicker" problem is a holdover from slower computers. There used to be a significant time lag between the time the picture path was loaded, and the picture could be displayed. This is now so fast that we are referring to it a Flicker, not as a status box, with a progress bar.

If this thread is to continue, then we need to look at the underlying problem. What is that, you ask. Our Access tables are secure. They cannot be changed without being in the Access program that alters them. We can validate the entries, prompt the users, and in many ways assure that the data is valid and complete.

Not so with images. Storing images within Access is impractible, as the tables quickly become so large that they violate the rules of Access. The best method is to store the pictures within the Windows File Structure. Then store the location in the Access table. Very simple, very clean, very precise. NO WAY!!!

If the pictures are stored in the Windows file structure, they can be added and/or deleted outside the Access program. Every camera purchased today allows direct downloads to a computer, but does not require that Access be involved. Copy, Move, & Delete are allowed in Windows, and will make a mess of your Access programs.

The vba statements within Access to deal with this are klutzy. But they are adequite, and they work. I am currently working on 2 subroutines to syncronize my database with my file structure. The first is to find "Orphans". This should find all images in the file structure but not in the database. I thought this could not happen, as whenever I deleted a record with a path to an image, I deleted the image. Very good, OldBirdman, you do good! Yeah, but when I sycronized my laptop and my desktop, some of the images got restored. Or my removable, backup harddrive restored the deleted images. So I got orphans.

The other half the problem is database references that have no pictures. Same thing, I get outside Access, and anything can happen. Again, the code isn't complicated, but it has to be addressed.

Is there an essay on this somewhere on this site? I could not find it, if it exists.

OldBirdman

I guess the form needs a timer, and every n seconds it has to look at Windows file structure and see if there are any changes. I don't yet know how to do that (find if any changes, not the timer). But only if in "Maintenance" mode, not some other mode.

[sierra7]

Thanks for the above reference. There is a lot more interesting advice on that website but I noticed that their solution to the 'continuous forms' problem (using multiple image controls on a page) was similar to what has been discussed in this thread.



S7

[OldBirdman]

You saw the answer to the question that I asked? Could you elaborate? What is the reference, or what is the link. I must have missed this, and I still miss it, looking at the site now.

What link are you referring to?

OldBirdman

[sierra7]

You saw the answer to the question that I asked? Could you elaborate? What is the reference, or what is the link. I must have missed this, and I still miss it, looking at the site now.

What link are you referring to?

OldBirdman

Hi OBi
Sorry if I added to confusion here. The article I was referrring to is in Samples and is called "Continuous Image Forms in Access - Paging" at www.ammara.com/support/samples/showsam084d.html

Basically, they agree with this thread that continuous forms CAN NOT be used and use multiple images on a page instead, even when using their own ActiveX component. However, they claim that this component avoids the 'database bloat' associated with linking using the MS component.

I don't have a problem with paging (Google-Image shows just 20 pics at a time, then pages to the next 20). I believe (if you want a fast response) you MUST generate (and store) thumbnails to start with, then call these into your 'scrolling' screen to browse the images. I bet Google don't try and generate thumbnails on the fly!

I think this has also been endorsed by Megaglogs experience.

I noted you will have issues on synchronisation and I believe that the starting point here is a WRITTEN PROCEDURE of how things are to work. If you can't write it down then you can't write a program for it. Writing it down logically makes it easier to program. (Am I teaching an OldBirman to suck eggs ??) Nothing should be allowed to happen Ad Hoc.

How many users have access to the system(s)? The notion of having a timer to keep checking everthing is still in place gives me nightmares! If there is a real danger of pictures moving or being deleted accidentally, then they should be placed in hidden folders. If they are on stand-alone computers where you cannot fully implement access control you should investigate third party 'File Lock' products that require passwords to write or delete files in certain folders.

We are some way off the original thread here but it has been an interesting journey!
Happy new year by the way.
S7

[Megalog]

I don't have a problem with paging (Google-Image shows just 20 pics at a time, then pages to the next 20). I believe (if you want a fast response) you MUST generate (and store) thumbnails to start with, then call these into your 'scrolling' screen to browse the images. I bet Google don't try and generate thumbnails on the fly!

I think this has also been endorsed by Megaglogs experience.

Actually we do generate all thumbnails on the fly, since the graphics get updated so much during production, we have to ensure what we see is the latest and greatest... Now if we could just find a way to get the form to print AND show the thumbnails that are on screen, we'd actually get some use out of it.

[sierra7]

Now if we could just find a way to get the form to print AND show the thumbnails that are on screen, we'd actually get some use out of it.

When you generate the thumbnails (on the fly) do you save them to disc?? If not, is that why the Report can not pick them up ?

Sorry if I am talking rubbish!

S7

[Megalog]

When you generate the thumbnails (on the fly) do you save them to disc?? If not, is that why the Report can not pick them up ?

Sorry if I am talking rubbish!

S7

Nope they dont get saved anywhere. They're generated on the fly, to unbound thumbnail controls. There's about 15 controls per page.. so on every page change they're regenerated. They show fine on the screen, but as soon as you hit print preview, they're empty. We're not sure if it's a control limitation or not.
The only way we've been able to capture them to print is through a screen capture =(

[sierra7]

Nope they dont get saved anywhere. They're generated on the fly, to unbound thumbnail controls. There's about 15 controls per page.. so on every page change they're regenerated. They show fine on the screen, but as soon as you hit print preview, they're empty. We're not sure if it's a control limitation or not.
The only way we've been able to capture them to print is through a screen capture =(

I've just had a muck around and knocked up a report in my db1 database and the images would not display until I put a line of code in the OnFormat event

 Me.Image8.Picture = Me.GPath

I only had one image control (Image8) and [GPath] is the path + filename of the Graphic.

You would obviously need 15 lines (one for each unbound image control) although I don't know how you would express a path to a file that is not saved to disc! I just cut and pasted the code from my form's OnCurrent event.

Hope this helps
S7

[OldBirdman]

I don't think I have trouble knowing what I want to do, or usually how to do it.

Modern programs such as Access do a lot of what used to have to be done on paper. I used to start with the Output, deciding what I wanted printed, and how things should be formatted. Working backwards, I assume I have the input, and so I can outline the program. The input is there, somewhere, but because those damn humans have had a hand in producing it, it isn't always exactly what I want.

Now, with Access, I can still start with the Report. Just create it, with all fields unbound. Then create a table with the necessary fields. Then create a form to view, edit, add, or delete rows in the table.

Procedures do need what you do suggest. I create the procedure and then use the space between the name and the end to outline the steps, in comments:
'1) Check arguments for validity
'1a) . . .
'1b) . . .

'9) Exit
This sketch becomes part of the code documentation, so there really is no wasted effort. I insert the various code lines between the comments.

If you review my posts, you will find that I can do something, or would like to do something (if I can ask, I want the idea in code and probably know where), but don't know how.

As has always been the case, the input & output are the detailed part. The stuff in between is usually easy.

Most of my pictures arrive by email, and are renamed and put in the appropriate folder. The Access program does the rename, and so the file name will match the name in the table. But the file structure is accessable to those pesky humans, no matter what I do. And some humans are idiots, and cannot/will not follow instructions. I write my code, but here it cannot be perfect. "You cannot defeat the idiots, because idiots are so clever!"

About sync between a computer and a backup hard drive, I do not know how to prevent a picture that has been deleted from being restored from a backup. Copy H:\Path1\Dir1\*.* to C:\Path2\Dir2 thinks the files on H:\ but that are not on C:\ are missing and restores them, whether or not they were deleted from C:\ accidentally or intentionally.

One task for this project is to display the full-size pictures in a user-specified order. I wanted to use the continuous form / small images for this. Displaying images 41-50 and trying to get #47 to follow the picture with the full moon in the background (was that 21-30 or 11-20?) is not easy. I notice that Netflix (DVD rental by mail company) displays the subscriber queue completely, so the subscriber can add, delete, and re-order without paging through subsets. Of course, no pictures here, but Google doesn't have pictures either (for those who want to compare my design with Google...I'm flattered).

For now, I have one image field in the footer of the form, and a continuous form in the detail section.

OldBirdman

[NeoPa]

I've had no great brainwave as to how you could show the pictures in a continuous form, however, as you've told us how you handle it at the moment, it occurred to me to suggest that the usability might be enhanced by the simple expedient of increasing the one picture in the footer to just three.
Assuming the delay showing these pictures is negligible, then the operator could probably scan through them more quickly if the previous and next pictures were shown alongside the current one. They could scan though quickly knowing that they will more likely notice the picture they're scanning for if it's available for a bit longer.
Just an idea.

[OldBirdman]

For the moment, I'm going to let this go.

Microsoft's site, "http://support.microsoft.com/kb/832508/en-us" implies that this can be done. It gives instructions on how to store a jpg image in an OLE Object. One could create a temporary table, and copy the image into this temporary table from a folder, and base the form (or subform) on the temporary table.

Take notice, however, of the note after step #17. You can store the image, BUT YOU CANNOT VIEW IT. The h*&@ you say. What is the purpose of that exercise. In my case, datasheet view of the table shows "Package", and form view of the form has the name of the file, including the extension, but lacking the path. I moved the pictures after inserting them in the table, to be sure they were actually embedded.

Double-clicking on the OLE field opens the default program for .jpg files, and displays the picture. Works the same in Continuous Forms and Single Form. Better is to have an image field in the footer, and when the record is selected, display the linked picture. Less clicking and toggling between programs, less bloat, and a lot easier to set up.

The fact that Access is storing the picture, keeping track of the type (extension), and can open it from a form is encouraging, but lacking.

Again, I'm not going to pursue this further, for now.

OldBirdman