VBA 根据数据库条目中的相应数据将图像添加到 MS 数据库 [英] VBA Adding images to a MS Database as per its corrsponding data in the database entry

问题描述

这是已解决的另一个问题的一部分，将链接粘贴到此处以方便将来遇到的人.感谢 Erik_von_Asmuth 之前的帮助.使用VBA将大量附件导入Microsoft Access

This is part of another question which has been resolved, pasting the link here for the convenient of those coming across in the future. Credit to Erik_von_Asmuth for his help previously. Using VBA to import a large number of attachment into Microsoft Access

我认为可能有效的代码概念:

The concept of the code I think that might work:

Sub MacroInsertImageToDatabase()

Dim I As Integer 'number of row in file_paths.txt
Dim J As Integer 'number of entries in the database

For J = 1 To 100

For I = 1 To 100
'Lets say there are 100 lines in file_paths.txt. Something like:
'C:\image_folder/image1.jpg
'C:\image_folder/image2.jpg
'all the way to
'C:\image_folder/image100.jpg

    If (string of file_name in column 2 in the database) = (current row in file_paths.txt we are looking at)
    Then 'That means there is a match!
        [Attach the image from as given from file_paths.txt(we ar looking at) into the 3rd row of the database(we are looking at)]
        [also escape this loop through file_paths.txt so we can move onto the next entry in the database to repeat this If statement]
    Else 'current row in file_paths.txt we are looking at is NOT what we
        [move in the the next "I" iteration to look at the next row in file_paths.txt]

Next I 'successfull attached the image to the correponse entry in the database as per string in the 2nd column (file_name)


Next J 'now move on to the next row (entry) in the database, the "J" loop

End Sub

或者我应该利用 MS Access 的功能，我正在阅读有关数据库表关系"的文档.有 1 张桌子，只有附件.有另一个带有相应文件名(和其他数据)的表.然后使用 MS Access 的关系功能将它们链接在一起.

Or should I exploit the features of MS Access, I am reading the documentation about the "database table relationships". Have 1 table with just the attachments. Have another table with the corresponding file names (and other data). Then use the relationship features of MS Access to link them together.

推荐答案

您可以使用以下代码根据 file_name 列中设置的位置向表格添加附件

You can use the following code to add attachments to your table based on the location set in the file_name column

Public Sub MacroInsertImageToDatabase()    
    Dim db As DAO.Database
    Dim rsEmployees As DAO.Recordset, rsPictures As DAO.Recordset, ws As DAO.Workspace
    'Initialize database, workspace and recordset
    Set db = CurrentDb()
    set ws = DBEngine.Workspaces(0)
    Set rsEmployees = db.OpenRecordset("Table1", dbOpenDynaset)   

    Do While Not rsEmployees.EOF
        'Open the attachment recordset for the current record
        Set rsPictures = rsEmployees.Fields("attachment_column").Value
        'If there are no attachments yet
        If rsPictures.BOF Then
            'Edit the record
            rsEmployees.Edit
            'Begin a separate transaction for inserting the picture
            ws.BeginTrans
            'Load the picture
            rsPictures.AddNew
            rsPictures.Fields("FileData").LoadFromFile rsEmployees.Fields("file_name")
            'Update the pictures recordset and commit the transaction to avoid troubles with nested transactions
            rsPictures.Update
            ws.CommitTrans
            rsPictures.Close
            'Then update the record (if anything has changed inside it, which it actually hasn't)
            rsEmployees.Update
        End If
        rsEmployees.MoveNext
    Loop
    rsEmployees.Close
End Sub

这篇关于VBA 根据数据库条目中的相应数据将图像添加到 MS 数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！