Access vba Check if file exists

Question

I have a database split in FrontEnd and BackEnd.

I have it running: i) in my office ii) updating/testing in my personal computer.

My BackEnd file is running in different Folder location according to computer running.

I want to place a code and check if file exist.

Code:

Sub FileExists()
Dim strPath As String   '<== Office.
Dim strApplicationFolder As String
Dim strPathAdmin As String   '<== Admin.

strPath = "\\iMac\Temp\"
strApplicationFolder = Application.CurrentProject.Path
strPathAdmin = strApplicationFolder & "\Temp\"

If Dir(strApplicationFolder & "SerialKey.txt") = "" Then
'===> Admin User.
    If Dir(strPathAdmin & "*.*") = "" Then
        '===> Empty Folder.
    Else
        '===> Files on folder.
    End If
Else
    '===> Office User.
    If Dir(strPath & "*.*") = "" Then
        '===> Empty Folder.
    Else
        '===> Files on folder.
    End If
End If
End Sub()

I have this until now.

Any help.

Thank you...

Answer 1

This is a very belated reply, but don't reinvent the wheel. VBA can access the FileSystemObject, which includes a powerful set of methods that fetch file and folder information without requiring you to write your own functions, and the resulting code is much easier to read.

Second, borrowing on the previous answer, you know the folders you want the code to look at, and they don't change much, but because they could, I would also move your parameters into the declaration so they can be customized if needed or default to existing values.

Finally, FileExists is a verb, which should scream Function rather than Sub, so I'm guessing you want to return something and use it elsewhere in higher level code. It so happens that FileExists is already the name of a method in FileSystemObject, so I'm going to rename your function to LocatePath. You could return the valid path of the file you're looking for and decide by convention to quietly return an empty string "" if the target isn't found, and handle that in the calling procedure.

fs.BuildPath(strRootPath, strFileOrSubDir) appends strFileOrSubDir to strRootPath to construct a properly formatted, full pathname and you don't need to worry about whether you have backslashes (or too many) between the two. It can be used to append files, or subdirectories, or files in subdirectories.

fs.FileExists(strFullPath) is simple, returns True if strFullPath exists, and False otherwise.

fs.GetFolder(strFolderName) returns a Folder object that has a .Files property, which is a Collection object. Collection objects in turn have a .Count property, which in this example indicates how many files are in strFolderName. The Collection object could also be used to iterate over all the files in the folder individually.

What follows is your code refactored to incorporate all the changes I recommend according to what I think you're trying to achieve. It's not as symmetric as I'd like, but mirrors your code, and it's simple, easy to read, and finished in the sense that it does something.

Function LocatePath(Optional strWorkPath as String = "\\iMac", 
       Optional strHomePath as String = CurrentProject.Path,
       Optional strFile as String = "SerialKey.txt"
       Optional strSubDir as String = "Temp") as String

   Dim fs as Object
   Set fs = CreateObject("Scripting.FileSystemObject")

   If fs.FileExists(fs.BuildPath(strHomePath, strFile)) Then
      If fs.GetFolder(fs.BuildPath(strHomePath, strSubDir).Files.Count > 0 Then '===> Admin User.
         LocatePath = fs.BuildPath(strHomePath, strFile)
      ElseIf fs.GetFolder(fs.BuildPath(strWorkPath, strSubDir).Files.Count > 0 Then '===> Office User
         LocatePath = fs.BuildPath(strWorkPath, strFile)
      End If
   Else 'Target conditions not found
      LocatePath = ""
   End If
   Set fs = Nothing
End Function()

Ideally, I probably wouldn't specify strHomePath as String = CurrentProject.Path because strWorkPath as String = CurrentProject.Path is probably also true when you're at work, and you would want to do a better job of differentiating between the two environments. This is also what causes the little problem with symmetry that I mentioned.

Answer 2

Create a small function to check if a file exists and call it when needed.

Public Function FileExists(ByVal path_ As String) As Boolean
    FileExists = (Len(Dir(path_)) > 0)
End Function

Since the backend database paths dont change, why dont you declare two constants and simply check their value?

Sub Exist()

    Const workFolder As String = "C:\Work Folder\backend.accdb"
    Const personalFolder As String = "D:\Personal Folder\backend.accdb"

    If FileExists(workFolder) Then
        'Work folder exists
    End If

    '....

    If FileExists(personalFolder) Then
        'Personal folder exists
    End If
End Sub