Linking Crystal Report to VB6 dynamic Recordset - HELP!!!

I have an application which is essentially a front end to a MS Access 2000 mdb database. I have been using Crystal Reports 8 to generate the reports that Access was formerly displaying. This is the first project I've used Crystal Reports with, so I'm quite new to it. I've gotten my simple reports in place with relative ease, but now I'm trying to add a more complex report. it is supposed to take user input and show a company name, chemicals they have on site, and precautionary measures for dealing with those chemicals. All this data is stored in separate, but related tables. The report uses several subreports to display some of the chemical properties.

I have the report itself created in Crystal Reports, however the data is coming from an ADO Recordset based on a SQL query. This query was pretty much copied and pasted from the original Access app, which was developed by someone else. When I finished with the report, I discovered that I needed to change the SQL query that creates the Recordset in order to properly format the report. Nevertheless, the report currently displays all the data I need, it just basically needs a few touchups.

So my real problem comes into play when I try to view the report in my Visual Basic 6 application. I am using the Crystal Reports Viewer control in VB6 on Windows 2000 to view the report. The problem is I can't figure out how to change the Data Source dynamically in the VB app.

The way I see it, there are really two ways to do this:

1.) Modify the SQL query that establishes the Recordset in the RPT file, then use a parameter field and selection formula, and pass CR the CompanyID of the record I am trying to retrieve. Nearest I can tell, that code in VB would go something like this:

***********************************************************
Option Explicit
Private Sub Form_Load()
      Dim crApp As New CRAXDRT.Application
      Dim crRep As CRAXDRT.Report
      Dim Param1 As Long
      
      Param1 = frmMain.txtCompanyID.Text
      Set crRep = crApp.OpenReport(App.Path & "\rpt_Chemical.rpt")
      
      crRep.ParameterFields.Item(1).AddCurrentValue Param1
      
      rptViewer.ReportSource = crRep
      rptViewer.ViewReport
      
      Set crRep = Nothing
      Set crApp = Nothing
End Sub
***********************************************************

The problem here is that I don't know if it's possible to change the data source for an existing RPT at design time, and if it is I certainly don't know how to do it. Also, my testing with this plan so far has indicated that I may have a problem getting it to display the right data. When I tested it with a CompanyID other than the one I first designed the report using, it first displayed the data for the original CompanyID (not my new one), and when I hit refresh it prompted me for a value instead of using the parameter I fed it in the code.

2.) My second, and I suppose preferred option would be to set the record source of the report at run-time. I am currently trying to accomplish this by removing the selection formula from my report, and running the following VB code:

***********************************************************
Option Explicit
Private Sub Form_Load()
      Dim crApp As New CRAXDRT.Application
      Dim crRep As CRAXDRT.Report
      Dim rs As ADODB.Recordset
      Dim Con As New ADODB.Connection
      Dim ConStr, SQLStmt As String
      
      SQLStmt = "SELECT DISTINCT Company.CompanyID, Company.Name, Company.Address + ' ' + Company.Address2 AS Address,Company.City + ', ' + Company.State + ' ' + Company.Zip AS CityStateZip, Company.Phone, Company.ResponsiblePersonName, Company.ResponsiblePersonTitle, ERG2000.MATERIAL, ERG2000.UN_NUMBER, ERG2000.GUIDE_PAGE, Company.EHSOS, ChemTable.MaxQtyOnSite, ChemTable.Location," & _
          "ChemTable.CASNo, ChemTable.ProductName, ChemTable.AvgQtyOnSite, ChemTable.MonthsMaxQtyOnSite, ChemTable.StorageMethod, ChemTable.Priority, Company.EncSuit, SystemVars.FireDept, ChemTable.Report FROM SystemVars, (Company INNER JOIN ChemTable ON Company.CompanyID=ChemTable.CompanyID) INNER JOIN (ERG2000 INNER JOIN GP2000 ON ERG2000.GUIDE_PAGE=GP2000.GUIDE_NO) ON (ChemTable.Name=ERG2000.MATERIAL) AND (ChemTable.ChemNo=ERG2000.UN_NUMBER)" & _
          "Where (((ChemTable.Report)=True)) AND Company.CompanyID=" & frmMain.txtCompanyID.Text
      
      ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db.mdb;"
      Con.ConnectionString = ConStr
      Con.Open
      
      Set rs = Con.Execute(SQLStmt)
      
      Set crRep = crApp.OpenReport(App.Path & "\rpt_Chemical.rpt")
      
      crRep.Database.SetDataSource rs
      
      rptViewer.ReportSource = crRep
      rptViewer.ViewReport
      
      rs.Close
      Set rs = Nothing
      Con.Close
      Set Con = Nothing
      Set crRep = Nothing
      Set crApp = Nothing
End Sub
***********************************************************

Buy when I try that, I get two errors. The first says "Unable to access the first record!", and the second says "SQL server error." I know the Recordset being returned by my SQL statement is OK, because I had it msgbox some of the values, and they where all correct. And the fact that it is saying SQL server error is weird in itself, because I'm specifying the Provider in my connection string as Microsoft.Jet.OLEDB.4.0; no reference to a SQL provider anywhere.

I'm sure what I'm trying to do is a fairly common task for regular CR developers, but I just can't figure it out. Does my VB code look OK? Is there something more I should be doing in Crystal? Would either or both of these plans lead to problems with my subreport data? If so, is there a better way to go about this all together?

I've been fiddling with this thing for six hours now, and I've gotten nowhere, so I'm hoping someone out there can offer some advice.

P.S. - In case it is helpful here are the references and components for my VB project:

References - Visual Basic for Applications, Visual Basic runtime objects and procedures, Visual Basic objects and procedures, OLE Automation, Microsoft DAO 3.6 Object Library, Microsoft Data Report Designer 6.0 (SP4), Microsoft Data Environment Instance 1.0 (SP4), Microsoft ActiveX Data Objects 2.5 Library, Microsoft Data Binding Collection VB6.0 (SP4), Crystal Report View Control, Crystal Report 8 ActiveX Designer Run Time Library

Components - Crystal Report Viewer Control, Microsoft Common Dialog Control 6.0 (SP3), Microsoft Data Bound Grid Control 5.0 (SP3), Microsoft Data Bound List Controls 6.0, Microsoft FlexGrid Control 6.0, Microsoft Windows Common Controls 6.0, Microsoft Windows Common Controls-3-6.0 (SP4), Registration Creator ActiveX v3.4, ZTray System Tray Control.

Thank you SO much for any help you can provide me!