I have an accounting/order/inventory application written in Access97 with a SQL 6.5 back end. The problem is, all of the programming was done in DAO with a small set of test data. Once we went live, after data converting 100's of thousands of records from an old FoxPro DOS system, the app crawls.

I've been brushing up on ADO and Transact-SQL but I'm very new to it. I can access records quicker for reports and snapshot type queries. But what do I do when a form is bound to a giant table (100,000+ records)? Or, more likely, a dynamically created query based on that table. Is an ADO recordset updateable? Should I pass through a stored procedure to create a SQL view and link to that?

I don't have many answers but I'm capable of learning the code if I'm pointed in the right direction. Any ideas on what I should be trying to do here?

Thanks