Also recommend a Scalar Function
http://www.access-programmers.co.uk/forums/showthread.php?t=272547 - almost last post
This Scalar function looks at the last 3 characters in a field and uses the wildcard. Different types of function can take Argument(s) and return a value or a table.
A view using the Scalar Function (and over a dozen more) is shown here. The view can be set up as a linked table.
These run very fast.
Just another option if it fits.
RIGHT(Well_Name, 3) COLLATE LATIN1_GENERAL_BIN LIKE '%[HD]%'
The BINary option can also be a speed up in T-SQL.
You didn't show an example of the wildcard search.
Having the wildcard at the end of the string, like 'abc%', would help
if that column were indexed, it allows seeking directly to the records which start with 'abc' and ignore everything else. Having the wild card at the beginning means it has to look at every row, regardless of indexing. So, the wildcard at beginning or end can make a big difference. A non-indexed field with the wildcard at the end will also be slow.
Another advantage of T-SQL (later versions) if the wild card is at the front-end is the CONTAINS function.
For a typical database, the CONTAINS search can be much faster assuming the appropriate full text search index is built on the field being searched.
In SQL Server Management Studio, right click on the field being search and choose the Full Text Search Index. Avoid doing this for evey text field and focus on the ones being searched.
Another option is to run the Access on Citrix or Remote Terminal. This way, the users use the ICA Client. The ICA is really tiny bandwidth. All process and network traffic take place on the server. My users in Utah or North Dakota have the same effictive respone as if they were on a terminal at the server. This has the advantage of letting you deploy a single Access project on the server. No Access need be installed on the user's. Access using linked tables on the same Server as the SQL Server is pretty much unlimited with virtually no ping time. It is just another option should too many users try to cram down a 10 MB network pipe.