Use of SetTableLocation for CR9

Hi all,
I have yet another question. I have been struggling to get a report which contains two identical subreports to work. CR9 was producing an internal query engine error. I determined that this was caused because the sub report pointed to one database and the code would set the database to be another.

The calling application is in VB6, Crystal Reports is v9, back end is SQL Server. The report consists of one main report and two identical subreports that print two copies of the same invoice on a letter sized sheet. The main report gets its data from the calling app using a SetDataSource statement and the sub report uses the same method for its first table and queries the DB directly to get customer data for the second defined table.

To correct the internal error I used the SetTableLocation method:

crReportSub.Database.Tables(iTblCnt).SetTableLocation sDB, "CustData", ""

This eliminated the error; however, now the invoice prints blank. Only the fixed captions, texts and images print. No data at all. This is what I am doing:

Create #temp tables and fill them with invoice data

Open two ADO recordsets from the #temp tables.

Send the first record set to the main report. This record set contains formatting information to paginate and group the invoices.

Send the second record set to the two sub reports. This record set contains the detailed data used to generate the invoice.

Use SetTableLocation as shown above to indicate to the report that the CustData table will be found in the database that is being used for this report.

Generate the report.

The above produces a blank report. If I remove the SetTableLocation statement then I get the CR internal query engine error. I believe that I am correct in using the Set Table Location statement, but possibly am missing another step, or perhaps the order of the steps as shown above is incorrect.

When specifying the database name using set table location, should I also include the server name? I tend to believe not. The sDB variable simply contains the DB name. Should I be also using the Set Table Location statement when setting a data source using the SetDataSource statement to pass a record set to the report? Does using SetTableLocation somehow override the SetDataSource statement?

Where can I learn more about Set Table Location and how to use it?

As always, thanks for any help received. Regards, SGarv

As a follow up to my initial post I'll add that I was unable to resolve the issue. I found no way to specify the same server/database for the table used within the report and the data source that I sent to it. The ultimate solution was to remove the table from within the report and add an inner join to the main data source to get the table's data in to the report along with the data source that I am sending it.

See code in post #6
Maybe the part where I set the LogonInfo could be useful to You
http://www.vbforums.com/showthread.p...m-VB6-to-CR-XI

Thanks JG. I'll test the code snippet that you referenced and post a follow up. Regards, SGarv