It's possible to manipulate Access as an OLE object using Win32::OLE. This is essentially using it as an OLE automation server. You'll have to familiarize yourself with the MS Access object model, of course.

Not too long ago, I was faced with the task of printing customer invoices, each of which consisted of a variable number of (from five to seven) separate PDF files, which were generated by our system. These had to be collated in a particular order, which depended upon the individual invoice.

My solution was a perl program running on a dedicated NT box, which instantiated an Acrobat COM object (OLE automation server), and called methods on that object to Open, Print, then Close each file in sequence.

In your case (you'll need to investigate Access' object model to be able to do this), you'll want to open your database, then for each image you want to insert, select the relevant row (record) in a result set, instantiate an appropriate object type from the file containing the image, and then update the appropriate column (field) of the result row with that object.

This can all be done from Perl :)

It's rather tedious wading through Microsoft's docs, looking for info about the Access object model; which properties, collections, etc., to use. but it's worth all the trouble when you have a working automated solution.

You should indeed consider moving your images out of the database (and simply referring to their name/location in the DB) if/when you switch to a full RDBMS (eg MySQL). Moving the database + images isn't too bad, I don't think. For MySQL at least you can do a dump of the table structures and data. Then you can simply tar/zip that up along with your image directory, and you should be good to go. Same can be done with an Access .mdb file

As to your actual question on OLE I'm afraid you'll have to wait for a monk worthier than I for some wisdom on that.

I really agree with AidanLee. You may want to just stay away from saving blob data. It's my bias (but it's an educated bias) against Access, but it seems that doing blobs is gonna eventually see performance issues as the access db grows (my experience is big access tables are trouble). Not to mention portability issues (when you toss the yoke and move to mySql or another RDBMS).

I would use some standard naming that makes matching thumnails to their big brothers intuitive and easy to code for, then perhaps store the name/path info in the db, and just say no to stuffing images into blobs....even if NOT using MS Access, images do just fine sitting in a file structure and reading them from the file system is going to be much more efficient that having Access between you and the files.

...and using any manual process above and beyond the minimal seems like too much work, then again perhaps perl is just making me lazy ;-)

good luck whichever way you go!

I think the original decision to store the image data in Access was probably false laziness.

I wanted the picture to be displayed within an Access form and this seemed like the easiest way to do that. I'm sure (given a bit of VB code) I could load an image from the filesystem into a form.

Must port to MySQL soon, it's approaching the top of the things to do list.

Kevin O'Rourke

I created both the thumbnail and the screen-sized compressed image using ImageMagick from the Perl script. The original, higher fidelity large size, image is on the server.

Every image is assigned a random code number. Not sequencial numbers, but random, for two reasons: so they hash nicely, and so people can't guess at numbers for pictures they were not shown in the album.

Anyway, it associates a fully qualified file name of the original image with the code number.

Now, when an image is needed of some size and quality (thumbnail or screen-sized), it forms a filename by combining the code number with option values.

If that file name exists in the image cache directory, serve it up! If it doesn't exist, generate it first using ImageMagick, then serve it up. When cache gets too large (e.g. 1GB) oldest files are deleted (could be better).

The file system is part of my database! As for copying/moving, it's a automatically-generated cache, so big deal, just delete it. For moving or renaming source images, the primary input to the system is actually the directory of full-sized images, and it scans that for available files. It will automatically notice changes when an album is displayed.

Now, that's a complex system with security and billing and everything, and selectable resolutions and quality and monitor calibration. It was a bit to bite off, so I also made a simpler (really simple) server that I could use without such a large development effort. It fills my needs of delivering immediate results to models who just had portfolio shots taken. There is one working directory that the script uses for its stuff. I put a configuration file, e.g. foobar123.ini, into that directory and include lines in it pointing at the source directory of full-size images (it can reach my work area via the network, where her files actually live), the desired resolution and quality settings, how many "up" to list, album title, etc. With stock settings, a simple perl script generates this ini file automatically given the target directory name.

The name, foobar123 in this case, is actually randomly chosen, so simply knowing it amounts to a password. Put that "personal password" in on the main page, and it reads the ini file of that name, and also looks in a subdirectory of the same name for its image files. When one album page's HTML is generated, it makes sure that all the thumbnails and screen images exist, creating them if needed. So the first time the page is served it is generated, and the IMG files and links are all normal static files, not scripts to serve them up. All the generated files for that album are in one place in a subdir, so it is easily deleted when I'm done with it. There is no database: static settings only in the .ini, and files in a subdirectory make up the whole system.

—John

Celebrate Intellectual Diversity

#Using ADO from PERL
use Win32::OLE;

my $Conn=Win32::OLE->new('ADODB.Connection') or die "Unable to create 
+ADODB.Connection";

#NB.You will need to set up the DSN in the ODBC Data Manager (Control 
+Panel)
#    MUST be done on the System DSN tab, you may to enter user name an
+d password
#Don't or die on these methods - it dies every time!

$Conn->Open("<db name in Data Manager>","<optional userid>","<optional
+ password>");
$RS=$Conn->Execute("SELECT * FROM <table>");

$RS->MoveFirst();

until ($RS->Eof()) {
    print $RS->Fields->{'Name'}->Value,"\n";
    $RS->MoveNext();
}

$RS->Close;
$Conn->Close;
[download]

If you are approaching this as a learning project I do not yet have th
+e skills to advise you. 
But if you wish a quick ready made answer using MySQL try
 
http://www.artronic.hr/mysql/index.htm

 for a MySQL database that does exactly what you describe

-_-_-_-_-_-_-_-_-_-_-_-
My memory concerns me
- but I forget why !!!
[download]