Microsoft Access – Find and Replace Issue

35 pts.
Tags:
Access
Access 2007
Microsoft Access
Microsoft Office 2007
Oracle
In Microsoft Access 2007, the "Search Fields As Formatted" check box checked results in a serious performance hit when a Find is done on a linked Oracle table. Even though the Find is being done on an indexed field, and the Access link to that table is aware that the field is indexed, the Find still does a sequential search through the table. Each record is brought back to the PC to see if it has the desired value. As a result, a value that appears in the 200,000th record will require a significant amount of time (many, many minutes), before it's found. On the other hand, if the "Search Fields As Formatted" check box is unchecked, then the Find operation will only take a few seconds. I am trying to find a "Registry Hack" that would change the "Search Fields as Formatted" unchecked, versus checked. Any suggestions?

Answer Wiki

Thanks. We'll let you know when a new response is added.

I experimented with a dummy database and discovered that after turning off the “Search as formatted” option, the option remained off for subsequent searches.

If you are in a form when the search is initiated, you could then create a command button to initiate the search. Use the wizard to create a search action on the Click event and let it set up the DoCmd.FindRecord command line. The default is to set Search as formated to False.
The VBA help topic “FindRecord Method” shows the complete description of the command line options:

expression.FindRecord(FindWhat, Match, MatchCase, Search, SearchAsFormatted, OnlyCurrentField, FindFirst)

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following