I am currently developing an application in VB6 (not .Net) that uses data from an old database environment that has been converted to MS Access. Sadly one of the largest tables contains way over 20000 records which actually isn't one of MS Access strongest areas. In any case I'm restricted by the platform on which the application is going to be used - in other words that is Windows and a "no cost" database. That means SQL Server is not an option, nor is the Linux environment. Is there any way to increase performance with Access and VB on such a large ammount of data? I have tested various ways of tuning the database according to different tips on the net and the most recommended way to go is connections through ADO and native OLE DB, but it is still not good enough. Data reads are too slow!
Thanks in advance!
20000 records isn't that bad for Access. You need to ensure that all the proper indexes are in place: PKs, FKs, columns that get joined to or queried constantly, etc... More horsepower (RAM & CPU) on the hosting machine will help also.