Speed up combo box search 1

[rookiedev]

Good Morning all!
I am using access 97 and I am having some serious performance problems using an unbound combo box to find a customer. I was hoping someone could offer some advice as to how I could speed up the process. It is so terribly slow on some computers on the network that they can go get a cup of coffee and come back before the record is found.Here is what I've currently got set up.

On my main form called Customers the Unbound combo box named Combo40-(Lable name "Find Customer&quothas this:
Row Source:SELECT DISTINCTROW Customers.CustomerID,Customers.[Fac#]From Customers;

Sub Combo40_After_Update
Me.RecordsetClone.FindFirst " [CustomerID]=&
Me![Combo40]
Me!Bookmark=Me!RecordsetClone.Bookmark
End Sub

My database is split and in an attempt to slow down the traffic over the network I moved the Customers table to the Front End. This did not really help the situation at all. The really sad part is that the database rarely has more than 2 users at a time and I doubt I should be having such serious performance issues.

RookieDev

 

[FancyPrairie]

Is CustomerID an index (or primary key) in your table?

 

[scriverb]

First of all it looks like CustomerID is a numeric field. If that is not true please respond right away. Secondly, it is not necessary to move your Customers table to the Front-end application.

From what you are describing I am going to take a wild guess that the customers table is a very large table, correct? How many records are we talking about. Whenever I have seen this problem in the past I have found an unusually large number of records in the RowSource of the Combobox along with a lookup being done on an non-indexed field. Both situations can be fixed quite easily.

Please get back with us to describe the table record count situation and the indexing of the fields. Bob Scriver

 

[rookiedev]

scriverb-
My table has 1647 records in it. The CustomerID field is an autonumbered,primary key field, and it is indexed. The Fac# field is also indexed.

I put the Customers table back in the BE when it did not improve performance so I am back where I started from.

RookieDev

 

[FancyPrairie]

With that many records, the record should be found and displayed immediately. Have you tryed compacting and repairing the back-end? If that doesn't solve it, as a last resort, I would create a new back-end database and import the tables into it and see if that solves the problem.

 

[WildHare]

If the Customer ID is the Primary Key, there should be no reason to DISTINCTROW the SQL.

When's the last time you compacted the database? Physically disordered tables could be a bit of a drain.

I just did an combo on a 5800+ employee file keyed and indexed on SSN (on the network drive ) and it found my guy in under a second. The query did NOT use DISTINCTROW.

SELECT [Employees].[SSN], [Employees].[Employee Name] FROM [Employees]

I don't see WHY that might be a problem, but.. ??

Jim Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[scriverb]

I agree with the above postings. If that does not solve the problem make a dummy database and import the table structure only(don't want the data as it is proprietary) and make a dummy form and copy the combobox onto the form. Then email it to me. See my profile.

Bob Scriver

 

[rookiedev]

Thanks for the rapid feedback!
I compact the FE and BE daily. The system runs fine till about 11:30 and then it starts getting slower and slower. One PC in particular running Windows 98 is especially slow. The response when opened on the server is pretty quick.Just today it started mishandling events in an attached scheduling program. I'm wondering if it has some specific problems in addition to my database issues. Any suggestions?
RookieDev

 

[scriverb]

I would say yes it sounds like there are other issues that are slowing it down like network traffic, server applications that are clogging up your system later in the day, etc. But, you said that you put the table Customers in the front end and found no significant speed up in the process. That leads me to believe that there are still problems with the Application. When the data is in the front-end there should be no network issues to slow it down.

Describe the slow processing with the combo. As you are entering characters does it take a long time to go from character 1 to character 2 like it is searching down through the recordset? I have seen that as a problem. Also, are there behind the scenes processes happening like in a timer-event on a form where a bunch of batch processing queries are running later in the morning. This could slow down the process due to PC processor overload.

Think of any of these type of issues and repost. Bob Scriver

 

[WildHare]

You might also check the one PC that you mention is running especially slowly..look for the Energizer Bunny or other such nonsense running in the background, anti-virus scanners that are hooking into the keyboard, Kazaa or other file sharing stuff, FindFast from MS/Office97, etc etc etc.

You'd be amazed at the amount of crap that gets into some PCs and drags them down like this. Even exactly similar type machines can have major differences because of rogue or asinine programs that load at startup.

Jim

Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[rookiedev]

Ok!Let me ck out the PC and see what is running around in the background. The problem with the combo box comes after the address is typed in and enter is pressed. The bottom left hand of the screen shows an indicator and the words running query. Once the address is found and you click the Workorders button to add a new workorder it again is very very slow to open. There are no timer events in the program at all.
If you look at the Wizards Service Call Management it is very similar Workorders by Customer is the main form that I added the combo box to...litttle esle is the same however.
Hope this sheds some light into the dark tunnel.
I'll see what's might be bogging down the PC.
Thanks!
RookieDev

 

[scriverb]

Well I see some descrepancies here that need to be clarified. When you say

The problem with the combo box comes after the address is typed in and enter is pressed . . does this translate into typing in a CustomerID and pressing the ENTER button and ACCESS then taking a long time to refresh the form with the correct record? Is this where the slowness of the form comes in or is it after this when

Once the address is found and you click the Workorders button to add a new workorder it again is very very slow to open. . . .because if it is here that the problem exists then we are not talking about a ComboBox problem but a problem with whatever is going on behind the Workorders button.

Or, are they both a problem??

This combobox situation just should not be happening with the number of records that you have in this table.

Like I said before whip together a demo DB and send it to me for review. I will add the records necessary to make up the 1600+. Bob Scriver

 

[rookiedev]

I'm sending you a sample of my database. I don't know if you will be able to see exactly what happens on the network when the database is split but maybe it will give you some idea of the structure and code. Actually in answer to your question the combo box is the biggest problem because everyone experiences it. The workorders button is also an issue though, with the PC in question.

I appreciate you taking the time to look at it. I will forwarn you that I am a beginner and am teaching myself as I go.

I just want to say that I would have never accomplished what I have without all the help from the wonderful members of this forum.
A huge thank you to everyone who has helped me get this far.

RookieDev

 

[FancyPrairie]

Sometimes, when you're application is acting weird and compact and repair doesn't seem to solve the problem, you may want to try the undocumented /DECOMPILE command-line option.

To use it, do the following

1. Backup your database
2. Open the database with the /DECOMPILE flag
(path\msaccess.exe path\YourDatabase.mdb /DECOMPILE)
3. Save your database and quit access
4. Start Access again, load your database normally, and immediately compile your database.
5. Quit and restart Access. Your database should respond better.

 

[scriverb]

rookiedev: I have not yet received a copy of your db. Check my profile for the correct email address. Bob Scriver

 

[rookiedev]

Bob-
I will try to resend it again. I don't know what happened. In the meantime I will try FancyPrairie's suggestion.
Thanks.
RookieDev

 

[scriverb]

I got the database. I added 2000 records to your Customer table along with Company Name and customer name info. I did not decompile or compact or anything of that sort before I tested it. The combobox pick and refresh of the form was instantaneous. This is as we all expected. The design of your code is not the problem. I did notice that you had the order by set to FAC# which is another indexed field and you had the Allow Filters property turned on with a filter in the Filter property but that should have had no effect.

So we need to be looking at other things pertaining to the PC's that you are using. The memory available, processing power, the number of programs running at any one time, etc.

I do not believe it has anything to do with the table structure or the combobox code or setup. Bob Scriver

 

[rookiedev]

The PC has 128 MB of RAM and is a pentium 2 processor. There are only 6 things loading but nothing looks unusual. This machine doesn't have a sound card or anything just the very basics. There should be plenty of Ram but it is having a problem using it. When the program we have been discussing is opened, it will often display this message "Not enough memory to update display". I posed the question before and no one seemed to know what I should do.

Any suggestions?

RookieDev