Solved

VB6, Large Database, Need a Good Lookup Routine

Posted on 2001-08-16
7
181 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

696 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