I have an archive table that is currently about 1.9 million records big. If I try a simple search on a surname such as:
"SELECT fldSurname FROM tblArchive WHERE fldSurname LIKE 'Hague' "
a recordset is returned in the blink of an eye.
However, if I try to query with wildcards, such as :
"SELECT fldSurname FROM tblArchive WHERE fldSurname LIKE '%Hague%' "
I eventually get an error message that the server has timed out and no recordset is returned.
The field fldSurname is indexed.
What is going wrong and how do I correct it so that I can do wildcard searches swiftly?