Why Doesn't Application.filesearch Work

First, a couple of reasons why my databases still run on Microsoft® Access 2002 and then how to handle application.filesearch.

My computers and laptop contain many databases using Microsoft® Access 2002. A large percentage of these databases use QODBC to exchange data between Microsoft® Access and QuickBooks®. If you are busy like me, you know the value of not fixing what is not broken.


The Domino Effect

If you are a developer or use VBA with Microsoft® Access or Visual Basic, you know about the domino effect. If this phrase as it applies to programming is unfamiliar to you, consider yourself lucky. How simple a task it seems to improve a small feature of a database you wrote yourself. And how often that tiny improvement ends up the source of bugs that multiply exponentially.

I have spent many frenzied sessions long into the night repairing what had once been a beautifully functioning database simply because I chose to make a small improvement without considering the ramifications.

If you cannot relate to this scenario, then congratulations! You are either the most excellent programmer on the face of the earth or have an iron will that allows you to resist the temptation to improve a complicated, functioning database.

The application.filesearch function is a huge part of my file linking code as explained in The Link Principle. For reasons previously stated, I am not about to rewrite all my databases. Maybe someday, but not today. Or tomorrow. Or even this year!


Quick Fix

If you use Microsoft® Access 2002 and have installed Microsoft Windows Search 4.0 and do not wish to rewrite all your code, you may use a shortcut to turn the indexer on and off as explained in this post: Windows Search 4.0 interferes with application.filesearch (http://www.vbquick.com/2011/09/windows-search-40-interferes-with.html)

At the time of this writing, I still use this temporary fix on most of my databases because I dread, dread, dread re-writing the programs I use daily. Working without these programs would be like raking my yard with a fork.


Permanent Fix

If you'd like to replace Application.Filesearch with vbDirectory, you might consider this code on my sister site TheGenericDatabase.com: 
     
            Replacing Application.Filesearch with vbDirectory for List Box Rowsource of Files
            www.thegenericdatabase.com/2011/11/file-list-vbdirectory-vba-visual-basic.html


I re-wrote one of my programs using the above method and was surprised to find two interesting improvements:
  1. speed - vbDirectory is faster
  2. placing file names into a table to use as the source of a list box removes the size limit imposed by using a value list of file names - if you do not understand this, no matter, just know that it is way better


FileSystemObject

You can also use filesystemobject to grab file lists and some of my older programs do use it. However, vbDirectory does not require any additional libraries. The less library references you can get away with, the faster your databases will run.


More

For more information on this topic, see: http://support.microsoft.com/kb/935402.

I am not the only one disappointed with the application.filesearch issue. I have heard enterprise programmers complain about their databases being broken after installing Microsoft® Access 2007 and discovering filesearch no longer works.

I hope Microsoft adds application.filesearch back to the next new-and-improved Microsoft® Access. Until then, I'm happy with everything just the way it is.

Do you like this post? Please link back to this article by copying one of the codes below.

URL:

HTML link code:

BB (forum) link code:

No comments:

Post a Comment