convert attachments field

[carmenv323]

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?

'>>>>>>>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?

[Gicu]

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,

[carmenv323]

I think I need to know how to have the user add the attachments, I'll re-post in Access forum
Thank you

[Gicu]

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,