work with attachment file access

Behrouz Bahram 1 Reputation point
2022-03-19T19:34:19.133+00:00

Hi Office Team ,
I have problem with attachment data type in my project .
I have a table that two fields . one ID code with number data type and other picture in with an attachment data type fields.
now I want filled in these two fields with sql statement via another form my cod is like this :
DoCmd.RunSQL "INSERT INTO tblattach VALUES ('" & Me.text01.Value & "' , '' )"
and then open a form that choose picture on .
but this statement get runtime error 3032 .
I don't know how to fill an attachment field via sql .
Is there a method for my problem ?

Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
834 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Albert Kallal 4,971 Reputation points
    2022-03-22T21:33:50.767+00:00

    You can execute a query against such tables. But, to add such rows in VBA code?

    You can't use sql. You have to use reocrdsets.

    Say, something like this:

       Dim rst           As DAO.Recordset
       Dim rstPictures   As DAO.Recordset  ' table of attachments
    
       Dim strSQL As String
    
       strSQL = "SELECT * from MyPictures WHERE ID = 1"
    
       Set rst = CurrentDb.OpenRecordset(strSQL)
    
       ' ok, got ONE reocrd. Now get attachments
    
       Set rstPictures = rst!Pictures.Value
    
    
       Do While rstPictures.EOF = False
    
           Debug.Print rstPictures!FileType, "-->";
           Debug.Print rstPictures!FileName
    
           rstPictures.MoveNext
       Loop
    

    And from above the output is thus this:

    gif -->clock.gif
    jpg -->inventory.jpg

    So, you have the file type, filename, and the 3rd column is in fact the file data.

    So, to say export the above attachments out of the table, we could use this:

       Dim rst           As DAO.Recordset
       Dim rstPictures   As DAO.Recordset  ' table of attachments
    
       Dim strSQL As String
    
       strSQL = "SELECT * from MyPictures WHERE ID = 1"
    
       Set rst = CurrentDb.OpenRecordset(strSQL)
    
       ' ok, got ONE reocrd. Now get attachments
    
       Set rstPictures = rst!Pictures.Value
    
       Dim strOutPath    As String
    
       strOutPath = "c:\test7\"
    
       Do While rstPictures.EOF = False
    
           rstPictures!FileData.SaveToFile strOutPath & rstPictures!FileName
    
           rstPictures.MoveNext
       Loop
    

    So, in fact, behind the scenes, the attachments are really a "child" table, one that you can't really get at for sql inserts, and thus have to use VBA code, and createa WHOLE NEW reocrdset that is pointed to the ONE row and attachment field (which, as I note is REALLY a child table).

    So, the reverse is also the same case. If we wanted to add a row to the above attachments, then we have to FIRST get the main one record row, then get the child table (attachment table) and THEN add rows to this child table.

    And we have to put the parent record into edit mode first. So, the code to add a new attachment would thus be this:

       Dim rst           As DAO.Recordset
       Dim rstPictures   As DAO.Recordset  ' table of attachments
    
       Dim strSQL As String
    
       strSQL = "SELECT * from MyPictures WHERE ID = 1"
    
       Set rst = CurrentDb.OpenRecordset(strSQL)
    
       ' ok, got ONE reocrd. Now get attachments
    
       Set rstPictures = rst!Pictures.Value
    
       rst.Edit
    
       ' now add a row to this child table
    
       rstPictures.AddNew
    
       rstPictures!FileName = "bug2.jpg"
       'rstPictures!FileType = "jpg"
       rstPictures!FileData.LoadFromFile "c:\test2\bug2.jpg"
    
       rstPictures.Update
    
       rst.Update
    

    So in effect behind the scenes there is a hidden table for attachments. The above code shows how to get each row, and shows how to even pull out (save) the attachment to file.

    And the reverse - adding a row? The above code snip shows how this works - when you use LoadFromFile, you don't have to set the file type - it is done automatic for you.

    Regards,
    Albert D. Kallal (Access MVP 2003-2017)
    Edmonton, Alberta Canada