Select All Files From Folder Using SQL

[DarkSprout]

Is there any way in SQL of get a list of all files in a folder? - It's to populate a combobox.

I've tried the following and lots of variants:

SELECT * FROM [PATH=C:];
'// C: being the path
but none work!

Help!

[OBP]

Darksprout, in stead of using SQL have a look at Application.FileSearch and DIR, you can return the File Names to a Temporary Table and use the table to supply the data to your Combo or list box.
I use this version

Private Sub cmdGetSource_Click()
On Error GoTo errorcatch
Dim count As Integer, rs As Object, foundfile As String, counter As Integer, Filelength As Double, docname As String, fs, filedate As Date
Dim filetype As String
If Me.Selected_Document_Type = "" Or IsNull(Me.Selected_Document_Type) Then Me.Selected_Document_Type = "*.*"
filetype = Me.Selected_Document_Type
Set rs = CurrentDb.OpenRecordset("Documents")
Set fs = CreateObject("Scripting.FileSystemObject")
Msg = "Select a location containing the files you want get Data on."
Directory = GetDirectory(Msg)
If Directory = "" Then Exit Sub
If Right(Directory, 1) <> "\" Then Directory = Directory & "\"
Me.Document_Location = Directory

With Application.FileSearch
.NewSearch
.LookIn = Directory
.Filename = Me.Selected_Document_Type
If Frame1 = 1 Then .SearchSubFolders = False
If Frame1 = 2 Then .SearchSubFolders = True
If .Execute() > 0 Then
counter = .FoundFiles.count
MsgBox "There were " & counter & " file(s) found."
For count = 1 To .FoundFiles.count
filedate = FileDateTime(.FoundFiles(count))
Filelength = FileLen(.FoundFiles(count))
foundfile = .FoundFiles(count)
docname = fs.GetFileName(foundfile)
With rs
.AddNew
![Document Location] = foundfile
![Document Type] = Right(![Document Location], 4)
![Last Accessed] = filedate
![File Size] = Filelength
![Document Name] = docname
.Update
.Bookmark = .LastModified
End With
Next count
Else
MsgBox "There were no files found."
End If
End With
MsgBox "Finished Copying Data to Table"
rs.Close
Set rs = Nothing
Set fs = Nothing

Exit Sub

errorcatch:
MsgBox Err.Description
Set rs = Nothing
Set fs = Nothing

End Sub

[DarkSprout]

I've Used the Idea, but gone down a Slightly Different Route...


'// Place in a Module
Public Enum DoAction
DeleteIfFound = True
DoNotDelete = False
End Enum
Sub DirContent(Path As String, Optional FileType As String = "*")
'// Populate temp table [tbl_tmp_DirContent] with the contents of a Directory
'// ComboBox Properties:
'ColumnCount:=3, ColumnWidths:= 0cm;4cm;0cm, RowSourceType:=Table/Query,BoundColumn:= 1, RowSource(SQL):=
'SELECT [tbl_tmp_DirContent].[anIndex], [tbl_tmp_DirContent].[FileName] FROM [tbl_tmp_DirContent] ORDER BY [FileName];
GoSub CREATE_TABLE
Dim strFile As String
Dim tb As DAO.Recordset
Dim iPos As Integer
Set tb = CurrentDb.OpenRecordset("tbl_tmp_DirContent", dbOpenDynaset)
Path = TrailingSlash(Path)
strFile = Dir(Path & "*." & FileType, vbReadOnly)
Do While strFile <> ""
tb.AddNew
tb![FullName] = strFile
iPos = InStr(1, strFile, ".", vbBinaryCompare)
tb![FileName] = Left(strFile, IIf(iPos > 2, iPos - 1, Len(strFile)))
tb.Update
strFile = Dir()
Loop
Exit Sub
CREATE_TABLE:
Call TableExists("tbl_tmp_DirContent", DeleteIfFound)
DoCmd.RunSQL "CREATE TABLE [tbl_tmp_DirContent] (anIndex COUNTER PRIMARY KEY, [FileName] TEXT(255),[FullName] TEXT(255));"
Return
End Sub
Public Function TableExists(ByVal strTable As String, Optional Action As DoAction = DoNotDelete) As Integer
TableExists = DCount("*", "MSysObjects", "[Type] = 1 AND [Name] = '" & strTable & "'") > 0
If TableExists And DeleteIfFound Then
DoCmd.RunSQL "DROP TABLE [" & strTable & "]; "
End If
End Function
Public Function TrailingSlash(ByVal strIn As String) As String
strIn = Trim(Nz(strIn, ""))
TrailingSlash = IIf(Len(strIn) > 0, IIf(Right(strIn, 1) = "\", strIn, strIn & "\"), strIn)
End Function

To Call:
Private Sub Form_Load()
'// Go Create Table
DirContent Application.CurrentProject.Path, "*"
End Sub
...and to referrence the Full File Name
FileName = [Combo0].Column(2)

[OBP]

Great, as long as you got it to work :thumb