Query to Display Picture from File System (not Database)

[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_fa q/access_loading_ image_dialog.ht ml" 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 "Maintenanc e" 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.htm l

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