Slow search when Search af Formatted

  • Thread starter Thread starter Monte
  • Start date Start date
M

Monte

I have linked from a regular ACC2002 MDB to a large SQl
table that is uniquely keyed on an nvarchar field. For
some reason, when I search for a record via that key
field, and "search as formatted" is checked it takes
nearly 10 times longer to find the record. Also, when I
use the 'findrecord' method in VBA, it seems to default to
using the "search as formatted" if that was left checked
by the user on the regular search dialog. Anyone else seen
this effect?
 
Hello Monte:
You wrote in conference microsoft.public.access.adp.sqlserver on Wed, 22
Sep 2004 06:29:11 -0700:

M> I have linked from a regular ACC2002 MDB to a large SQl
M> table that is uniquely keyed on an nvarchar field. For
M> some reason, when I search for a record via that key
M> field, and "search as formatted" is checked it takes
M> nearly 10 times longer to find the record. Also, when I
M> use the 'findrecord' method in VBA, it seems to default to
M> using the "search as formatted" if that was left checked
M> by the user on the regular search dialog. Anyone else seen
M> this effect?

Yes, all the time, and this is reasonable.

Let's say, you have a column INT in the table. In Access, you can specify
"Format" it, for example Currency. Then you can search not for 1234 but for
$1,234.

Since the data in the actual sql server table is 1234, naturally, it's much
easier to find than $1,234. In order to search for "formatted" , Access has
to actually format each row and then compare. Hence the delay.

Vadim
 
Vadim:
I always suspected that was what Access was doing. What I
didn't realise was that the VBA "findrecord" method would
default to 'search as formatted' if the user left the
search box dialog set-up that way. I guess I will try and
be more specific in the way I use Findrecord (i.e. don't
rely on the defaults).
 
Back
Top