I am working on a contact management db application project with my developer currently operating in VB 6 + MS Jet. The data being manipulated is appx 150,000 firms and 250,000 contacts at those firms.

The application has two primary functions:

1. The need to browse and search the database by firm name, zip, street, telephone, contact name, etc.

2. The need to perform selective queries and print, export, or archive a pointer file of records which met the query.

The Problem: Performance of a lookup/search item in Browse area of application.

Table vs Dynaset, Seek vs FindFirst

In order to have the query capability we are opening firm db as dynaset which loses all indexes and seek options (and SPEED) in the browse area of the application.

This is a periodic stamp and replacement of a local database including a few installations who wish to operate from CDROM. The application we are developing is replacing a DOS based app written in FoxPro.

I would be most interested in hearing from anyone who might offer suggestions on recommended solutions to achieve maximum performance.

We are open to duplicating tables, switching db engine from jet to ??? We were already looking to apply this same code to a SQL server db backend and were using jet for this initial project.

I would be most intersted in any advice, tips, tricks, and other commentary on what others have successfully done in similar situations.

I trust my first post was not too long winded.

Steve