turnerrob
asked on
VB6, Large Database, Need a Good Lookup Routine
I have a large (100,000),,Access2000 Database, which contains the following Fields
Code,Title,Publisher,Compo
-------------------
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.
Code,Title,Publisher,Compo
-------------------
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
I agree, looking at the whole 100,000 records is not practical.... Seems using Like is the answer.
Regards Turnerrob
Regards Turnerrob
ASKER
I agree, looking at the whole 100,000 records is not practical.... Seems using Like is the answer.
Regards Turnerrob
Regards Turnerrob
ASKER
Thanks for your advice on this question.
All comments greatly appreciated.
Turnerrob
All comments greatly appreciated.
Turnerrob
You're welcome! :)
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