Solved Access VBA ways to obtain file system directory listing

[Isaac]

???? How would you suggest getting the output of command line DIR back into Access? There is no queue to VBA interface that I know of.... unlike ooRexx which has Rxqueue that you can pipe to and that interfaces directly to the ooRexx memory space.

I am thankful,

I was referring to the VBA function DIR

 

[theDBguy]

I was referring to the VBA function DIR

Just FYI, if I remember correctly, you can't use Dir() in a recursive function. For some reason, I think Access only keeps one instance of Dir() in memory, so the previous calls get overwritten by the succeeding ones.

 

[The_Doc_Man]

Whereas FileSystemObject lets you create "directory objects" that retain content and that can be used when dealing with recursion (by creating another directory object for a deeper level.)

 

[Isaac]

Just FYI, if I remember correctly, you can't use Dir() in a recursive function. For some reason, I think Access only keeps one instance of Dir() in memory, so the previous calls get overwritten by the succeeding ones.

I've definitely used DIR recursively, did so at my previous job to this one where I wanted to traverse endless subfolders.

 

[Isaac]

Note - my experience may have been in Excel VBA, but I strongly doubt it would be different in any Office host

Either way, my point is for whatever they can do, DIR will be 1000x faster than FSO.

 

[mdlueck]

I was referring to the VBA function DIR

Oh, I did not know there was one in current VBA. Like I began the thread, Internet search first lead me to a solution which had been removed from Access VBA. A suggestion with sample code came to use a FSO, and I was able to make it work. I will review what DIR is.

I am thankful,

 

[mdlueck]

DIR will be 1000x faster than FSO.

And for me, the FSO solution complete with editing (opening existing, creating new fixed file) completed in a literal blink of the eye. I cannot imagine 1000x faster than a blink.

I am thankful,

 

[The_Doc_Man]

In general, when you call things from one of the DLL files that are the most common reference file types, they are compiled to machine code and will be the fastest possible execution of the function possible, whereas VBA - which is interpreted or emulated but not executed - will usually be slower.

 

[Isaac]

And for me, the FSO solution complete with editing (opening existing, creating new fixed file) completed in a literal blink of the eye. I cannot imagine 1000x faster than a blink.

I am thankful,

Yeah, with folders containing a small number of files, there might not be much difference. Volume will increase clarity on the subject, but I agree, it's probably negligible, and since VBA can't deal in milliseconds (I don't think), then impossible to test at smaller volumes. Best of luck on the project!

 

[MajP]

Volume will increase clarity on the subject, but I agree, it's probably negligible, and since VBA can't deal in milliseconds (I don't think), then impossible to test at smaller volumes.

I need to fix my demo and I will post when done. I built a db that spans a directory and logs the files to a table then displays the files in a treeview. I have a button to do the span using FSO or DIR. In my testing the DIR was about 2-5 times faster and the bigger the number of files and directories the bigger the difference. I did not see anything approaching 1000x times. However I did nothing more than 23k files so with 100k or M files maybe you could get up there. The difference in speed is not linear.

I think at my max test of 23k files and 1k sub directories the difference was about 90 s versus 500 sec. (since I am logging the files a lot of that time is in the logging but should be comparitive).

The problem I found with DIR is that it is very unforgiving. If you throw an error it is really hard to continue where you left off and it throws some weird errors. For example it will find a file but then you check the attributes and it will say file not found. I think you can return a file with a path name with bad characters but you cannot then read that path in another function.

Some of my problem is explained here and why it is hard to continue if you throw an error.

The Subtle Dangers of Dir()

If Len(Dir(FullPath)) > 0 Then 'the file exists. This potential bug patiently waits to pounce and ruin another poor programmer's day.

nolongerset.com

Now his fix is not really a dir fix since the solution is to incorporate some FSO.

So I agree DIR is faster, but it is hard to make it work without errors and then continuing from those errors.

 

[The_Doc_Man]

Yeah, with folders containing a small number of files, there might not be much difference. Volume will increase clarity on the subject, but I agree, it's probably negligible, and since VBA can't deal in milliseconds (I don't think), then impossible to test at smaller volumes. Best of luck on the project!

You can use the Timer() function, which will return a SINGLE value in milliseconds since midnight (i.e. time of day) and can then get at least another decimal point for time. However, it is slightly misleading since the time that is returned is only updated once every 1Hz of your power source. I.e. in the USA the "clock-tick" is 60 Hz, so one tick is 0.016666 seconds. Where you are on different power speeds (I think I've seen some 50 Hz power sources), the tick would be 0.02 seconds. And there IS such a thing as accessing the crystal clock, which can theoretically give you microseconds, but you can only use it via API and the call itself isn't fast. You can probably still get single milliseconds from the High Precision Timer routines, MAYBE even 0.1 milliseconds - but probably not closer than that from Access because the call is, of course, interpreted/emulated, not actually executed.

 

[Isaac]

I need to fix my demo and I will post when done. I built a db that spans a directory and logs the files to a table then displays the files in a treeview. I have a button to do the span using FSO or DIR. In my testing the DIR was about 2-5 times faster and the bigger the number of files and directories the bigger the difference. I did not see anything approaching 1000x times. However I did nothing more than 23k files so with 100k or M files maybe you could get up there. The difference in speed is not linear.

I think at my max test of 23k files and 1k sub directories the difference was about 90 s versus 500 sec. (since I am logging the files a lot of that time is in the logging but should be comparitive).

The problem I found with DIR is that it is very unforgiving. If you throw an error it is really hard to continue where you left off and it throws some weird errors. For example it will find a file but then you check the attributes and it will say file not found. I think you can return a file with a path name with bad characters but you cannot then read that path in another function.

Some of my problem is explained here and why it is hard to continue if you throw an error.

The Subtle Dangers of Dir()

If Len(Dir(FullPath)) > 0 Then 'the file exists. This potential bug patiently waits to pounce and ruin another poor programmer's day.

nolongerset.com

Now his fix is not really a dir fix since the solution is to incorporate some FSO.

So I agree DIR is faster, but it is hard to make it work without errors and then continuing from those errors.

Well yes, perhaps my "1000x" was a bit of an exaggeration to make a point. Thanks for testing - that's interesting.

It depends on your network latency too. When I was on a VPN using network shares for my previous job, DIR seemed so many times faster than FSO and the ability to be recursive was specially useful. I was imaging pretty simple work with DIR, like recursing folders and files, but I take your point that there are some gotchas - thanks for posting them too.