use the file system in a query?

[martinmike2]

Hello,

What I am trying to do is run a query to get a unique field form the
table then compare those results against a set of files and display
only the records that don't have a matching file. Is this possible?

If it is, I am not very good with calling windows commands from VBA
and would appreciate some example code to work from.

 

[Dirk Goldgar]

Hello,

What I am trying to do is run a query to get a unique field form the
table then compare those results against a set of files and display
only the records that don't have a matching file. Is this possible?

If it is, I am not very good with calling windows commands from VBA
and would appreciate some example code to work from.

I think this is a question you're going to have to explain in much more
detail. What constitutes a match? Are you talking scanning text *inside* a
set of files? Or just looking for matching file names? Would all the files
be in the same folder? Also, are you really talking about running a query
that returns results as a set of rows, such as might be displayed in
datasheet view? Or do you just want to run a VBA routine that examines
files and returns some sort of list of matching files?

You'd better give some examples.

 

[martinmike2]

I think this is a question you're going to have to explain in much more
detail.  What constitutes a match?  Are you talking scanning text *inside* a
set of files?  Or just looking for matching file names?  Would all the files
be in the same folder?  Also, are you really talking about running a query
that returns results as a set of rows, such as might be displayed in
datasheet view?  Or do you just want to run a VBA routine that examines
files and returns some sort of list of matching files?

You'd better give some examples.

Mr. Goldgar,

Sorry about the confusion.

I am trying to match match picture files to personnel in a table.
Each person has a number associated with them as the primary key of
the table. The picture files are named with this number and are all
located in the same folder.

With that said, what I want to do is compare the filenames in the DBdir
\pictures folder with all of the records in the table PERS and return
the rows from PERS that do not have matching files in the folder. A
match would constitute a file having a name that = unique field in
PERS.

 

[martinmike2]

for example:

Picture:
DBdir\pictures\123.jpg

PERS Row:
123 | Martin | Michael | J

123 = unique number for each record

PERS Record with 123 = picture file 123.jpg (Do not include this row
in results)

 

[Dirk Goldgar]

I am trying to match match picture files to personnel in a table. Each
person has a number associated with them as the primary key of the table.
The picture files are named with this number and are all located in the
same folder.

With that said, what I want to do is compare the filenames in the DBdir
\pictures folder with all of the records in the table PERS and return the
rows from PERS that do not have matching files in the folder. A match
would constitute a file having a name that = unique field in PERS.

By "DBDir", do you mean whatever folder the database is currently sitting
in? To get that into a query, you'll need to define a function to return
it, like this:

'----- start of code -----
Function fncPictureFolder() As String

fncPictureFolder = CurrentProject.Path & "\Pictures\"

End Function
'----- end of code -----

That function would go into a standard module in the database.

With the function in place, then in principlea query like this should work
(if executed from Access):

SELECT * FROM PERS
WHERE Len(Dir(fncPictureFolder() & [PERS ID] & ".jpg"))=0

You didn't give me the name if the field in PERS that contains the ID you
want to look up, so I've called it "PERS ID" in the above SQL. You should
change that to the correct name.

I said that should work "in principle", because (if I recall correctly) some
levels of security can prevent the execution of queries that use the Dir()
function. If you find that the Dir() function is not recognized in the
query, you can disable "jet sandbox mode" to allow it -- see this link:

http://support.microsoft.com/default.aspx?scid=kb;en-us;294698&Product=acc

 

[martinmike2]

unfortunatly, Dir did not work and I cannot modify the registry. Is
there a way to enumerate the files in the folder, put them into an
array and then use the array in the query?

 

[Dirk Goldgar]

unfortunatly, Dir did not work and I cannot modify the registry. Is
there a way to enumerate the files in the folder, put them into an
array and then use the array in the query?

I suppose you might create a public function fncFileExists() in a standard
module, and call that from the query:

'----- start of code -----
Function fncPictureFolder() As String

fncPictureFolder = CurrentProject.Path & "\Pictures\"

End Function

Function fncFileExists(strFileName As String) As Boolean

On Error GoTo Err_Handler

fncFileExists = Len(Dir(fncPictureFolder() & strFileName)) > 0

Exit_Point:
Exit Function

Err_Handler:
fncFileExists = False
Resume Exit_Point

End Function
'----- end of code -----


The SQL would look like this:

SELECT * FROM PERS
WHERE fncFileExists([PERS ID] & ".jpg")

 

[martinmike2]

unfortunatly, Dir did not work and I cannot modify the registry.  Is
there a way to enumerate the files in the folder, put them into an
array and then use the array in the query?

I suppose you might create a public function fncFileExists() in a standard
module, and call that from the query:

'----- start of code -----
Function fncPictureFolder() As String

    fncPictureFolder = CurrentProject.Path & "\Pictures\"

End Function

Function fncFileExists(strFileName As String) As Boolean

    On Error GoTo Err_Handler

    fncFileExists = Len(Dir(fncPictureFolder() & strFileName)) > 0

Exit_Point:
    Exit Function

Err_Handler:
    fncFileExists = False
    Resume Exit_Point

End Function
'----- end of code -----

The SQL would look like this:

    SELECT * FROM PERS
    WHERE fncFileExists([PERS ID] & ".jpg")

yep, that one worked, thanks a lot Mr. Goldgar

 

[defay gerard]

unfortunatly, Dir did not work and I cannot modify the registry. Is
there a way to enumerate the files in the folder, put them into an
array and then use the array in the query?

I suppose you might create a public function fncFileExists() in a standard
module, and call that from the query:

'----- start of code -----
Function fncPictureFolder() As String

fncPictureFolder = CurrentProject.Path & "\Pictures\"

End Function

Function fncFileExists(strFileName As String) As Boolean

On Error GoTo Err_Handler

fncFileExists = Len(Dir(fncPictureFolder() & strFileName)) > 0

Exit_Point:
Exit Function

Err_Handler:
fncFileExists = False
Resume Exit_Point

End Function
'----- end of code -----


The SQL would look like this:

SELECT * FROM PERS
WHERE fncFileExists([PERS ID] & ".jpg")


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)