I have a rather large VB5 application (over 130 forms, a lot of code, and over 90 Crystal Reports) that uses an Access 97 database as the "back end". I would like to convert the back end to MS SQL Server 2000 with as little impact on the front end as possible. The VB application accesses the back end database in several manners:
* Many forms have one or more Data controls, and many controls that are bound to the Data control(s) on the form. There are probably about 2,000 bound controls in the application.
* There is one place where the database is opened and closed, and many places in code that use DAO programming to do various things with the data. Lots of code like "set rs = gdb.OpenRecordset("SELECT ...."). This code is sprinkled all over the application.
* The Crystal Reports are linked to tables and/or queries in the database. The current Crystal database driver they are using is PDBDAO.
I can migrate the data structure and data to SQL Server 2000. That is relatively easy. What I'm looking for is how to modify the VB program so it will use SQL Server instead of the Jet/MDB. I have tried creating an Access database with linked tables pointing to the SQL Server tables. This sort-of works, but (a) it is much slower, (b) there are some runtime errors which would have to be examined case-by-case to see why they don't run against SQL Server, and (c) the Crystal Reports don't work - not sure if this is just a matter of changing the database driver, or if it is more complex.
Ultimately, I think we would like to remove the MDB layer all together, and go directly to SQL Server.
(1) What is the best method to use to connect the bound controls to SQL Server? RDO, ADO, or something else? Are there any utilities that help automate this task?
(2) Is there a way to minimize the impact of going and finding all the DAO code and changing it to ADO or something else?
(3) If ADO is the "way to go", does it work with VB5? There are other issues I have with converting to VB6 (bugs in DBCOMBO32.OCX) that will give me headaches in converting the application to VB6.
(4) Are there any future things coming down the line that might make this task easier, if I wait some time (say, for VB.NET, or something else that's in the works?)
(5) What are the pitfalls I need to know about in doing this whole process?
I've been trying to find a consultant to help us with this project, but so far I haven't found anyone that has actually "done it"... Just people who have read about the process and "think" they know what to do.
I am making this question worth 300 points, and I'm looking for as much detail as possible about this process: Utilities that will help (other than the "upsizing wizard"), links or books that are good sources of reference material, etc.
If you have specific questions that will help you give me a better answer, please ask them. I can post some specific examples of code, etc. if any are required.
