You can do it from within Access with a little bit of VBA code which uses the
Dir function to loop through the folder, but first create a table Files with
a text field FileName to hold the data. The add the following procedure to
any standard module in the database. Do this by either selecting an existing
module form the Modules tab of the database window or creayte a new module.
In either case just paste the code from below the two lines which you'll find
already in place at the start of the module. Then save it under a name of
your choice, but don't use the same name as the procedure. I always prefix
module names with bas, e.g. basUtilities for a module containing various
utility functions or procedures:
Public Sub ListFiles(strFolder As String, Optional strPattern As String =
"*.*")
Dim cmd As ADODB.Command
Dim strSQL As String
Dim strFile As String
Dim strFullPath As String
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
' first empty table
strSQL = "DELETE * FROM Files"
cmd.CommandText = strSQL
cmd.Execute
' then fill table
strFullPath = strFolder & "\" & strPattern
strFile = Dir(strFullPath)
If strFile <> "" Then
strSQL = "INSERT INTO Files(Filename) VALUES" & _
"(""" & strFile & """)"
cmd.CommandText = strSQL
cmd.Execute
strFile = Dir()
Do While strFile <> ""
strSQL = "INSERT INTO Files(Filename) VALUES" & _
"(""" & strFile & """)"
cmd.CommandText = strSQL
cmd.Execute
strFile = Dir()
Loop
End If
End Sub
This procedure will empty and fill the table each time you run it, so if
you want to keep the data in several tables then copy the table under a new
name after using the procedure to fill it.
The procedure allows you to enter a pattern or list all files in the folder,
so to list all files you might call it like so:
ListFiles "C:\MyFolder\MySubFolder"
To list all .mdb files in the folder you'd call it like so:
ListFiles "C:\MyFolder\MySubFolder", "*.mdb"
You can call the function from anywhere in the database. You could for
instance simply enter something like the above into the debug window (aka the
immediate pane), which you can open by pressing Ctrl+G. Or you could have a
form with a text box to enter the path and pattern (if any) and a button to
call the procedure, taking its parameters form the text boxes. You can even
set a form up so it opens a dialogue in which you can browse to a folder
rather than having to type the path into a text box. You can find code for
such a dialogue at:
http://www.developerfusion.co.uk/show/2127