By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,830 Members | 682 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,830 IT Pros & Developers. It's quick & easy.

How to insert an attachment into a table through form in ms access 2007

P: 6
hi all,
I would like to insert an attachment object into a table using a form. I have created a table with a field type attachment. But when i am inserting data into table , error is coming.
The error is:
Runtime error: -2147217887
Cannot perform this operation.


Expand|Select|Wrap|Line Numbers
  1. Private Sub cmd_add_save_Click()
  2.  
  3. Dim cmd As ADODB.Command, rs As ADODB.Recordset
  4. Set cmd = New ADODB.Command
  5. cmd.ActiveConnection = CurrentProject.AccessConnection
  6. cmd.CommandType = adCmdUnknown
  7.  
  8. Set rs = New ADODB.Recordset
  9. rs.Open "Purchase", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
  10. With rs
  11.     .AddNew ' create a new record
  12.     .Fields(0) = Me.txt_add_prno
  13.     .Fields(1) = Me.txt_add_prdesc
  14.     .Fields(2) = Me.txt_add_prvalue
  15.     .Fields(3) = Me.txt_pr_date
  16.     .Fields(4) = Me.Purchase_PRNote ' Error occurs at this line. 
  17.  
  18.     .Update
  19. End With
  20. Set rs = Nothing
  21. End Sub
  22.  
Purchase_PRnote is the name of the textbox which contains the attachment.
Mar 20 '12 #1
Share this Question
Share on Google+
1 Reply


ADezii
Expert 5K+
P: 8,623
Working with an Attachment Field in VBA is bo no means intuitive. I did not have much time, but I did have some success adding Attachments to an Attachment Field in a Table. It is not exactly what you are looking for, but it may point you in the right direction.
Expand|Select|Wrap|Line Numbers
  1. Dim dbs As DAO.Database
  2. Dim rs As DAO.Recordset
  3. Dim rs2 As DAO.Recordset
  4.  
  5. Set dbs = CurrentDb
  6.  
  7. Set rs = dbs.OpenRecordset("Purchase", dbOpenDynaset)
  8.  
  9. Set rs2 = rs.Fields("PRNOTE").Value
  10.  
  11. With rs
  12.   .Edit
  13.     .Fields(0) = Me.txt_add_prno
  14.     .Fields(1) = Me.txt_add_prdesc
  15.     .Fields(2) = Me.txt_add_prvalue
  16.     .Fields(3) = Me.txt_pr_Date
  17.       rs2.AddNew
  18.         rs2.Fields("FileData").LoadFromFile Me!Purchase_PRNOTE
  19.       rs2.Update
  20.   .Update
  21. End With
  22.  
  23. rs.Close
  24. Set rs = Nothing
  25. Set rs2 = Nothing
Mar 22 '12 #2

Post your reply

Sign in to post your reply or Sign up for a free account.