Purge Image Gallery

Are they in an attachment field?

Yes, The Field Name is "Check Image" of Data Type "Attachment" in a Table name "Payments."

If I open the Payments table in design view and then open the Insert Image dropdown, all I see in the Image Gallery is the logo pic that I use in multiple places.

Watch my Attachments video. In the extended cut, I show you how to extract those attachments.

Very good information in that video and the extended cut!  Exactly what I want to accomplish, but since my attachments are in a table named "payments" that have multiple entries for a foreign key to my "members" table is there a way just to get each attachment for a particular foreign to be in a directory named for the foreign that points back to an individual.  Or am I just going to have to deal with all the records that have attaachment in the "payments" table with no reverence to the members themselves?

Since my end objective is to remove the attachments I guess not having the reference back to "members" won't be the end of the world.

You can definitely keep the member reference. The attachment lives in PaymentsT, but when you extract it, just include the foreign key field from that payment record when building the destination folder.

For example, if PaymentsT has a MemberID field, create folders such as:

C:\CheckImages\Member_123\
C:\CheckImages\Member_456\

Then, as you loop through each payment record, use that record's MemberID to determine the folder. You can also include the PaymentID and date in the file name so multiple checks for the same member don't overwrite each other.

You don't have to join to MembersT unless you want to name the folders using something friendlier, like the member's name. Using MemberID is usually safer because names can change and may contain characters that Windows won't allow in folder names.

Once you've verified that all of the files extracted correctly, you can delete the attachment data. Just make a backup first, and don't delete anything until you've spot-checked several members and payments.

I have made a copy of my current DB and I am only using the copy for my testing phase.  When and if I can get things extracted and verified then I'll move to do the same on my current good DB.

But I did go ahead and attempt the removal of attachments from the PaymentsT without worrying for the moment that I didn't have the MemberID.  When I tried to "Extract Attachments", even though the code compiled OK I got a 'Run-time error 3265, Item not found in this collection' right away.  That stumped me and I think I'm going to stop for the evening and get back to it in the morning.

Sometimes that's best. I often come up with the best ideas after I sleep on something.

I may have figured out the error I was getting!  I think it must be the field name that the attachments are associated with.  From the code vault the line in the code that generates the error is "Set rsA = rsT!Attachments.Value".  It dawned on me that "Attachments" is the name of the field in your DB, but the field in my DB is "Check Image".  Since there is a space in my field name, something I don't do anymore, would I need to make it [Check Image] in the VBA code?

Try it Yourself

Well, I went ahead and made those changes and "Clicked" on my "Extract Attachments" and lo and behold it seemed to run to completion as I have an attachments directory that got created with a subdirectory for each record that contained a "Check Image", but the images still appear to be in the DB!  I must have missed something!

Good job - you found the problem. "Attachments" in that example was the Attachment field name, so changing it to [Check Image] was necessary.

What the extraction code does is copy the files out of the Attachment field into folders on your drive. It does not remove the attachments from PaymentsT. So seeing the images still in the database afterward is expected.

First, verify the extracted files by opening several of them, especially from different members and payments. Once you're satisfied that everything made it out correctly, I would delete the entire [Check Image] field from PaymentsT. Since your goal is to get those old check images out of the database, there's no reason to keep that Attachment field around afterward.

Of course, keep that backup copy until you've verified the live database too.

Well one step forward and two steps back.  The DB is much smaller, from 800MB to 7MB. but if I try to open the embedded form based on my Payments table it is asking for a parameter value for Payments.Check Image!  But the is no field called "Check Image" in Payments anymore.  Also the embedded payments form in the members form no longer shows up.  Sure glad I'm working with a copy of my good database!

Is the field referenced in a query or your VBA code?