Query to Display Picture from File System (not Database)

[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.ShellFo lderView) 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\sys tem32\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:
[code=vb]
Option Compare Database
Const ViewFolder = "C:\Temp"

Public Sub CreateShortCut( strTarget As String)

Dim objShell As New IWshRuntimeLibr ary.wshShell
Dim objShortCut As IWshRuntimeLibr ary.WshShortcut

Set objShortCut = objShell.Create ShortCut(ViewFo lder & "\" & _
GetFileName(str Target) & ".lnk")

With objShortCut
.TargetPath = strTarget
.Save
End With

Set objShortCut = Nothing
Set objShell = Nothing

End Sub

Public Function GetFileName(str Path As String) As String
GetFileName = Right(strPath, Len(strPath) - InStrRev(strPat h, "\"))
End Function

Public Sub FillFolder()

Dim RS As New ADODB.Recordset
Dim fso As New IWshRuntimeLibr ary.FileSystemO bject

For Each f In fso.GetFolder(V iewFolder).File s
f.Delete
Next

With RS
.ActiveConnecti on = CurrentProject. Connection
.LockType = adLockReadOnly
.CursorType = adOpenForwardOn ly
.Open "tblPicture s"
While Not .EOF
CreateShortCut !txtPath
.MoveNext
Wend
.Close
End With

FollowHyperlink ViewFolder

Set fso = Nothing
Set RS = Nothing

End Sub
[/code]

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=PictureFileNa me(Drive&Path&F ileName 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.SetWarni ngs 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.SetWarni ngs False' does not seem to influence it.

Any Ideas?

1. Have you tried turning OFF/ON Screen Updating?
   [CODE=vb]
   Application.Ech o False
   Application.Ech o True[/CODE]
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?
   [CODE=vb]
   Application.Ech o False
   Application.Ech o True[/CODE]
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.Ech o 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.Ech o 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.