CODEWITHSUNDEEP
mysqlvbams-accessdao
Melissa
Melissa

Reputation: 1

Access VBA code to pull all files from a folder and insert them into seperate attachment fields in a table

I have code written to pull a specific file from a folder, insert it into an attachment field (local_attachment) and which creates a new record in table TEMP_attachment. I am trying to pull all the files from a folder and have them each be a new record in the table but I keep running into issues where I either pull all the files and they all go into one record, or it won't pull any. Thank you for your help!!!
Here is my code:

Dim x As Long
Dim strFile As String
Dim db As DAO.Database
Dim rs As DAO.Recordset2
Dim rsA As DAO.Recordset2
Dim fld  As DAO.Field2
Dim SQL As String

x = 1
strFile = "C:\dev\test_file2.txt"

SQL = "INSERT INTO TEMP_Attachment (ID) "
SQL = SQL & "VALUES (" & x & ")"
DoCmd.RunSQL SQL

Set db = CurrentDb
Set rs = db.OpenRecordset("TEMP_Attachment")
Set fld = rs("local_attachemnt")

'Navigate through the table
Set rsA = fld.Value
rs.Edit
    rsA.AddNew
    rsA("FileData").LoadFromFile strFile
    rsA.Update
rs.Update

Upvotes: 0

Views: 2293

Answers (1)

Don Jewett
Don Jewett

Reputation: 1977

The problems with your code can be fixed my taking a more methodical approach. As I see it, you need to find all the files in the folder, add a record for each one, add an attachment record for the file, read the file data into the new record, and generate a unique key for the parent record.

Rather than try to do everything at once, let's break into pieces and take them in reverse order, so we're dealing with the smallest problems first:

First, let's figure out how we are going to generate a key. The easiest way to do this is to use an Autonumber field. Rather than cook up something fancier, I'm going to assume this is the solution you will use. This will make DoCmd.RunSQL unnecessary and simplify the whole operation.

Second, write a routine which adds one file to one record in the database, and make sure this is working. My suggestion would be to create parameters for a recordset to the main table and a path to the file, like so (I have not tested this, that will be your job. I've added error handlers to help you work out any issues):

Private Sub AddFileAttachment(ByRef rs As DAO.Recordset, ByVal sPath As String)

    Dim rsAttachments As DAO.Recordset

    On Error Goto EH

    With rs
        '(this will generate a new Autonumber in the main table)
        .AddNew 

        'this will create a new attachment in the field and add it
        Set rsAttachments = .Fields("local_attachemnt").Value
        With rsAttachments
            .AddNew 
            .Fields("FileData").LoadFromFile sPath 
            .Update
            .Close
        End With

        'this is what adds the main record 
        .Update
    End With

EH:
    With Err
        MsgBox .Number & vbcrlf & .Source & vbCrLf & .Description
    End With
FINISH:
    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If
    If Not rsAttachments Is Nothing Then
        rsAttachments.Close
        Set rsAttachments = Nothing
    End If

End Sub

And call it like so:

Private Sub cmdTest_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    On Error Goto EH

    Set db = CurrentDb
    Set rs = db.OpenRecordset("TEMP_Attachment")
    AddFileAttachment rs, "C:\dev\test_file2.txt"

    Goto FINISH

EH:
    With Err
        MsgBox .Number & vbcrlf & .Source & vbCrLf & .Description
    End With
FINISH:
    rs.Close
End Sub

Important! Perfect the first routine before moving on. You should test it until you know it works over and over. You should be able to click the button it is attached to 10 times and each time get a new record with the file attached.

Once you know this is working, you are ready to write the main routine that calls it for each file you are attaching. I will not include that here, but would suggest researching the FileSystemObject. You should be able to find a lot of vba examples for how to get all the files in a folder. You would loop through them and call your routine for each file the same way it is called in the test above, passing in the open recordset.

Upvotes: 0

Related Questions