[RESOLVED] Loading 5000 records into a listbox

Printable View

Dec 26th, 2005, 05:56 PM
Rudie

[RESOLVED] Loading 5000 records into a listbox

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.
Dec 26th, 2005, 08:03 PM
dee-u

Re: Loading 5000 records into a listbox

Are you really sure you want to load all those 5000 records in the listbox at once?
Dec 28th, 2005, 05:42 PM
Rudie

Re: Loading 5000 records into a listbox

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.
Dec 28th, 2005, 07:55 PM
dee-u

Re: Loading 5000 records into a listbox

30 seconds for 5000 records is really slow, would you care to post the code you are using? And what database are you using?
Dec 28th, 2005, 08:04 PM
szlamany

Re: Loading 5000 records into a listbox

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.
Dec 29th, 2005, 11:42 AM
Rudie

Re: Loading 5000 records into a listbox

Ok, i try and trim down the 5000.

database is VDM access 2.0 but having problems converting to ADO at the moment
Dec 29th, 2005, 11:48 AM
Hack

Re: Loading 5000 records into a listbox

Quote:

Originally Posted by Rudie

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?
Dec 29th, 2005, 11:58 AM
Rudie

Re: Loading 5000 records into a listbox

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
Dec 29th, 2005, 12:00 PM
Hack

Re: Loading 5000 records into a listbox

Quote:

Originally Posted by 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

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.
Dec 29th, 2005, 02:03 PM
Rudie

Re: Loading 5000 records into a listbox

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.
Dec 29th, 2005, 02:10 PM
Hack

Re: Loading 5000 records into a listbox

Quote:

Originally Posted by 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.

So, did that take care of the error?
Dec 29th, 2005, 03:08 PM
Zakary

Re: Loading 5000 records into a listbox

Quote:

Originally Posted by 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.

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 :ehh:
Dec 29th, 2005, 05:11 PM
Rudie

Re: Loading 5000 records into a listbox

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.
Dec 30th, 2005, 06:32 PM
Rudie

Re: Loading 5000 records into a listbox

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.
Dec 30th, 2005, 06:34 PM
szlamany

Re: Loading 5000 records into a listbox

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)...
Dec 30th, 2005, 06:38 PM
dglienna

Re: Loading 5000 records into a listbox

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.***********
Dec 31st, 2005, 08:37 AM
Rudie

Re: Loading 5000 records into a listbox

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.
Jan 4th, 2006, 05:38 PM
Rudie

Re: Loading 5000 records into a listbox

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?

:mad:
Jan 4th, 2006, 05:41 PM
szlamany

Re: Loading 5000 records into a listbox

Look here:

http://search.msdn.microsoft.com/sea...&c=0&s=1&swc=0
Jan 4th, 2006, 06:04 PM
Rudie

Re: Loading 5000 records into a listbox

Would installing SP6 resolve this as the link above states on MS website?
Jan 4th, 2006, 06:07 PM
szlamany

Re: Loading 5000 records into a listbox

Quote:

Originally Posted by Rudie

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.
Jan 4th, 2006, 06:13 PM
Rudie

Re: Loading 5000 records into a listbox

If i sent you my database empty, can you try to connect on your PC to see whether its a PC/environmental/database issue?
Jan 4th, 2006, 06:15 PM
szlamany

Re: Loading 5000 records into a listbox

Quote:

Originally Posted by 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?

I could try... leaving in 5 minutes though - so might not be tonight...
Jan 5th, 2006, 01:06 PM
Rudie

Re: Loading 5000 records into a listbox

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

Thanks.
Jan 5th, 2006, 01:09 PM
szlamany

Re: Loading 5000 records into a listbox

I don't give out my e-mail - try to PM it to me. ZIP it up...
Jan 5th, 2006, 02:03 PM
Rudie

Re: Loading 5000 records into a listbox

heres the db...
Jan 5th, 2006, 02:05 PM
szlamany

Re: Loading 5000 records into a listbox

It didn't attach...
Jan 5th, 2006, 02:11 PM
Rudie

Re: Loading 5000 records into a listbox

here...
Jan 6th, 2006, 10:15 AM
szlamany

Re: Loading 5000 records into a listbox

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
Jan 6th, 2006, 10:49 AM
kaffenils

Re: Loading 5000 records into a listbox

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:

Code:

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

Take a good look at the red text.

All times are GMT -5. The time now is 12:43 AM.