两件事。由于这是带有SQL Server后端的Access数据库，将其转换为存储过程可能会极大地提高速度。

第二，你真的需要返回所有这些字段吗，特别是在tabCustomers表中吗？永远不要返回比你实际要使用的字段更多，这样可以提高性能。

At first, try compacting and repairing the .mdb file.

Then, simplify your WHERE clause:

WHERE
  [drawername] Like Nz(Forms!FrmSearchCompany!SearchName, "") & "*"
  And 
  [Drawerpostcode] Like Nz(Forms!FrmSearchCompany!Searchpostcode, "") & "*"
  And 
  [drawersortcode] Like Nz(Forms!FrmSearchCompany!Searchsortcode, "") & "*"
  And 
  [draweraccountno] Like Nz(Forms!FrmSearchCompany!Searchaccount, "") & "*"
  And 
  [tpcchequenumber] Like Nz(Forms!FrmSearchCompany!Searchcheque, "") & "*"

Does it still run slowly?

EDIT

As it turned out, the question was not clear in that it is an up-sized Access Database with an SQL Server back end-and an Access Project front-end.

This sheds a different light on the whole problem.

Can you explain in more detail how this whole query is intended to be used?

If you use it to populate the RecordSource of some Form or Report, I think you will be able to refactor the whole thing like this:

• make a view on the SQL server that returns the right data
• query that view with a SQL server syntax, not with Access syntax
• let the server sort it out

How many rows are in QryAllTransactons?

If your result returns 0 rows then Access may be able to see that immediately and stop, but if it returns even a single row then it needs to pull in the entire resultset of QryAllTransactons so that it can do the join internally. That would be my first guess as to what is happening.

Your best bet it usually to do joins on SQL Server. Try creating a view that does the LEFT OUTER JOIN and query against that.

Your goal, even when Access is running on the SQL Server itself and minimizes network traffic, is to only send to Access what it absolutely needs. Otherwise a large table will still take up memory, etc.

Have you tried running each of the subqueries in the union? Usually optimizers don t spend much time trying to inspect efficiencies between union elements - each one runs on its own merits.

Given that fact, you could also put the "IF" logic into the procedural code and run each of the tests in some likely order of discovery, without significant additional overhead from more calls.

Get rid of those like operators.

In your case you don t need them. Just check if the field starts with a given value which you can achive whith something like this:

Left([field], Len(value)) = value

This method applied to your query would look like this (did some reformatting for better readability):

SELECT
  TabDrawer.DrawerName, 
  TabDrawer.DrawerSortCode, 
  TabDrawer.DrawerAccountNo, 
  TabDrawer.DrawerPostCode, 
  QryAllTransactons.TPCChequeNumber, 
  TabDrawer.DrawerRef
FROM
  TabDrawer 
  LEFT JOIN QryAllTransactons 
    ON TabDrawer.DrawerRef = QryAllTransactons.TpcDrawer
WHERE 
  (Forms!FrmSearchCompany!SearchName Is Null 
  Or Left([drawername], Len(Forms!FrmSearchCompany!SearchName)) = Forms!FrmSearchCompany!SearchName)
And
  (Forms!FrmSearchCompany.SearchPostcode Is Null 
  Or Left([Drawerpostcode], Len(Forms!FrmSearchCompany!Searchpostcode)) = Forms!FrmSearchCompany!Searchpostcode) 
And 
  (Forms!FrmSearchCompany!SearchSortCode Is Null 
  Or Left([drawersortcode], Len(Forms!FrmSearchCompany!Searchsortcode)) = Forms!FrmSearchCompany!Searchsortcode) 
And 
  (Forms!FrmSearchCompany!Searchaccount Is Null 
  Or Left([draweraccountno], Len(Forms!FrmSearchCompany!Searchaccount)) = Forms!FrmSearchCompany!Searchaccount) 
And 
  (Forms!FrmSearchCompany!Searchcheque Is Null 
  Or Left([tpcchequenumber], Len(Forms!FrmSearchCompany!Searchcheque)) = Forms!FrmSearchCompany!Searchcheque)

Note that you re comparing case sensitive. I m not totally sure if the like operator in MS-Access is case insensitive. Convert both strings to upper- or lowercase, if needed.

When you upsized did you make sure the tables were properly indexed? Indexes will speed queries tremendously if used properly (note they may also slow down inserts/updates/deletes, so choose carefully what to index)