Unanswered: VB and large Access databases - howto?!?
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 2000. Sadly one of the largest tables in the database 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!
Microsoft gives you with Office2000 and OfficeXP MSDE - which is small brother of SQL server. The base is the same as in SQL 2000. You can find it on Office CD.
Did you say 20,000 records? It's nothing for MS Access. Access becomes slow when we talk about MDB bigger than 100MB.
look at your indexes, make sure you compact it often, use ADO or DAO (I prefer ADO), but not both. And if you have an option, store it on faster server. When I had bigger MDB, I split it into smaller files (each table in separate file) - it's harder to maintain, but it helps little bit....