Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

VB6, Large Database, Need a Good Lookup Routine

Posted on 2001-08-16
7
Medium Priority
?
184 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 300 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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.
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…
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…
Suggested Courses

610 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