Solved

VB6, Large Database, Need a Good Lookup Routine

Posted on 2001-08-16
7
177 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
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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

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…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

785 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