Solved

VB6, Large Database, Need a Good Lookup Routine

Posted on 2001-08-16
7
179 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
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.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

856 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