work with attachment file access

Question

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 ?

Answer 1

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