Using VBA to import a large number of attachment into Microsoft Access

Question

I'm trying to attach a large number of images (~1000) into Microsoft Access. I figured using VBA to automate the task would be sensible instead of doing it manually.

I do NOT want to link the hyperlink or path or OLE to file's location, which would keep the database's file size down. (Edit: It has been understood that Ms Access has a limit of 2Gb, I would like to go forward under the assumption that the 2Gb limit would not be exceed in this case.)

The database I want to do all this in is named "database1". The table I want to import the images into is named "Table1". In this table there are 3 column so far:

1) The auto-generated ID, which I kept as it is

2) A column with the heading named "file_name", which is currently "image1", "image2", "image3", etc. Alternatively I can change the entries into this column as the path of the files on my computer (e.g. C:\Users\Username\Documents\image1.jpg). I have generated a list of all the images' path on my computer using a .bat file into a .txt file, which is named "file_paths".

3) A column with the heading named "attachment_column". This is the column which I want the images to be put into in my database.

i would like the image to be imported into the corresponding Database entry as per its file_name in column 2), if possible.

I have been looking at various documentation and tried them without any luck. https://msdn.microsoft.com/VBA/Access-VBA/articles/work-with-attachments-in-dao https://access-programmers.co.uk/forums/showthread.php?t=172939

The closet thing I have is something like this below. But I can't figure out how to loop through all the files paths in file_paths.txt to attach all images.

Sub macrotest2()

   Dim db As DAO.Database
   Dim rs As DAO.Recordset
   Set db = CurrentDb 'I guess I don't have to define as database1 ?
   Set rsEMployees = db.OpenRecordset("Table1", dbOpenDynaset)

   rsEMployees.Edit

   Set rsPictures = rsEMployees.Fields("attachment_column").Value

   rsPictures.AddNew
   rsPictures.Fields("attachment_column").LoadFromFile "C:\Users\Username\Documents\image1.jpg"
'how to automate this to loop all the file paths in file_paths.txt?

   rsPictures.Update
   rsEMployees.Update
End Sub

Thank you in advanced.

Please specify: do you want all the images in a single record, or separate records per images. Please share the structure of your file_paths.txt file. Note that Access has a 2GB size limit per database, so you might not be able to achieve this due to that limitation, depending on the size of your pictures. — Erik A
– Erik A, Commented Aug 13, 2017 at 11:29
I want a single image per record (row in the database table), i understand it is possible to have multiple attachment per record but let ignore that for now. The file_path.txt is literally as below:C:\Users\Username\Documents\images\image1.jpg C:\Users\Username\Documents\images\image2.jpg C:\Users\Username\Documents\images\image3.jpg C:\Users\Username\Documents\images\image4.jpg etc I understand Ms Access has a limit 2Gb as per documentation (hence all the recommendation about OLEs), I won't exceed this limit. I'll act under the assumption that I won't exceed this. Post edited. — GreenDingy
– GreenDingy, Commented Aug 13, 2017 at 11:42
Is that new line delimited or space delimited (I know you can't insert lines in the comments)? — Erik A
– Erik A, Commented Aug 13, 2017 at 12:03
Sorry I don't understand "delimited" in term of coding. If this is about the file paths of the files in the .txt, then it has its own line (row) in the .txt files. — GreenDingy
– GreenDingy, Commented Aug 13, 2017 at 13:52
Ah, that's clear. Will answer soon. See this wiki page for a definition of a delimiter. So your text file is new line delimited (the different fields are separated by new lines). — Erik A
– Erik A, Commented Aug 13, 2017 at 13:58

Erik A · Accepted Answer · 2017-08-13 17:03:18Z

3

Try this:

Dim fileName As String, textRow As String, fileNo As Integer
fileName = "C:\file_paths.txt"
fileNo = FreeFile 'Get first free file number  
Dim i as Integer
Dim db As DAO.Database
Dim rsEmployees As DAO.Recordset, rsPictures AS DAO.Recordset
Set db = CurrentDb()
Open fileName For Input As #fileNo
Do While Not EOF(fileNo)
    i = i + 1
    Set rsEmployees = db.OpenRecordset("Table1", dbOpenDynaset)   
    rsEmployees.Edit
    rsEmployees.AddNew
    Line Input #fileNo, textRow
    rsEmployees.Fields("file_name").Value = textRow
    Set rsPictures = rsEmployees.Fields("attachment_column").Value
    rsPictures.AddNew
    rsPictures.Fields("FileData").LoadFromFile textRow
    rsPictures.Update
    rsPictures.Close
    rsEmployees.Update
    rsEmployees.Close
Loop
Close #fileNo
MsgBox i

There are multiple ways to go through a way line by line, but I like this one.

Note that there can't be empty lines in your text file. Even the last line needs to contain a file link.

edited Aug 13, 2017 at 17:03

answered Aug 13, 2017 at 14:17

Erik A

32.8k13 gold badges49 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

17 Comments

GreenDingy Over a year ago

I got a error "Run-time error '21542: Item not found in this collection. For the 7th line form the bottom. "rsPictures.Fields("attachment_column").LoadFromFile textRow" Also I added "Sub Macro1()" to the top of the code and "End Sub" in the VB code editor. Currently in the 2nd column in my database I put the full file path of the corresponding image I would like to attach.

Erik A Over a year ago

See edit. Fixed it. Note that your example also fails to add a single file because of the same error.

GreenDingy Over a year ago

I am getting another error "Could not start transaction; too many transactions already nested. (Error 3003) " from the edited code above from the line "Set rsEmployees = db.OpenRecordset("Table1", dbOpenDynaset)". On a separate note, "rsEmployees" and "rsPictures" are just arbitrary term I used in the code, it shouldn't affect the code I think

Erik A Over a year ago

Well, then we're going to have to get a little bit less effective, and insert 1 record at a time. Edited again.

GreenDingy Over a year ago

Now things got interesting. Running the code once made some changes to the database. Only one single new row has been added to the bottom of the Table1, with a new AutoID (#21), and added image3.jpg. Running the macro again adds only image3.jpg again on a new row, with with an autoID #24 (multiple of the number of rows I have in the file_paths.txt).

|

Collectives™ on Stack Overflow

Using VBA to import a large number of attachment into Microsoft Access

1 Answer 1

17 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

17 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related