Is it possible to query file names from a HDD folder ?

[vamoose]

I have an Access 2000 database with a Table called tbl_Labels. I also have a folder on my hard drive called C:\Labels. The information contained in tbl_Labels is the name of the file (Label47.txt) that should be contained within C:\Labels.

Is it possible to query tbl_Labels and C:\Labels to find missing files that should be on the hard drive ?

Thanks very much.

 

[Golom]

Public Function FileFound(FileName As String) As Boolean
With New FileSystemObject
   FileFound = .FileExists("C:\Labels\" & FileName)
End With
End Function

and use it with

Select FileName
From myTable
Where FileFound([FileName]) = False

You will need a reference to Microsoft Scripting Runtime

 

[vamoose]

I am trying to understand this...
Do both these examples go in a Module ?
Do I need to associate them with a command button to run ?

Thanks, I am a beginner and need advice.

 

[vamoose]

Is this correct, thanks.

Public Function filefound(filename As String) As Boolean
With New FileSystemObject
filefound = .FileExists("c:\labels\" & filename)
End With

End Function

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

Private Sub Form_Open(Cancel As Integer)

DoCmd.RunSQL "Select " & filename & " from tbl_labels where " & filefound([filename]) & "= " & False

End Sub

 

[Golom]

Not quite. As you have defined it, you are calling the FileFound function from your code and not from the SQL statement. Within the code [FileName] is a VB variable and not a database field. I assume that you want to retrieve those FileNames in your table that do not exist so you will need to use the ones retrieved by the SQL and not the single one that is (or may be) defined in some VB variable.

Also [blue]DoCmd.RunSQL[/blue] should be used for Action queries like UPDATE, INSERT, DELETE. This is a SELECT query that returns records but you haven't provided any place to hold the returned records (i.e. a recordset). Try this

Private Sub Form_Open(Cancel As Integer)
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset ( _
             "Select [filename] from tbl_labels " & _
             "Where filefound([filename]) = False"
    [COLOR=black cyan]' Do something with the recordset[/color]
  
End Sub

 

[CautionMP]

vamoose,

Here is another possible approach, it's not any better or worse than what Golom proposed, just different. Create a data file (CSV in this example) that lists the files in a directory. You then use it as a linked table in your database and use more traditional query techniques to identify what files are missing, and then run update/delete queries to update [tt]tbl_labels[/tt] as needed.

[navy]Sub [/navy] BuildFileList(Folder [navy]As String[/navy], Optional FileExtension [navy]As String[/navy])
On [navy]Error Goto[/navy] BuildFileList_Error
[navy]Const[/navy] cOutputFileName [navy]As String[/navy] = "FileList.csv"
[navy]Dim In[/navy]tOutputFile [navy]As [navy]In[/navy]teger[/navy]
[navy]Dim[/navy] strFolder [navy]As String[/navy]
[navy]Dim[/navy] strBuffer [navy]As String[/navy]

intOutputFile = FreeFile

[green]'Double check the folder name syntax[/green]
[navy]If[/navy] Right(Folder, 1) <> "\" [navy]Then[/navy]
  strFolder = Folder & "\"
[navy]Else[/navy]
  strFolder = Folder
[navy]End If[/navy]

[green]'Check To see If a file extension wAs passed[/green]
[navy]If[/navy] FileExtension = "" [navy]Then[/navy]
  strBuffer = Dir(strFolder & "*")
[navy]Else[/navy]
  strBuffer = Dir(strFolder & "*." & FileExtension)
[navy]End If[/navy]

[green]'Check If there are any files[/green]
[navy]If[/navy] strBuffer <> "" [navy]Then[/navy]
  [green]'Open the output file[/green]
  [navy]Open[/navy] strFolder & cOutputFileName [navy]For Output As[/navy] #intOutputFile
  [green]'Write the header row[/green]
  [navy]Write[/navy] #intOutputFile, "Folder", "File", "Date_Modified"
Else
  MsgBox "No files found", vbOKOnly, "BuildFileList"
[navy]End If[/navy]

[green]'Loop through the entire directory[/green]
[navy]Do While[/navy] strBuffer <> ""
  [navy]Write[/navy] #intOutputFile, strFolder, strBuffer, [navy]For[/navy]mat$(FileDateTime(strFolder & strBuffer), "mm/dd/yyyy hh:nn:ss")
  strBuffer = Dir
[navy]Loop[/navy]

Clean_up:
Close #intOutputFile
[navy]Exit Sub [/navy]

BuildFileList_Error:
[green]'Do some Error handling here If you need it[/green]
[navy]End Sub [/navy]

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)