[RESOLVED] Loading 5000 records into a listbox

[Rudie]

Hi,

I have a customer database that has just over 5000 records. To load an initial worklist will all the records, it takes 30 seconds. I have read through posts that say to hide the listbox when loading etc, but it makes minimal difference. Before I change the way the data is read (use ADO instead of recordsets), is there anything else I can try?

Thanks.

[dee-u]

Are you really sure you want to load all those 5000 records in the listbox at once?

[Rudie]

Hi,

Not all the time, but if I want to see all the customers it will display 5000. Also, whether I display the ones in credit or debit, it takes the same amount of time.

Thanks.

[dee-u]

30 seconds for 5000 records is really slow, would you care to post the code you are using? And what database are you using?

[szlamany]

My suggestion, as I am sure Dee-u was leading towards, is to never load 5000 records into any control.

That is simply too many.

No user is ever going to scroll through 5000 of anything.

It would be an easier experience to have an A to Z list - some kind of drill down - tree view maybe - whatever you can imagine.

We personally "halt" filling any and all textboxes at 251 entries and flash a message that the user is being too vague. Entering SMITH for a lookup name in a database with 20000 rows and getting 300 SMITH's in a drop-down list is not a appropriate.

[Rudie]

Ok, i try and trim down the 5000.

database is VDM access 2.0 but having problems converting to ADO at the moment

[Hack]

Ok, i try and trim down the 5000.

database is VDM access 2.0 but having problems converting to ADO at the moment

What kind(s) of problems?

[Rudie]

I have always used ms access/vdm on small data like less than 100 records. but nw i been told to use ADO, but do i have to modify the database? or is it just a code change when to database is openned and accessed or written to? Beacon sent me a doc on how to use ADO but i keep getting the message:

Runtime Error '-2147467259 (80004005)':
Couldn't find installable ISAM

[Hack]

I have always used ms access/vdm on small data like less than 100 records. but nw i been told to use ADO, but do i have to modify the database? or is it just a code change when to database is openned and accessed or written to? Beacon sent me a doc on how to use ADO but i keep getting the message:

Runtime Error '-2147467259 (80004005)':
Couldn't find installable ISAM

Using ADO is just a coding change. No change to the database is necessary.

As far as your error goes, take a look at this and this.

[Rudie]

For the first link, I cant find the option its talking about "Data Access", I have Office 2000 installed as well. I have found and renamed the files.

[Hack]

For the first link, I cant find the option its talking about "Data Access", I have Office 2000 installed as well. I have found and renamed the files.

So, did that take care of the error?

[Zakary]

Hi,

I have a customer database that has just over 5000 records. To load an initial worklist will all the records, it takes 30 seconds. I have read through posts that say to hide the listbox when loading etc, but it makes minimal difference. Before I change the way the data is read (use ADO instead of recordsets), is there anything else I can try?

Thanks.

Is the Base query of your ComboBox is base on more than one table ? Did you sort it ? Sometime sort take big time if there no Index

[Rudie]

I have not rectified the problem as I cannot find the "Data Access" option within the Office installation CD or within Add / Remove programs in the control panel.

I am sorting the listbox.

[Rudie]

Ok, now im proper confused.

I downloaded this example

http://pages.cpsc.ucalgary.ca/~saul/...al3/index.html

and ran it, it works using ADO fine. I changed the openconnection string to point to my database and left everything else the same, but got the following error:

Couldnt Find installable ISAM

Now if the original code works, why doesnt it work using my DB, surely this means that theres something wrong with my database?

Thanks.

[szlamany]

That sounds like an MDAC problem...

What version/type OS is the client and also the server??

You can arrive at that problem by upgrading a WINDOWS 2000 server to 2003 without putting MDAC 2.8 on it first (I think I recall that scenario)...

[dglienna]

Check out a link in randem's signature about his Database Updater. You can find many of his posts in Application Deployment, or try his site: www.***********

[Rudie]

I am running VB Professional V6.0 on Windows XP. Its a standalone laptop so no real server apart from local database using MS Access.

Please let me know if you need more information.

[Rudie]

This is also the case with the sample program that comes with MSDN called "datatree". Using the BIBLIO.MDB, it works fine, but using the same code, I replace the open connect string with my database and get "Couldnt Find Installable ISAM"

Its getting me down now, whats wrong with my database?

[szlamany]

Look here:

http://search.msdn.microsoft.com/sea...&c=0&s=1&swc=0

[Rudie]

Would installing SP6 resolve this as the link above states on MS website?

[szlamany]

Would installing SP6 resolve this as the link above states on MS website?

I could not guarantee that for you - as I've never had this particular situation.

[Rudie]

If i sent you my database empty, can you try to connect on your PC to see whether its a PC/environmental/database issue?

[szlamany]

If i sent you my database empty, can you try to connect on your PC to see whether its a PC/environmental/database issue?

I could try... leaving in 5 minutes though - so might not be tonight...

[Rudie]

Can I have ur email and I will post the database?

Thanks.

[szlamany]

I don't give out my e-mail - try to PM it to me. ZIP it up...

[Rudie]

heres the db...

[szlamany]

It didn't attach...

[Rudie]

here...

[szlamany]

Rudie - I'm not an ACCESS expert - so I got some others involved...

Please see this thread - and maybe you should continue posting within that thread if you have further questions...

http://www.vbforums.com/showthread.php?t=379950

BTW - MDAC should be re-installed - that's a general consensus. Also see these other threads about ACCESS and MDAC:

http://www.vbforums.com/showthread.p...ght=win98+mdac

http://www.vbforums.com/showthread.p...ght=win98+mdac

[kaffenils]

What connection string do you use? I got the ISAM error at first, but then I checked www.connectionstrings.com for the correct parameters and got it to work when I used this string:

"Provider=Microsoft.Jet.OLEDB.4.0;data Source=cleanbackup.mdb;Jet OLEDB:Database Password=bs0911;"

Take a good look at the red text.