What are and how do I make Stored Queries

Access tries its best to optimise performance in a frontend/backend design but needs some help and understanding from the developer.
The Access back-end database is not a Server, that is it doesn't do any record selection, the SQL logic is performed in the front-end. This is different to SQL Server, Oracle etc. where the server processes the SQL logic and returns only the selected results. The Access front end will make use of any indexes there are in order to reduce the amount of data traffic over the network, but if there is a Where clause on a field without an Index the front-end has to retrieve every record to check the criteria. If the tables are large this can lead to a significant delay in execution.
Examine the data source for all the controls on the form and check the where clauses for unidexed fields on large tables.

Thank you all for your responses.

I have spoken with the client, and they have allowed that as these drop downs won't change that often they can be left on the front end, and when they do change then they will have to update the users version (which I have simplified by using a batch file).

Alas, an SQL Server in this instance is out of the question - the Clients 'must have'. I had warned them that an Access Database is not the best program to use, but they insisted. As they are a small company that keeps being bought by others, they just want the ability to 'pick-up' their data and take it somewhere else without having to pay $000's for servers.

The drop downs contain things like, Contract In, Contract Out etc, but they relate to very long sentences that are exported out to produce a Mail Merge Document in word.

If you are currently operating in a 'normal' way whereby you open the form to use it and close uit when finshed, you might want to consider a different approach.

Open the form hidden as soon as the database opens.
Then anywhere you have a button/menu to 'open' the form, just make it visible, and anywhere you close the form, make it hidden again.

Updating the comboboxes is not what I was referring to.

It is the sheer number of comboboxes.
Again, what are all of these comboboxes doing?

The concern is that all 30 of these comboboxes must load all of there lists.
This may be a big contributor to the slowness.
Again, especially if they each contain lots of values.

^ so what I am trying to say is that with 30 comboboxes, each loading, for example, 100 items, this means that 3000 rows of data must be loaded.

Meaning that, perhaps it may not make a difference, performance-wise, if they are loaded from a stored query or an SQL string.

But let's see what the other experts have to add...
;-)

Jeff

Most of the combo boxes contain a list of about 6 items on average. There a two which have about 100. One of the list uses a filter query, which has a couple of linked tables so I can later use the data in another function.

peter57r - so a 'stored query' is really just building the query via the properties box and the three dots? Isn't that just another way of building a query? I don't see really how much of a difference a 'stored query' against another and attaching it to the row source manually. Looks like that suggestion was really building me up to nothing.

Well if no more tips that are out of my control I'll award points soon.

Cheers guys

A 'stored' query is what you see listed in the database window queries tab.  It has a name.

SQL strings in a recordsource or rowsource do not appear as queries in the database window.

Stored queries are faster than queries run from sql strings because they are processed in accordance with a compiled 'query plan' which is intended to optimise them.

Using the ... button was intended  to give you a built query from the outset; all you have to do is save it.  If you started from the database window, you have to do all the work yourself.  With 30 such queries I think that is worthwhile time saver apart from avoiding any errors as you rebuild them.