Do...While querying

[Montez659]

Dang it! I hate writing a post twice! Stupid network.

Anyways, what I was saying before I was so rudely interrupted by my computer not cooperating was that I am trying to find a solution to a couple of problems, and not sure that a Do...While is what I am looking for, and if it is how I can accomplish it.

Essentially, I have a form that runs a query to fill a listbox after a field has lost focus. This can cause a 5 to 10 second delay as the query runs. I want to:

1. Display a message saying that the query is in process.
2. Allow other edits to the form while the query is processing.

Any way to do this? I have a hard time understanding the Do...While loop so if it does involve that, a bit more in-depth might be required. Thanks!

 

[jackd]

You might want to look at this by Allen Browne
http://allenbrowne.com/ser-19.html

It should give some ideas.

Perhaps your query could be revised if that is what takes the 5-10 second delay.

 

[Montez659]

Thanks for the link, but I must be a dunce because I fail to see how that post helps me. Please explain what I should be looking for because that all looks like it has to do with Reports.

 

[jackd]

No it isn't you. I have used Allen Browne's tips many times and I have given you a link that shows filling a listbox. You're quite right that it is dealing with reports. I should have read your post and double checked my link for relevance. Sorry for that.

Can you show the query involved?

Did you set that query as the Rowsource of the listbox?

 

[Montez659]

I will do that when I get back to work tomorrow, but let me ask this question: I know I can link to tables in a back end, but is it possible to link to queries in the back end as well (or do I even need to)?

I know that network slowness is a constant problem and I was thinking that if I can filter some of those 5000 records down (probably to 1000) that would at the least mean less traffic. I currently query the records for any from the last 9 months as it is.

Even if the query is stored in the front end, is it still retrieving all 5000 records to look through and filter by date, or does the query execute in the back end retrieving only the relevant records for the front end?

Hope that makes sense.

 

[Norie]

Is the table you are querying indexed?

I was recently working with a table that had every postal code in Canada, I think there were about 750,000+ in all.

It took ages even to open the table.

Anyway, the user wanted to use this table for validation.

(I know there's probably some formula/expression whatever that can handle that but that's what they wanted...)

Whatever I tried didn't work, but then I looked at the table design and nothing was indexed.

I created one index on Postal Code, City and Province.

It's pretty quick now validating using a simple DLookUp, though I know that's probably not the most efficent method.

Oh, and I added a postal code finder form with a listbox populated from this table and filtered by partial postal code.

That's pretty quick too.

No idea if that's relevant but that simple change making so much difference was a surprise to me.

 

[Montez659]

You know Norie, that may be a big problem! I can't believe I completely missed that. That may be a big cause of my whole db running slow.

 

[Norie]

Well that table was pretty big and I had started to contemplate splitting it out in some way.

I was also considering using a list of provinces, then towns, then postcodes in a cascading setup.

Even filtering by town province and town you end up with 160+ cities with more than 1000 codes.

 

[Montez659]

Jackd, here are the queries. The first one is a general query, which limits the table to the past 9 months. The second query then searches for the relevant info:

SELECT Tasks.ID, Tasks.Title, Tasks.[Start Date], [First Name] & " " & [Last Name] AS fullName, Tasks.[Tax ID], Tasks.Description
FROM Contacts RIGHT JOIN Tasks ON Contacts.ID = Tasks.[Assigned To]
WHERE (((Tasks.[Start Date])>Date()-270));

SELECT qryFindMatchesGeneral.ID, qryFindMatchesGeneral.Title, qryFindMatchesGeneral.[Start Date], qryFindMatchesGeneral.fullName
FROM qryFindMatchesGeneral
WHERE (((qryFindMatchesGeneral.[Tax ID]) Like [Forms]![frmRequests]![reqTax])) OR (((qryFindMatchesGeneral.Description) Like "*" & [Forms]![frmRequests]![reqTax] & "*"));

The rowsource of the listbox is set to that second query.

I did check the indexing of the table and found that there were several fields that were indexed that didn't need to be. I know that over-indexing can have the reverse effect so I removed some of those indexes and at the moment, it seems to be running a bit faster. However, that might be because it is 8:00am and netowrk traffic is low. I will recheck as the day wears on to see how it holds up. I am thinking that it really is a network traffic type of issue.

 

[jackd]

Good Morning.

Over indexing can be a issue especially for updates where unused indexes still need to be updated.

It's possible that your issue is network related. If you have a slow network etc that could be a fundamental issue. I even saw a post recently where the network was an issue, but also response time was related to how deeply nested a file or folder was on a particular server.

My guess is that your second query is doing a Like * & some string & *. This will not use indexes and will require a read of individual records. I don't see any simple way around it unless you had a table or list of keywords or phrases that could be searched.
Then you could build such a table and do a pass through your data to create another table of those records that contained which phrase.

Perhaps you could follow up with network people to see if there is any known issue or condition that may account for some of the speed issues you are experiencing.


Here is a link to some performance facts -- don't know how relevant it may be but may be worth a look.
http://www.granite.ab.ca/access/performancefaq.htm

Good luck.