none
Save attachments to SQL table RRS feed

  • Question

  • I have a form in Access 2016 that show and hold the employee information. The back end database of my program is SQL. I have some buttons in my form that after clicking them, I want that it show an open dialogue box and browse for some files (scan of ID card, SSO card and etc. that are in .JPEG or PDF format) and attach them to specified fields in Employee table. every button is specified for a document and should save it in a field.

    If there is a code that I be able to scan the documents and attach them directly to table it is perfect,

    Regards,


    Karim Vaziri Regards,

    Wednesday, May 9, 2018 12:43 PM

Answers

  • Hi kvaziri,

    I try to make a test with code below on my side and I check that it is updating the image properly.

      Set rs = New ADODB.Recordset
        rs.Open "select Image from img_data1 where Id=1", adoCon, adOpenKeyset, adLockOptimistic
    
        Set streamobj = New ADODB.Stream
        streamobj.Type = adTypeBinary
        streamobj.Open
        streamobj.LoadFromFile "C:\Users\panchals\Desktop\image\10.png"
        rs.Fields("Image").Value = streamobj.Read
        rs.Update
        MsgBox ("complete")

    Output:

    I suggest you to add a where clause in your query to fetch specific record to update.

    Also, Try to add a primary key in your table, Otherwise it will give you another error.

    Directly pass the connection object in 'rs.open' after opening the connection.

    I suggest you to create a dummy table like mine and try to run above code to check whether it updates the image or not.

    Then after you can try to implement this solution on your actual table.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, May 11, 2018 6:58 AM
    Moderator

All replies

  • Hi Karim,

    Are you trying to control a scanner from Access? If so, not sure but this old thread at UtterAccess might be of some help.

    Good luck!

    Wednesday, May 9, 2018 2:39 PM
  • Dear Dbguy,

    I want to save the PDF or Jpeg file to my table, not the link or path of it.

    Regards,


    Karim Vaziri Regards,

    Wednesday, May 9, 2018 7:00 PM
  • Right, but my question was if you were trying to create the JPEG or PDF by scanning paper documents. If you were trying to scan a document using Access, I just thought the link I posted above to an old post might be of some help. Otherwise, attaching files to table records is pretty much straightforward.
    Wednesday, May 9, 2018 7:05 PM
  • For example, here's a code snippet to save a file from the hard drive to an Attachment field:

    Dim db As DAO.Database
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset2
    Dim strFile As String
    
    Set db = CurrentDb()
    strFile = "C:\Photos\SomeImage.jpg"
    
    Set rs1 = db.OpenRecordset("TableName")
    
    rs1.Edit
    
    Set rs2 = rs1!Attachments.Value
    
    With rs2
       .AddNew
       !FileData.LoadFromFile strFile
       .Update
    End With
    
    rs1.Update
    
    Set rs2 = Nothing
    Set rs1 = Nothing
    Set db = Nothing

    Hope it helps...

    Wednesday, May 9, 2018 7:12 PM
  • Hi kvaziri,

    Below is an another example to save file in SQL Database.

    Set rs = New ADODB.Recordset
    rs.Open "select * from AttachmentTable", Connection, adOpenKeyset, adLockOptimistic
    
    Set streamobj= New ADODB.Stream
    streamobj.Type = adTypeBinary
    streamobj.Open
    streamobj.LoadFromFile "c:\myfile.pdf"
    rs.Fields("blobfield").Value = streamobj.Read
    rs.Update 

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 10, 2018 4:31 AM
    Moderator
  • I think Deepak has your answer. Here is another example for SQL Server that uses an INSERT statement with parameters:

    https://usefulgyaan.wordpress.com/2014/09/30/store-and-fetch-files-sql-server-tables/


    Paul ~~~~ Microsoft MVP (Visual Basic)


    Thursday, May 10, 2018 4:23 PM
  • Dear Deepak,

    I used your code as below, but I got an error message:


    Karim Vaziri Regards,

    Thursday, May 10, 2018 8:10 PM
  • Hi Karim,

    Just a guess but try replacing

    Connection

    with

    CurrentProject.Connection

    Hope it helps...

    Thursday, May 10, 2018 8:15 PM
  • Dear Deepak,

    I used your code as below, but I got an error message:


    Karim Vaziri Regards,

    Yes, you need to provide an actual SQL Server connection string to the database. Below is an example using the latest version of the native OLEDB Provider:

    https://docs.microsoft.com/en-us/sql/relational-databases/native-client/applications/using-ado-with-sql-server-native-client?view=sql-server-2017

    https://www.connectionstrings.com/sql-server-native-client-11-0-oledb-provider/

    You may need to use an earlier version depending upon which version is installed.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, May 10, 2018 9:03 PM
  • Dear Deepak,

    I amend my code but now I got this error


    Karim Vaziri Regards,


    • Edited by kvaziri Thursday, May 10, 2018 10:46 PM
    Thursday, May 10, 2018 10:43 PM
  • Is there an MCScan column in your table? Are you trying to update or insert a new row? 


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, May 10, 2018 11:42 PM
  • Also, make sure the data type is compatible for storing files or binary objects.
    Friday, May 11, 2018 12:47 AM
  • Hi kvaziri,

    You need to make sure that data type in SQL Table is VarBinary Max.

    Also you need to use field names in your query with where clause to fetch desire record to update.

    For demo purpose, I wrote 'Select * ', you need to write field name that you want to update.

    If you want to insert a new record then also you need to make a change.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, May 11, 2018 6:20 AM
    Moderator
  • Yes. I have a "MCScan" column in tblEmployee table that should hold the .jpg or PDF file and its data type is "varBinary(Max)" and I want to update previous records. I mean that the personnel information inserted in previous monthes and now I want to update that records and add this photo and documents to them, but after now , it is possible that I insert a new field for new-employed personnel.


    Karim Vaziri Regards,


    • Edited by kvaziri Friday, May 11, 2018 6:21 AM
    Friday, May 11, 2018 6:20 AM
  • Hi kvaziri,

    I try to make a test with code below on my side and I check that it is updating the image properly.

      Set rs = New ADODB.Recordset
        rs.Open "select Image from img_data1 where Id=1", adoCon, adOpenKeyset, adLockOptimistic
    
        Set streamobj = New ADODB.Stream
        streamobj.Type = adTypeBinary
        streamobj.Open
        streamobj.LoadFromFile "C:\Users\panchals\Desktop\image\10.png"
        rs.Fields("Image").Value = streamobj.Read
        rs.Update
        MsgBox ("complete")

    Output:

    I suggest you to add a where clause in your query to fetch specific record to update.

    Also, Try to add a primary key in your table, Otherwise it will give you another error.

    Directly pass the connection object in 'rs.open' after opening the connection.

    I suggest you to create a dummy table like mine and try to run above code to check whether it updates the image or not.

    Then after you can try to implement this solution on your actual table.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, May 11, 2018 6:58 AM
    Moderator