Can this be a query rather than saved to table

[sxschech]

I found this code on tek-tips and was wondering if it could be modified to display results as a query rather than saved to a table. Reason being is that would like to use it dynamically to join with other queries to use for filters and where clauses without having to remember to run code to refresh the table.

CurrentProject.Connection.Execute "Insert into tblFileNames (FileName) values ('" & Left(strFN, Len(strFN) - 4) & "')"

 

[PHV]

Where is strFN coming from ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sxschech]

A string holding the results of a Dir() function

Here is the entire sub...

Sub FileNames()


    Dim strFN As String
    Dim myfolder As String
    myfolder = "P:\Library Archives & Photos\BNWSM"
  
    strFN = Dir(myfolder & "\*.jpg")
    Do
        CurrentProject.Connection.Execute "Insert into tblFileNames (FileName) values ('" & Left(strFN, Len(strFN) - 4) & "')"
        'Debug.Print strFN
        strFN = Dir()
    Loop Until strFN = ""
End Sub

 

[Zion7]

sxschech,
Even as a query, you'll need to update, as long as files are being added & deleted.

Maybe use it as a function.

Function FileNames()

Dim strFN As String
Dim myfolder As String
myfolder = "P:\Library Archives & Photos\BNWSM"

strFN = chr(34) & Dir(myfolder & "\*.jpg") & chr(34)

Do
strFN = strFN & "," & chr(34) & Dir() & chr(34)
Loop Until strFN = ""

End Function

So your queries can do this

SELECT * FROM tblFiles WHERE txtFile IN(FileNames)

 

[sxschech]

Zion7,

Just had a chance to try your code. I am receiving a run-time error 5 Invalid procedure call or argument. It seems like it is building the string, but errors out at the end. The first item doesn't show a quote and the last item ends with a comma and double quotes.

Is there a limit to how many items can be listed in the In statement?

This is a partial list of what is shown in strFN at the time of the error. The actual string contains over two hundred file names.
[tt]
C-204,"C-100","C 100_test","C 109","C 135","C 204","C 302","C-305","C-308","C-608","C-612","C 700","C 701","C 702",""
[/tt]

 

[Zion7]

You may have to check for "illegal", file names,
and truncate the final string


Do Until strFN = ""

strFN = strFN & "," & chr(34) & Dir() & chr(34)
Loop

strFN = Left(strFN, Len(strFN)-2)


JUST FOR EXAMPLE!

play around, until you get a valid criteria string.