Combo box requery

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

geribosi - 01 Nov 2004 19:58 GMT

I have a question that may have been asked numerous times, however
from the previous postings no one seems to have a plausible
explanation.
This is a performance issue that is very common:

I have a split Access database- tables and front end both sit in at
users local drive(except when theuser connects to the server to import
update files).
I have two unbound combo boxes, when I make a selection to the first
one the second's row source is being updated (it runs a query based on
the selection) my problem is it takeslonger than ususal to run the
query.

Has anyone an effective way of solving this?

Thanks

GB

Reply to this Message

Darryl Kerkeslager - 02 Nov 2004 01:39 GMT

1. What is SQL string for the second combobox's rowsource ?

2. Where are the tables for the above SQL query stored?

3. How many are records in those tables?

Darryl Kerkeslager

> This is a performance issue that is very common:
>
[quoted text clipped - 5 lines]
> the selection) my problem is it takeslonger than ususal to run the
> query.

Reply to this Message

geribosi - 02 Nov 2004 15:41 GMT

the second combo box row sourceis based on a query that genrates a
list of items based on the user selection. As I have sait thedatabse
is split and the tables reside separately from other objects.
There are more than 7k of records in the tables queried.

Thx.

> 1. What is SQL string for the second combobox's rowsource ?
>
[quoted text clipped - 13 lines]
> > the selection) my problem is it takeslonger than ususal to run the
> > query.

Reply to this Message

Darryl Kerkeslager - 03 Nov 2004 02:09 GMT

My first question was: 1. What is SQL string for the second combobox's
rowsource ?

Your answer, " the second combo box row sourceis based on a query that
genrates a
list of items", doesn't really answer the question. What, specifically, is
the wording of that query? In other words, if you built the query in the
query design view, open that query, switch to SQL view, and paste the SQL
into your response.

My second question, "Where are the tables for the above SQL query stored?"
means, are the tables stored in the front end, or the back end. You didn't
answer this either. For instance, if your combobox rowsource is "SELECT
street_id, str_name FROM su_street ORDER BY str_name", then is table
su_street in the BE? Because if it is, that could be the problem,
especially if table su_street contains 7000 records which your combobox has
to fetch from the BE every time you requery the combobox.

>There are more than 7k of records in the tables queried.

So is there more than one table in the query? Post the SQL string (the
query)(the SELECT statement).

Darryl Kerkeslager

> the second combo box row sourceis based on a query that genrates a
> list of items based on the user selection. As I have sait thedatabse
[quoted text clipped - 20 lines]
> > > the selection) my problem is it takeslonger than ususal to run the
> > > query.

Reply to this Message