CODEWITHSUNDEEP
ms-access
Samuel Anderson
Samuel Anderson

Reputation: 17

Output results of WScript query In Microsoft Access

I have an Access database that uses a WScript query to get directory information for a file path that the user provides. I would like to be able to output the results into a SQL or Access table.

For example, if I have the WScript set to get the contents of a file on the C: using the DIR command, I would like the output of that placed in a SQL Server or Access table.

I know that xp_cmdshell would be a better option for this type of command, however our environment has disabled this capability with no chance of being able to use it. Any help you can provide would be appreciated.

Thanks.

Upvotes: 0

Views: 60

Answers (1)

Brad
Brad

Reputation: 12253

You can use the FileSystemObject to get all the info you need about folders/files. You can then look through all the file in a folder and subfolders in a folder to populate an in memory ADO recordset. Set this recordset to the recordset of the form.

Public Function GetFolderContents(path As String) As ADODB.Recordset

    Dim fso As New FileSystemObject
    Dim folderRecordSet As ADODB.Recordset

    If Not fso.FolderExists(path) Then
        Set GetFolderContents = Nothing
        Exit Function
    End If

    Dim fol As Folder, fil As File, subFol As Folder
    Set fol = fso.GetFolder(path)

    Set folderRecordSet = GetNewFolderRecordset

    For Each fil In fol.Files
        If fil.Type <> "System file" Then
        folderRecordSet.AddNew
            folderRecordSet("Name") = fil.Name
            folderRecordSet("Size") = fil.Size
            folderRecordSet("Date modified") = fil.DateLastModified
            folderRecordSet("Type") = fil.Type

        folderRecordSet.Update
        End If
    Next fil

    For Each subFol In fol.SubFolders
        folderRecordSet.AddNew
            folderRecordSet("Name") = subFol.Name
            folderRecordSet("Size") = null 
            folderRecordSet("Date modified") = subFol.DateLastModified
            folderRecordSet("Type") = subFol.Type

        folderRecordSet.Update
    Next subFol
    Set GetFolderContents = folderRecordSet
End Function

Function GetNewFolderRecordset() As ADODB.Recordset
    Set GetNewFolderRecordset = New ADODB.Recordset
    With GetNewFolderRecordset
        .Fields.Append "Name", adVarWChar, 255
        .Fields.Append "Size", adInteger, , adFldIsNullable
        .Fields.Append "Date modified", adDate
        .Fields.Append "Type", adVarWChar, 255

        .CursorType = adOpenKeyset
        .CursorLocation = adUseClient
        .LockType = adLockPessimistic
        .Open
    End With

End Function

Then in your Open event you can bring it all together. This snippet is just going to list out your temporary directory contents.

Private Sub Form_Open(Cancel As Integer)
    Set Me.Recordset = Module1.GetFolderContents(Environ("temp"))
End Sub

The form's controls have to be bound to the columns of the recordset which will look like this

enter image description here

When you run it in Datasheet view it will look like this

enter image description here

You can capture the appropriate OnClick events to open up files or if Type = "File Folder" then you can reset the recordset to go one more level deep. You can add in the artificial .. to go up one level too. Or make your own interface. There are plenty of other file/folder attributes in the File and Folder objects if you need more.

Upvotes: 1

Related Questions