Help with my Image (1 Viewer)

[Privateer]

Years ago, I stored images in an Access table where they were converted to bitmap images. This caused the database to swell to 1.8 gig, just under the maximum of 2 gig. I want to replace these images with files on the hard drive; that process is working fine. To do this, I would like to be able to see the original image so I can replace it with the same one. Once I have the new image, I can delete the bitmap image in the table. Unless I am on that image looking at it, then I can't delete it. That's my problem. So, I would like a way to grab the original image using a record set, assign it to a variable, and assign the variable to the image on the form. This way the original record won't be locked, thus allowing me to delete it.

I have a record set which returns the field with the image, but I can't find the proper data type for the variable to handle an image. After some searching, I created the variable as a variant, and used PictureData on the field. But the code blew up when the transfer was being executed. It looks like this:

Dim PicImg As Variant
PicImg = ![BookCover].PictureData

When it comes to images, I really have no idea what I am doing. Any help would be greatly appreciated. Thanks.

 

[theDBguy]

Just a quick thought, but maybe you could also just move the record pointer before deleting the image?

 

[Privateer]

Moving the pointer would have worked, but the form in question is opened to one book only, bound to the table, so I have nowhere to move. But it's not a bad idea. Perhaps I can do a union query to a blank record, move to that, whack the image, and move back. Will let you know how that works out. Thanks.

 

[moke123]

I'm still not sure what your trying to do. I assume this is a one off procedure and your trying to move all the images into a new folder and associate them back to the original record. I would just extract all the images and write the records primary key and the filename to a new table. Then just delete the attachment field from the table. You may have some issues if there are repetitive or duplicate file names but that can be dealt with.

something like this:

Sub ExtractAll(ByVal TableName As String, PKeyName As String, ByVal AttachColName As String, ByVal ToFolder As String)

    Dim db As DAO.Database
    Set db = CurrentDb

    Dim rsMain As DAO.Recordset2
    Dim rsAttach As DAO.Recordset2
    Dim strSql As String
 
      Set rsMain = db.OpenRecordset("Select " & PKeyName & "," & AttachColName & " from " & TableName & " where " & AttachColName & ".FileName is not null")

    Do Until rsMain.EOF

        Set rsAttach = rsMain.Fields(AttachColName).Value

        Do Until rsAttach.EOF
   
            Dim OutputFileName As String
            OutputFileName = rsAttach.Fields("FileName").Value
            OutputFileName = ToFolder & "\" & OutputFileName
   
            rsAttach.Fields("FileData").SaveToFile OutputFileName
       
             strSql = "Insert into tblImages(PKey,ImageName) values(" & rsMain.Fields(PKeyName) & ",""" & rsAttach.Fields("FileName").Value & """)"       
            db.Execute strSql, dbFailOnError

            rsAttach.MoveNext
        Loop

        rsMain.MoveNext
    Loop

    rsMain.Close

db.Close
Set db = Nothing

End Sub

call it with something like

ExtractAll "table1", "ID", "img", CurrentProject.Path & "\ImagesA"

-Borrowed heavily from Phil Stiefel