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!

 

[Montez659]

The fact that the back end is nested so deep in folders is a problem. I am not in the IT department, but have built this database for my department. However, I feel like I could go and talk with some of the IT folks and find a better place for the back end ot reside. I think it is something like 6 folders deep right now, but we are talking about a 650GB drive, so who knows how long it takes to "find" the file.

This will not use indexes and will require a read of individual records

I was under the impression from the tutorial on searching from datapig technologies that even using the "Like" criteria that it still uses the index. But I am not real knowledgable on how indexing works so I might have just misundertood.

Anyways, I appreciate you taking a look at this. I will take a look at that link you gave me as well and see if there is anything I can implement.

 

[Norie]

Montez

I actually only had one index which included the 3 fields I mentioned.

 

[jackd]

My view is that because the LIKE you have has an * before and after a string, you will not get any advantage from an index.
If the LIKE was on the pattern partial string & *, then an index could/would be used.

Also, in the link I gave it talks about putting the database/file at the root ...

Place backend MDB on the root of the network share rather than several folders down.

The problem is likely related to server security as each directory you navigate must be checked against the domain security system. This may be particularly acute in combo boxes and subreports when using a FE/BE system as I've noticed these appear to be poorly optimized.

Alternatively I'd suggest having the network people setup a share right on the directory of your BE for your use. Instead of using \\Server\Dir1\Subdir2\Subdir3\subdir4\subdir5\backend.mdb you'd use \\server\Sharesubdir5.

They can can append a $ to the end of the share name to make it a hidden share so as to not confuse people. You would also need to use a $ at the end of the share name as well. As in \\server\Sharesubdir5$.

I'd read a credible posting a number of years ago indicating that someone who was working on a dialup networking analyzed the packets and realized this was a big problem. Novell report the same problem in MS access database run from NetWare server excessively slow. However this could have been fixed in newer Novell OSs as well as various service packs.

There was a posting the same day I wrote the above paragraph indicating this is still a problem. Access 2000/Windows 2000-Slow Performance....kind of solved

PB stated that shortening the path from 75 characters to 31 characters and removing four directory levels changed "from 50 seconds to load for the first time and was accelerated to unbelievable about 15 seconds." "About 3 1/2s instead of 15s is a real progress." They also shortened the name of the back end.

Slower performance in Access-based or Jet database-based programs after you upgrade from Windows NT 4.0 to Windows 2000 or to Windows XP - 891176

 

[Montez659]

Great info Jack, thanks! I will definitely look into talking with IT - the security checks taking time makes a lot of sense.

Norie, I have a question on indexing, mainly that you said that you have 3 separate fields as part of one index. I used the Index button to look at the indexes and what I see is five different fields with five different index names. Is it better to combine all of these under one index name and what is the difference if they use five names or one name?

Or better yet, instead of answering, you can just point me to some good reading on the topic so I won't make you regurgitate info that I should have looked at already.