Filling a table with file names from a remote folder.

[Neil Graham]

Hi

I got the following code to work (snippet) filling a table with path, name
and created date.

But when i try to add an extra field to show modified date the code runs and
says it has done the work but nothing appears in the table. The modified code
is below the original code that works.

the table has been altered to include an extra date field MDate.

-------------------
Private Function FillDirToTable(colDirList As Collection _
, ByVal strFolder As String _
, strFileSpec As String _
, bIncludeSubfolders As Boolean)

'Build up a list of files, and then add to this list, any additional folders
On Error GoTo Err_Handler

Dim strTemp As String
Dim colFolders As New Collection
Dim vFolderName As Variant
Dim strSQL As String
Dim FileDate As Date
Dim DateModified As Date
Dim fs, f '*****NEW - Add Date Created/Modified
Set fs = CreateObject("Scripting.FileSystemObject") '*****NEW - Add Date
Created/Modified

'Add the files to the folder.
strFolder = TrailingSlash(strFolder)
strTemp = Dir(strFolder & strFileSpec)
'dtCreated = f.dateCreated

Do While strTemp <> vbNullString
gCount = gCount + 1
SysCmd acSysCmdSetStatus, gCount
Set f = fs.GetFile(strFolder & strTemp) '*****NEW - Add Date
Created/Modified
FileDate = f.dateCreated
'DateModified = f.DateModified
strSQL = "INSERT INTO DirFiles " _
& " (FName, FPath, FDate) " _
& " SELECT '" & strTemp _
& "', '" & strFolder _
& "', #" & FileDate & "#;"
CurrentDb.Execute strSQL
'colDirList.Add strFolder & strTemp
colDirList.Add strFolder & strTemp & "; DateCreated: " & f.dateCreated & ""
strTemp = Dir
Loop

-------------------------

this does not work

Private Function FillDirToTable(colDirList As Collection _
, ByVal strFolder As String _
, strFileSpec As String _
, bIncludeSubfolders As Boolean)

'Build up a list of files, and then add to this list, any additional folders
On Error GoTo Err_Handler

Dim strTemp As String
Dim colFolders As New Collection
Dim vFolderName As Variant
Dim strSQL As String
Dim FileDate As Date
Dim DateModified As Date
Dim fs, f '*****NEW - Add Date Created/Modified
Set fs = CreateObject("Scripting.FileSystemObject") '*****NEW - Add Date
Created/Modified

'Add the files to the folder.
strFolder = TrailingSlash(strFolder)
strTemp = Dir(strFolder & strFileSpec)
'dtCreated = f.dateCreated

Do While strTemp <> vbNullString
gCount = gCount + 1
SysCmd acSysCmdSetStatus, gCount
Set f = fs.GetFile(strFolder & strTemp) '*****NEW - Add Date
Created/Modified
FileDate = f.dateCreated
DateModified = f.DateLastModified
strSQL = "INSERT INTO DirFiles " _
& " (FName, FPath, FDate, MDate) " _
& " SELECT '" & strTemp _
& "', '" & strFolder _
& "', #" & FileDate & "#" _
& "', #" & DateModified & "#;"
CurrentDb.Execute strSQL
'colDirList.Add strFolder & strTemp
'colDirList.Add strFolder & strTemp & "; DateCreated: " & f.dateCreated & ""
colDirList.Add strFolder & strTemp & "; DateCreated: " & f.dateCreated & ";
DateModified: " & f.DateLastModified
strTemp = Dir
Loop
-------------------


--
Thanks for your Help

Regards

Neil