VBA 根据数据库条目中的相关数据将图像添加到 MS 数据库

Question

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

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

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 的关系功能将它们链接在一起。

Answer 1

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

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