Stupid Access Tricks
 

I have this MS Access database. Among other information, it has pictures of people. Think ID card photos.

I have another database with many of the same people. I need to extract the pictures from the first database and get them to the second. The second will import all the images for me, if only I can give it a directory full of files, where the name of each file is the ID # of the person. (The alternative is for somebody to sit and cut & paste the pictures between the two databases.)

(Please don't ask for more background about how and why I got to this point. If I had to explain, I'd probably just shoot myself.)

The problem is I can't figure out how to get the pictures out of the Access database as individual files. Googling has resulted primarily in solutions that get the files out as part of a report, e.g. to a PDF file. I had some hope for "print to HTML" utilities, but the ones I've found so far work by producing images of the entire report, a page at a time. I even found a little shareware app that promises to pull the images out of an Access file, but it doesn't seem to actually work. (To be fair, I've not yet been able to get any feedback from the developer.) I also made a very quick try with Crystal Reports, but if there's a way to do it there, I missed it.

Any suggestions?

Steve,

A = new database
B = old database

If you have access to both MDB files. Simply open A, the one you want the data in, link to the B table (new table --> link--> pick the B.mdb -> pick the table with the pix), and create a 'make table query' to copy the pics (and any important fields) from the linked table to a new local table. When done, simply delete the linked table. The images are in the a locally contained table with all of the other information you chose in the query (like id and such). Optionally, If you want to optimize, Put that table in design mode, add an autonumber primary key, index any fields you may need to join later, save and behold your fine work.

Sounds like an upgrade to a badging system or perhaps a photo directory. Did both a few years ago from an 'secure' access db.

BTW, the datatype for the pix should likely be 'Ole Data' and it is kind of a strange bird. You can write a module to save these in bmp or other format... (google that answer)


Hope that helps

Cheers,

sa


pm me if you need a better explaination...quickly..I'll be out of contact for a while in a couple of days.

Ahh... I didn't explain... "A" is a homemade conglomeration of bubblegum and paperclips. "B" is a commercial product that runs on Oracle, and I have somewhat limited access to the guts.

Dragging the files out as BMPs would be just fine, except I've never written any VB. I'm currently leaning toward using some of the perl database interface modules... I'll still have a learning curve to set it up, but I already know the language.

Don't you have slaves or summer interns that you can set to work cutting and pasting for a day or two? Let the kids do it. And then make them double check their work. And have them fetch you an iced latte while they are at it.;)

It may come to that.

I haven't used it, but this looks like it might work.

http://www.sobolsoft.com/accessimage/

Do you have access to a command line SQL client for the Oracle server? Does access have any sort of command line SQL client?

If so you should be able to dump the data from Access, massage as necessary, and put it in the Oracle database. That's the sort of approach I've used to move databases between MySQL and Postgres anyway.

here's a little exe that should do the trick

I have read-only access to the Oracle... and frankly I don't know enough to try messing with it if I could write. (Besides, the actual database that we paid for has things like audit trails that I wouldn't want to circumvent.) Still, not a bad idea, but I'm not sure about the differences between the OLE in Access and the BLOB in Oracle.

DING DING DING DING!!! Thank you barefoot serpent, that did the trick. Dar, the one you found is the one I found last night, and it didn't work (file type error). The guy who wrote it asked for some sample data to see what was going on, but I haven't heard back from him.

What amazes with me, is that will all the googling & research I did, I didn't find the second one at all, and I only found the first one after I started adding mysql to my googling in hopes of finding somebody else working on some kind of transition or interface.

keyword = BLOB

glad it worked!