MS-Access nested DIR - check if a file exists elsewhere whilst looping through a folder

Question

I have used the DIR() command in Microsoft Access 2003 to loop through the files in folder A. This works fine, but I need to check if each file also exists in another location (folder B), and only process the file if it doesn't exist in folder B.

The problem is that checking for the file existing in folder B also uses the DIR() function and this then resets or confuses the original one, with the result that no further files are found in folder A.

Is there a way to check if a file exists without using DIR?

Or, is there a way to have a separate instance of DIR?

I suppose I could build a list of the files in folder A into an array and then process the entries in the array, but this seems rather 'clunky'

Any suggestions for a better solution?

Thanks

Answer 1

I think the FileSystemObject may be what you're looking for. You would need to set a reference for Microsoft Scripting Runtime (I think that's the name) if you want early binding. I just use late binding to create the object without a reference set.

Dim objFSO As Object
Set objFSO = CreateObject("Scripting.FileSystemObject")

Then if you store the path to folder B in strFolderB and store the file name from Dir() in strFileName, you can do something like this within your main loop.

If Not (objFSO.FileExists(strFolderB & Chr(92) & strFileName)) Then
    'do your processing '
End if

Answer 2

Depending on your operating system(s) and requirements, you may find searching the SystemIndex useful. Here are some notes.

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String

''This is the Windows Search connection string to use
cn.Open "Provider=Search.CollatorDSO;" _
    & "Extended Properties='Application=Windows';"

''SQL SELECT  statement specifies what properties to return,
''            you can add more if you want
''    FROM -  use SystemIndex for a local query or
''            MACHINENAME.SystemIndex for remote
''    WHERE - specify restrictions including SCOPE and other
''            conditions that must be true

''To add scope restriction:
''strSQL = "SELECT System.ItemName, System.ItemTypeText, " _
''       & "System.Size FROM SystemIndex " _
''       & "WHERE Scope='file:c:\Users\'"

strSQL = "SELECT System.ItemName, System.ItemTypeText, " _
       & "System.Size, System.ItemFolderPathDisplay " _
       & "FROM SystemIndex " _
       & "WHERE System.ItemName='AnExampleFile.mdb'"

rs.Open strSQL, cn
rs.MoveFirst

Do Until rs.EOF
    Debug.Print rs.Fields.Item("System.ItemName")
    Debug.Print rs.Fields.Item("System.ItemTypeText")
    Debug.Print rs.Fields.Item("System.Size")
    Debug.Print rs.Fields.Item("System.ItemFolderPathDisplay")
    Debug.Print String(30, "-")
    rs.MoveNext
Loop

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

Further Information:
http://msdn.microsoft.com/en-us/library/bb266517(VS.85).aspx
http://msdn.microsoft.com/en-us/library/bb419046(v=VS.85).aspx
http://msdn.microsoft.com/en-us/library/bb776859(v=VS.85).aspx
http://msdn.microsoft.com/en-us/library/bb231297(v=VS.85).aspx

Answer 3

I prefer the file APIs as I don't quite trust the FSO. Also see FindFirstFile: Performance Comparison - FSO vs. API With less than say a hundred files the performance difference isn't that significant. Lots of sample code that will be useful for a straight file list or recursively thorugh sub folders at File API Routines