Again, thanks to all that took the time to read the original post. After much research and experimentation I can say that I resolved all the problems I had. The solutions are documented here for posterity.
I inherited a report made using CR6 that is called from a VB6 app. The report worked fine until the database was moved to two different SQL Servers. Also, the report logic in VB6 used two temporary tables in such a way that only two users could run the report at a time. Even worse, each temp table was tied to a specific printer. Note: When I say “temporary” I refer to a normal SQL Server table that is used as a temp worksheet, not at all a #temp table.
The report employed a subreport to print out the details of the receipt and printed out two distinct copies, one occupying the top half of an 8x11 sheet, the other the bottom half.
I found out the following:
The main RPT report used data to format the overall report. The data came from the VB6 app. The sub report also used data to print out the detailed information, but it was querying the db table directly, not getting the data from the VB6 app.
The report was attempting to connect to the old (now non-existent) SQL Server. It was having a really bad day.
I could not get the report to connect to the new SQL Server(s) through code. I found some sample VB6 code, but it did not work.
I could not fully understand the following statement:
Why Tables(1)? How was this collection numbered? What was that 3 option? It is called DataTag, but this did not tell me anything. Did in indicate an instance of the record set being passed? How was it related to the objects in the RPT file?
crReport.Database.Tables(1).SetDataSource rsRpt, 3
The first task was understanding the Tables collection. It turns out that the RPT file is really a “database” of information pertaining to the report. Once the RPT file is opened the report object that is created contains many of the report’s attributes. The Tables collection lists all the tables that have been defined in the RPT using the Designer (or equivalent). When designing the report and adding data fields, one must first tell the designer which fields will be used within the report. This is an easy task. With this, we also tell the Designer the table, database and server name. Finally, so that the designer can go and fetch the data that we’ll use in our report, it connects to the DB. For this we need to provide logon credentials. If the fields that will be used within the report come from more than one table then we need to repeat the above process for each table. In the end we have a list of tables that the report will use to print (or display) the data that we want.
Once we have defined the fields that we need I speculate that the Designer builds a query that it uses to fetch the information every time the report is called and it takes the needed data from this default data source.
The Tables collection contains the names of these tables. If we want to get a peek to what is in this collection we can use a loop like the following:
This will run through the Tables collection printing the table name in the Immediate Window. The Tables collection element contains the data set that will be used to populate the report, but that data can come from numerous sources, one of them being a record set object that is passed to the report using the SetDataSource method.
'crReport is object that opened the CR9 RPT file.
For iTblCnt = 1 To crReport.Database.Tables.Count
This brought me to the “3” parameter. After getting creative with my search phrases I finally found a page that described each parameter. The 3 indicates that the data object that is passed is an ADO object… and guess what, as of CR9 this was the only option that was supported (also assuming that the info I found pertained to CR9). So basically this meant that the “3” parameter should always be 3, end of story.
Using the SetDataSource method is not as straightforward as it seems. If the report has various tables defined then we need to determine which element of the Tables collection corresponds to the table or better said data source that we want to replace. To find this out was easy. I used the loop above checking for a specific name. When I defined the table in the CR9 Designer it allowed me an alias. For example, if my table name was SalesTrans I would select that from the tables list, but I could internally name it SalesTrans01. The internal name is what is in the Name property of the Tables collection as shown in the code snippet above.
Since the table name, as defined within the report is known to me, all I had to do was check each table name element looking for the name. When I found it I knew that I was referencing the correct Tables element that corresponded to the data source that I was about to replace. Then I used the SetDataSource method for that Table element passing the ADO record set object.
Another obstacle was passing the data to the sub report. The previous code did not do this. Since the sub report uses more than one table, I used the loop structure shown before to loop through the Tables collection looking for the right table. Once found I passed the record set containing all the detail data for the sub report.
This half worked. I noticed that the bottom half of the report was blank! Somehow, I was only passing the data to the sub report in charge of displaying the upper half of the receipt. A colleague took a look at my code and the RPT file and after just a little while figured out what was gong on. To print the bottom half of the receipt a second instance of the sub report needed to be opened and the date set passed to it. This second instance is just the report name with an “ – 01” appended to the end.
I needed to open a second sub report variable specifying the sub report name plus the suffix as the report name:
After the second instance was opened I just did the loop again looking for the table name that I was going to replace and used SetDataSource against the object variable of the second sub report to pass the same record set I passed to the first instance of the sub report.
Dim crReport As New CRAXDRT.Report
Dim crReportSub As New CRAXDRT.Report
Dim crReportSub01 As New CRAXDRT.Report
'strRptFile – full file path of the RPT file
'strSubRptName – Name of the sub report as defined within the main RPT file
' This is not the file spec although the sub report name will usually be
' the name of the rpt file.
Set crReport = crApp.OpenReport(strRptFile)
'Sub report, top half of sheet.
Set crReportSub = crReport.OpenSubreport(strSubRptName)
'Sub report, bottom half of sheet.
Set crReportSub01 = crReport.OpenSubreport(strSubRptName & " - 01")
Now I understood how the Tables collection was related to the report file and to the record set data. Next was connecting to the db of my choice. After some research and failed attempts I found the following code that worked:
To determine the names that I needed to add (“Provider”, etc.) I also looped through the original ConnectionProperties collection just before doing the Delete All:
'I need to pass DB logon credentials to the report.
crReport.Database.Tables(1).ConnectionProperties.Add "Provider", "SQLOLEDB"
crReport.Database.Tables(1).ConnectionProperties.Add "Data Source", strSrv
crReport.Database.Tables(1).ConnectionProperties.Add "Initial Catalog", strDB
crReport.Database.Tables(1).ConnectionProperties.Add "User ID", strDBUsr
crReport.Database.Tables(1).ConnectionProperties.Add "Password", strDBPwd
Note that I explicitly used Tables(1). This is because the main report only had one table defined, so I knew it would always be Tables(1). The exception for the password is needed because this property is write-only and will generate an error if I try to print out its value (or access it in any way). The above code yielded 15 elements (in my particular case) of which I added the 5 that interested me as shown above in the Add statements.
Dim ocp As CRAXDRT.ConnectionProperty
For Each ocp In crReport.Database.Tables(1).ConnectionProperties
If ocp.LocalizedName = "Password" Then
Debug.Print "Password = *****"
Debug.Print ocp.LocalizedName & " = " & ocp.Value
After I added the items to the ConnectionProperties collection I was able to successfully connect to the server of my choice as indicated by the calling VB6 app.
Removing the dual user limitation was as easy as removing the old temporary tables and replacing them by real #temp tables. Now each user has his (or her) own temp tables that won’t interfere with anyone else's as the previous incarnation of temp tables did.
Once I populated the #temp I opened a record set using a query to get the pertinent data from the #temp table. I then passed the record set to the report as described above.
Once I was able to point the RPT file to the new server, know how to specify connection data to the report, pass record sets correctly and eliminate the physical temp tables everything worked as expected.
I hope this information is useful to some one at some future time. SGarV