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?