query to test if external file exists 2

[djnick]

Sorry about this ..
newbie still learning..
I need to write a query which basically pulls a field called membersid and then test within a field called phototaken a true or false result.

The test is to check if a file exist of the same membersid name. The photos of the member are all jpg. and live in a subdirectory under neath the database location called photos.

How can i do this to check each member and then check if there is a file and then alter the check box to be correct.

Please can you explain everything clearly and do not assume anything of knowledge as I am still learning..

Many thanks ..

Nick

 

[LonnieJohnson]

There is a Visual Basic function called Dir. It checks a given path to see if a file exist.

You could build a procedure that would iterate thru your recordset. As you go thru the recordset you would concantenate the MEMBERSID field with the path using the Dir funtion. If our funtion returns a null, then the file does not exist.

At this point you could programmatically set your check box.

Go to help or the Office Assistant and type Dir as your search criteria. Take a look a this first and see if this is something you want to do.
ljprodev@yahoo.com
Professional Development
MS Access Applications

 

[tlbroadbent]

Here is a nifty query that will update [Phototaken] to "Yes" if the file exists and "No" if it doesn't exist. This method requires no VBA code and no iterating through a record set.

UPDATE PhotoTable SET PhotoTable.PhotoTaken = IIf([MemberID] & ".jpg"=Dir$("photos\" & [MemberID] & ".jpg&quot,Yes,No);

You may need to tweak it a little for your database and directory structure and names.
Terry

http://members.home.net/tlbroadbent/index.htm

 

[LonnieJohnson]

Thanks Terry,

I'm going to try this one myself in place of my above method. I do file checking and the above was a make-shift process I came up with to get me thru.

Ya got my vote...B-) ljprodev@yahoo.com
Professional Development
MS Access Applications

 

[wirelessben]

Is there something wrong with the Dir function in MS Access 2002, build 10.2616.2625?

I am using the following SQL in build 10.2616.3311 without problems:

Select IIF(Dir("C:\appPath\Pictures\" & Personnel.[PersonnelID] & ".jpg")="",False,True) as HasPicture FROM Personnel

When executing the same Access application on another computer running Access 2002 with build 2625, I get the error "Undefined function 'Dir' in expression".

I get the same error with Dir$. The file format is Access 2000.

The non-working machines happen to be Windows XP. The working machine is Windows 2000.

 

[PHV]

HAve you checked the references on your non working application ?
While in VBE (Alt-F11), menu Tools -> References ...

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

 

[wirelessben]

PHV,

Yes, I checked for the references. They are checked in this order on both machines:

VBA
MS Access 10.0 Object Library
OLE Automation
MS Office 10.0 Object Library
MS DAO 3.6 Object Library

ADO is unchecked because DAO is faster.

I thought the problem might be permissions, but everyone has full control over the application directory.

Stumped.

 

[PHV]

No error if you compile the modules ?
menu Debug -> Compile

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

 

[wirelessben]

PHV,

It compiles with no problems.

 

[wirelessben]

I got around the problem by putting the Dir function in a user defined function. The query calls the user defined function. Now it works fine.

I tend to agree with PHV and Microsoft that it is a references problem. Manually refreshing the references on the user's machine did not fix it, however.

I am still curious as to why Dir does not work directly inside a query on some users' machines.

 

[PHV]

Same version of MDAC on all the machines.
May be a security level issue.

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