Manipulating SalesLogix attachments using the FileSystemObject

I recently had to build a solution that would run through the SalesLogix attachments directory, and move any items that didn’t have a corresponding record in the attachments table.  To do this, I utilized functionality from the FileSystemObject.  It’s a pretty simple script if you’re familiar with the FSO, but I thought I’d just outline what I did here.

Sub UpdateAttachments
Dim strSiteCode, strAttachPath
Dim objRS, fso, f, fl, fc
Dim strCurrentFile
Dim sCheckVal, sNewPath
Dim sFoundCount, sNoCount

‘First of all, we need to get the attachment path for the machine we’re on.  I first check SystemInfo to get the site code, then BranchOptions to get the attachment path for that sitecode.
    strSiteCode = GetField(“PRIMARYSERVER”, “SYSTEMINFO”, “”)
    strAttachPath = GetField(“ATTACHMENTPATH”, “BRANCHOPTIONS”, “SITECODE = ‘” & strSiteCode & “‘”)

‘Next, we create a new RecordSet object, returning all filenames from the Attachment table
    Set objRS = CreateObject(“ADODB.Recordset”)
    Set objRS.ActiveConnection = Application.GetNewConnection
    objRS.open “SELECT ATTACHID, FILENAME FROM ATTACHMENT”

‘We also need to create an object for the FileSystemObject
    Set fso = CreateObject(“Scripting.FileSystemObject”)

‘Instead of deleting mismatched files outright, we’re moving them to a “Delete” folder under attachments.  This code creates that folder if it does not already exist.
    If not fso.FolderExists(strAttachPath & “Delete”) Then
       fso.CreateFolder(strAttachPath & “Delete”)
    End If

    Set f = fso.GetFolder(strAttachPath)
    Set fc = f.Files

    For Each fl in fc
        sCheckVal = fl.name
        sNewPath = strAttachPath & “Delete” & sCheckVal

‘After determining the name of the first file in the Attachments folder, I use the ADO “find” function to see if the file exists in the Attachments table

        objRS.MoveFirst
        objRS.Find “filename = ‘” & fl.Name & “‘”
        If (objRS.BOF = True) or (objRS.EOF = True) Then

‘If not found, the file is moved to the new folder.  I added a count which I display on a label on the form launching the code.
           sNoCount = sNoCount + 1
           fl.Move sNewPath
           lblMissing.Caption = sNoCount
        Else

‘If found, I do nothing other than record a count on a different label on the form.
            sFoundCount = sFoundCount + 1
            lblFound.Caption = sFoundCount
        End If

    Next

    Set fso = Nothing
    Set objRS = Nothing

End Sub

There you have it.  Just a simple example of using FileSystemObject in conjuction with SalesLogix functionality.  I hope you find this helpful.