Importing images into MS Access using VBA

Question

I have a directory with hundreds of images that I would like to use to create and populate records in Access. How do I do this using VBA? I essentially want to do:

choose directory
for each image in the directory:
     create new record
     set "name" field of the record to the file name
     add the image to the "image" attachment field of the record

What do you mean "add the image to the image attachment field of the record? Do you intent to store a copy of the actual image file in your database or just a link to the file? — HK1
– HK1, Commented Mar 9, 2011 at 14:46
Adding images to a database is very rarely a good idea. It even needs to be considered carefully for, eg, SQL Server. You might like to say what problem you hope to solve with this method, because you may get a selection of alternative ideas. — Fionnuala
– Fionnuala, Commented Mar 9, 2011 at 15:42
I'd like to store a copy of the file in the database. If it isn't a good idea, why is there an attachment field in Access that lets me render image attachments in a form? — Senior
– Senior, Commented Mar 10, 2011 at 14:39
There's a difference between the functionality that Microsoft has designed and what is considered best practices in designing databases. Storing files inside your database causes massive bloat. Your best bet for storing files is to use SQL Server 2008 (or newer) and use FileStream. FileStream basically stores the file in a given folder but all files are moved into that folder or removed from it by SQL Server using your code/interface in MS Access, .Net, etc. The user should never have direct access to a FileStream folder since SQL Server manages it. — HK1
– HK1, Commented Mar 11, 2011 at 17:17

HK1 · Accepted Answer · 2011-03-09 20:26:22Z

Choose Directory:
Because there are many different ways to do this, I'll leave this part up to you. Here's some code if you want to use the Common 'Browse for Folder' Dialog window.

To find each image in a directory:

Public Sub LogPictureFilesToDatabase(sFolderPath As String)
    Dim sFileName As String
    sFileName = Dir(sFolderPath)

    Do Until sFileName = ""
        Select Case LCase(Right(sFileName, 4))
            Case ".jpg", ".gif", ".bmp"
                'Put your SQL Insert Statement here
                'Or you can use DAO or ADO to add new records instead, if you prefer
                'You may also want to use a function to insert a blob if you want to save
                'the file inside the database, which I do not recommend
            Case Else
                'Ignore other file extentions
        End Select
        sFileName = Dir 'Get next file
    Loop
End Sub

Collectives™ on Stack Overflow

Importing images into MS Access using VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related