Solved

VB6, Large Database, Need a Good Lookup Routine

Posted on 2001-08-16
7
180 Views
Last Modified: 2008-03-04
I have a large (100,000),,Access2000 Database, which contains the following Fields

Code,Title,Publisher,Composer
-------------------
I have the file open using ado.

 cnn.Provider = "Microsoft.Jet.OLEDB.4.0;"
    cnn.Open Trim(App.Path) & "\accesswol.MDB"
    Set rs = New ADODB.Recordset
rs.index="title"
-------------------

I need to give the user a good method of Looking up The Title in the Database

I have tried a Combo Box. But it takes to long to load the items..  Additem  etc.
If this is the way of doing it, how do i speed it up??
**********************

I envisage (maybe),,having the following procedure

1. Having a Text Box, which the user willl enter the    lookup, and having a list box progressively displaying the titles., starting with the characters keyed in.
If i do this.

A. Do i use Select Code,Title from Accesswol WHERE ..., on the enter of each character.

B. Do a SEEK and the DO while   .....,on entering each character.

Or... is there a better way!!!!
-------------------------------
2. is there a way of having a Slider, which when moves, displays the Code,Title etc in a (?list box),progressively down or up the database, as the slider is moved.
-------------------------------

                       
Would appreciate any help in this area.


0
Comment
Question by:turnerrob
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 22

Accepted Solution

by:
rspahitz earned 75 total points
ID: 6394997
One way to speed up a database is to assign an index to a lookup field.  When you do this, the search for any specific piece is much faster, even for 100,000 records.

Once you've done that, doing a "select * from table where field like 'a%'" will be rather quick, and with typical caching mechanisms, doing the next "select * from table where field like 'ab%'" should be even quicker.

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6395019
The bigger question is, is it really relevant to display 100,000 rows to the user?  Do you honestly expect them to scroll through to find the one they are looking for?  That is even if you do get it into a combo, list, grid, whatever, in a speedy manner.

Get the user to narrow the data down by entering the first few letters and save it in a string variable called Search

Than do a
Select Top 100 Title,ISBN, etc.
From Table1
Where Title Like Search & "%"

Like is not very fast, but if you index like rspahitz suggested, it will be faster enough for 100 rows.

Just some thoughts,
Anthony
0
 
LVL 3

Expert Comment

by:Bahnass
ID: 6395021
Only one Idea for speed

Use Do events inside text1_change()

Use a static boolean variable to check recursion


I mean if user type a   then b
I don't need to complete 1st search as I have a new one
I need just to exit loop


Hope that helps

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:turnerrob
ID: 6395048
I agree, looking at the whole 100,000 records is not practical.... Seems using    Like  is the answer.

Regards  Turnerrob
0
 

Author Comment

by:turnerrob
ID: 6395049
I agree, looking at the whole 100,000 records is not practical.... Seems using    Like  is the answer.

Regards  Turnerrob
0
 

Author Comment

by:turnerrob
ID: 6395085
Thanks for your advice on this question.

All comments greatly appreciated.


Turnerrob
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 6395165
You're welcome! :)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

697 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question