Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Posts
    3

    Question Unanswered: VB and large Access databases - howto?!?

    Hi!
    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!
    Best regards
    Jonas

  2. #2
    Join Date
    Nov 2002
    Location
    Ohio
    Posts
    90
    Hi Jokre,

    These are "generic" items which might help:

    Define indexes on database fields used in searches.

    Limit searches by returning "Top N rows".

    Reduce the use of "Order by" in queries.

    Reduce the number of "Connect/Disconnect" operations.

    Good Luck,
    Bruce Baasch

  3. #3
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251
    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....


    jiri

  4. #4
    Join Date
    Jan 2003
    Posts
    3

    Thanks all!

    OK, I have done some modifications of the database according to the tips I've got here and some other things I've discovered myself. Thanks a lot, I think I'm "on track" now!
    Cheers!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •