Almost Got it...Just need 1 more piece of code

[BrianPaul]

I used the Example from http://support.microsoft.com/Default.aspx?id=210613
to list files on a hard drive. I modified the code to suite where my files
were at.

Function LocateFile(strFileName As String)
Dim vItem As Variant
With Application.FileSearch
.FileName = strFileName
.LookIn = "N:\Books\Auto"
.SearchSubFolders = True
.Execute
For Each vItem In .FoundFiles
Debug.Print vItem
Next vItem
End With
End Function

Ran the Code in the immediate window: ?LocateFile("*.*")

Result was:
N:\Books\Auto\An Introduction to Buffing & Polishing.pdf
N:\Books\Auto\Auto Repair for Dummies.pdf
N:\Books\Auto\Automobile Repair Tips.PDF
N:\Books\Auto\Haynes Service And Repair Manual.pdf
N:\Books\Auto\how to hot wire a car correctly.txt
N:\Books\Auto\Small Engine Repair.PDF
N:\Books\Auto\The Haynes Welding Manual.pdf

Okay it worked. It listed the files specified in the path of the function.

MY Question: On a Form I want to Put a Command Button. I can use the same
Syntax: ?LocateFile("*.*") on the event click property. However I would
like it to import the results to my table called Ttest with a field called
FileBookName.

Can it be Done? and How? Read through the articles but couldnt find how to
do it.
Thanks,

BRian

 

[Douglas J. Steele]

Personally, I prefer simply using the Dir function, rather than the
FileSearch method:

Function LocateFile()
Dim dbCurr As DAO.Database
Dim strFolder As String
Dim strFile As String
Dim strSQL As String

Set dbCurr = CurrentDb()
strFolder = "N:\Books\Auto\"
strFile = Dir(strFolder & "*.*")
Do While Len(strFile) > 0
strSQL = "INSERT INTO Ttest (FileBookName) " & _
"VALUES ('" & strFolder & strFile & "')"
dbCurr.Execute strSQL, dbFailOnError
strFile = Dir()
Loop
End Function

I removed strFileName as a parameter from your function, since you don't use
it. (For that matter, I'd be inclined to make it a Sub, since you're not
returning a value, however you might need it as a function depending on how
you're invoking it)

 

[Dirk Goldgar]

I used the Example from
http://support.microsoft.com/Default.aspx?id=210613 to list files on
a hard drive. I modified the code to suite where my files were at.

Function LocateFile(strFileName As String)
Dim vItem As Variant
With Application.FileSearch
.FileName = strFileName
.LookIn = "N:\Books\Auto"
.SearchSubFolders = True
.Execute
For Each vItem In .FoundFiles
Debug.Print vItem
Next vItem
End With
End Function

Ran the Code in the immediate window: ?LocateFile("*.*")

Result was:
N:\Books\Auto\An Introduction to Buffing & Polishing.pdf
N:\Books\Auto\Auto Repair for Dummies.pdf
N:\Books\Auto\Automobile Repair Tips.PDF
N:\Books\Auto\Haynes Service And Repair Manual.pdf
N:\Books\Auto\how to hot wire a car correctly.txt
N:\Books\Auto\Small Engine Repair.PDF
N:\Books\Auto\The Haynes Welding Manual.pdf

Okay it worked. It listed the files specified in the path of the
function.

MY Question: On a Form I want to Put a Command Button. I can use the
same Syntax: ?LocateFile("*.*") on the event click property.
However I would like it to import the results to my table called
Ttest with a field called FileBookName.

Can it be Done? and How? Read through the articles but couldnt find
how to do it.
Thanks,

Try this version:

'----- start of revised code (WARNING: air code) -----
Function LocateFile(strFileName As String)

Dim vItem As Variant
Dim db As DAO.Database

Set db = CurrentDb

With Application.FileSearch
.FileName = strFileName
.LookIn = "N:\Books\Auto"
.SearchSubFolders = True
.Execute
For Each vItem In .FoundFiles
db.Execute _
"INSERT INTO Ttest (FileBookName) " & _
"VALUES(" & Chr(34) & vItem & Chr(34) & ")", _
dbFailOnError
Next vItem
End With

Set db = Nothing

End Function
'----- end revised code -----

 

[Tom Wickerath]

Hi Brian,

Here is one method. If FileBookName is a text data type that allows the
maximum number of characters (255), then you may want to check for the lenght
of the vItem value first, to ensure that it does not exceed this limit. If
you place a unique index on the FileBookName field, then you will avoid
getting duplicate records added to the table if the procedure is run more
than one time. The update will fail silently, as long as you do not use the
optional dbFailOnError parameter, which is what you likely want to happen.

Option Compare Database
Option Explicit

Function LocateFile(strFileName As String)
Dim vItem As Variant

With Application.FileSearch
.FileName = strFileName
.LookIn = "N:\Books\Auto"
.SearchSubFolders = True
.Execute

For Each vItem In .FoundFiles
CurrentDb.Execute "INSERT INTO Ttest ( FileBookName ) " _
& "SELECT " & Chr(34) & vItem & Chr(34) & " AS FileBookName;"
Next vItem

End With

MsgBox "Done.", vbInformation

End Function


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Tom Wickerath]

Doug,

A couple of comments

1.) Using the Dir function will not interrogate any subfolders, which was
apparently desired by Brian, since he used .SearchSubFolders = True

2.) Using "VALUES ('" & strFolder & strFile & "')" will choke on any files
the include an apostrophe in the name of the file.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[BrianPaul]

Thanks, Going to try it....Makes since that I wouldn't want to duplicate
it...glad you thought of it before I found out.

 

[BrianPaul]

It worked Just great guys. Just what I needed. I also run the code on my
MP3's and had over 5000, So I copied another MP3 into the directory just to
test out if it would only add 1. It worked great.

This function actually will catalogue directories, etc. Your Solution
should be posted more often because I have seen where this will work for the
majority of persons inquiring about how to catalogue directories.

Thanks again guys.

 

[Tom Wickerath]

Hi Brian,

I'm glad you got it to work. Good luck on your project.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Douglas J. Steele]

Good points, Tom.

I missed the SearchSubFolders setting, and forgot that quotes could be
included in file names. In my defence, I wrote a column for Access Advisor
comparing the various methods, and always use APIs for search folders since
it was significantly faster. I also always wrap text fields in a function to
ensure appropriate quotes.