Missing Folder?

Question

Good afternoon,

[tblMembers].[txtLocation] contains the file path and filename of all docs stored for each member. (i.e.: L:\Smith, John)

How can I produce a list of members where the [txtLocation] is missing from the L: drive?

Answer 1

For Folders you should use one of the functions from https://www.devhut.net/vba%E2%80%93determine-folder-directory-exists/

Answer 2

You could create a query such as:

SELECT tblMembers.ID, tblMembers.FirstName, tblMembers.LastName, tblMembers.txtLocation, GA_FileExist([txtLocation]) AS FileExists
FROM tblMembers
WHERE GA_FileExist([txtLocation]) = False
ORDER BY tblMembers.txtLocation;

where you can copy the GA_FileExist() function from https://www.devhut.net/vba-determine-if-a-file-exists-or-not/ 

Create a new VBA module and simply copy/paste the function from the link. Then create the query and run it. Obviously, you may need to adjust the field names I used (ID, FirstName, LastName) in the query to match your table field names.

Answer 3

You could create a query such as:

SELECT tblMembers.ID, tblMembers.FirstName, tblMembers.LastName, tblMembers.txtLocation, GA_FileExist([txtLocation]) AS FileExists
FROM tblMembers
WHERE GA_FileExist([txtLocation]) = False
ORDER BY tblMembers.txtLocation;

where you can copy the GA_FileExist() function from https://www.devhut.net/vba-determine-if-a-file-exists-or-not/ 

Create a new VBA module and simply copy/paste the function from the link. Then create the query and run it. Obviously, you may need to adjust the field names I used (ID, FirstName, LastName) in the query to match your table field names.

Thank you Daniel for your very generous reply. I have created the query:

   SELECT t_matters.Nickname, t_matters.Location

FROM t_mattersWHERE (((t_matters.Location) Like "f:*")) OR (((t_matters.Location) Like "k:*")) ORDER BY t_matters.Location;

I have copied your GA_FileExist() function into a module and named the module Daniel in your honor :-)

Function GA_FileExist(ByVal sFile As String) As Boolean 

' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/

On Error GoTo Error_Handler 

    Dim iRetVal               As Integer 

    iRetVal = GetAttr(sFile) 

    GA_FileExist = Not ((iRetVal And vbDirectory) = vbDirectory) 

Error_Handler_Exit: 

    On Error Resume Next 

    Exit Function 

Error_Handler: 

    'We're here because nothing was found 

    Resume Error_Handler_Exit

I run the query and debug your function. It executes iRetVal = GetAttr(sFile) then goes to the error_Handler. It returns to On Error Resume Next, exits the function, and returns this error message: "Data Type mismatch in criteria expression"

I am able to see the value of sFile in the immediate window. It is correctly capturing the [Location] field (which is the full path, i.e.: F:\Jones, Bill).

I have tested it with a found folder as well as a not found folder at the top of the query and the resulting error message is the same in either case (whether true or false)

What am I missing? and does this have anything to do with the fact that I am trying to match folder names, not files within folders?

Answer 4

You can loop through the records using the Dir() function to determine if the folders/files exist.