Hi ssanfu!
I spell the fields out for you so I don't have to explain them. So they actually are FName, FAtttb, FCreate, DLA,DLM, FDrive, FPath, FSize. Allen's code actually brings in not only the file name but the path as well. As I mentioned in post 1 we have duplicate files for the same projects hiding in the project directory. Allen's code also tracks down files in sub directories to combat just this issue. The file names are pulled with the file types so I do not have to worry about that.
I have been able to add the FSize information to the table by going to the insert query in the code and adding this line: & ", """ & FileLen(strFolder & strTemp) & """;"
I built this only to find it in an example code on line a little later. Just when I think I can explain what I did...... i'll understand it in time!
Currently working on inserting File Date Creation or FDate. I am using this code: & "' , #" & FDate & "#;" but now it runs but only gives me 1 file instead of the 31,000 files it usually pulls.
In the end the database will have a form that will use a dropdown to lookup projects and what files are connected to those projects. Using file date and size along with last modified we can start looking at the files to see which files need to be deleted, combined or kept. June7 helped create a hyperlink with the file name and path information that is being pulled down all ready. It works great! Just want to get more tools to look these files.
So from this little bit of code can you help me find my error?
Code:
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 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 FDate As Date
'Add the files to the folder.
strFolder = TrailingSlash(strFolder)
strTemp = Dir(strFolder & strFileSpec)
Do While strTemp <> vbNullString
gCount = gCount + 1
SysCmd acSysCmdSetStatus, gCount
strSQL = "INSERT INTO Files " _
& " (FName, FPath, FDate, FSize) " _
& " SELECT """ & strTemp & """" _
& ", """ & strFolder & """" _
& "', #" & FDate & "#" _
& ", """ & FileLen(strFolder & strTemp) & """;"
CurrentDb.Execute strSQL
colDirList.Add strFolder & strTemp
strTemp = Dir
Loop
If bIncludeSubfolders Then
'Build collection of additional subfolders.
strTemp = Dir(strFolder, vbDirectory)
Do While strTemp <> vbNullString
If (strTemp <> ".") And (strTemp <> "..") Then
If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0& Then
colFolders.Add strTemp
End If
End If
strTemp = Dir
Loop
'Call function recursively for each subfolder.
For Each vFolderName In colFolders
Call FillDirToTable(colDirList, strFolder & TrailingSlash(vFolderName), strFileSpec, True)
Next vFolderName
End If
Exit_Handler:
Exit Function
Thanks!