You’ll want to change out to using the SQL 2005 OLE driver.
All the T/SQL code should work fine. At a prior company we had a VB6 application which was using a SQL 2000 database. We upgraded to SQL 2005 with no problem. We were using about 98% stored procedures.
What errors are you getting, and what T/SQL is being run?
It could be that you are trying to use some SQL objects which have been renamed or removed, or the data changed. It could also be that you are trying to use system objects within the master database which they don’t have access to by default any more.
Did you install a new version of SQL Server or an in-place upgrade? If it was a new instance the instance name may need referenced in queries. Try to set up a DSN through windows to test if you can physically connect to the database. Make sure that there is no problem with the user you are using to connect to SQL from inside the VB6 program. Open SQL Server Management Studio as this user and attempt a few queries to make sure that this is not a permissions problem.