Results 1 to 4 of 4
  1. #1
    carmenv323 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Location
    Massachusetts
    Posts
    78

    convert attachments field

    Hello All,

    New to SQL server migration so please excuse me if this is a simple task.



    I have a split database and am moving our backend to SQL, however one of the field's Data Type is "Attachments" and gets converted to nvarchar(MAX).

    I don't need to save the attachments to the table because I have code that saves it out to a folder that's created based on a number in one of the form's fields, but how do I get the attachments to be attached without an attachments field if I'm using the linked table from sql?

    Code:
    '>>>>>>>Save attachments<<<<<<<
            Dim strSQLb As String
            strSQLb = "SELECT dbo_tbl_Requests.RequestID, dbo_tbl_Requests.Attachments, AttachmentCount(""dbo_tbl_Requests"",""Attachments"",""[RequestID]="" & [RequestID]) AS [Num Attach]"
            strSQLb = strSQLb & " From dbo_tbl_Requests"
            strSQLb = strSQLb & " WHERE (((AttachmentCount(""dbo_tbl_Requests"",""Attachments"",""[RequestID]="" & [RequestID]))<>0) AND ((dbo_tbl_Requests.txtProjectNumber)='" & Me.txtProjectNumber & "'))"
        Dim sFolder As String
                sFolder = sTempProjectFiles & Me.txtProjectNumber
    
    
    'Checking attachments
            Set Records = db.OpenRecordset(strSQLb, , dbSeeChanges) ' set to records with attachments
                If Records.RecordCount > 0 Then
    '                Me.bAttachments = "Yes"
                    If Dir(sFolder, vbDirectory) = "" Then 'Check if folder exists, create if not
                        MkDir sFolder
                    End If
                Dim s As String
                 Do While Not Records.EOF
                     s = sFolder & "\" & "R" & Records.Fields("RequestID").Value
                     Set MyAttachments = Records.Fields("Attachments").Value
                        While Not MyAttachments.EOF 'Save attachments to prj\multipleindex\files*
                            If Dir(s, vbDirectory + vbHidden) = "" Then MkDir s
                            MyAttachments.Fields("FileData").SaveToFile s
                             MyAttachments.Delete
                             MyAttachments.MoveNext
                        Wend
                     MyAttachments.Close
                         Set MyAttachments = Nothing
                         Records.MoveNext
                        Loop
            End If
        Set Records = Nothing
    Any thoughts?

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Sorry, but not sure what you're asking. Usually you save the path to the file along with the primary key of the related record (in your case RequestID) in a separate table and employ a form\subform design to show them. To open them you would add code to use one of the many methods (Shell(), FollowHyperlink, etc.).

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    carmenv323 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Location
    Massachusetts
    Posts
    78
    I think I need to know how to have the user add the attachments, I'll re-post in Access forum
    Thank you

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Just use the FileDialog to browse for the file(s) and add their full name to the field along with the PK of the related record.
    https://learn.microsoft.com/en-us/of...ion.filedialog
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Adding attachments to email, saving attachments
    By snsmith in forum Programming
    Replies: 31
    Last Post: 09-10-2021, 08:25 AM
  2. Bulk upload of attachments to table field
    By Sanar1234 in forum Programming
    Replies: 3
    Last Post: 05-15-2021, 05:52 AM
  3. Replies: 6
    Last Post: 09-06-2016, 05:13 PM
  4. Replies: 5
    Last Post: 10-14-2013, 11:53 AM
  5. Replies: 5
    Last Post: 06-19-2012, 10:46 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums