Hopefully some people still remember version 8.5...
I have a number of reports which all work from a single database, this can be either Access or SQL Server, I'm currently testing on Access. I would like to use the same reports but print them for multiple databases, all of which have the same schema. To do this I'm creating UNION queries in one of the databases and I now want to change the location of the tables in the report to use these union queries instead.
For example, say the report uses table "Customer" in db1 but I have also have db2 and db3 with the same table, I'm enumerating all fields used in the report and creating a union in db1 something like:
SELECT field1, field2 FROM Customer IN 'db1.mdb'
UNION SELECT field1, field2 FROM Customer IN 'db2.mdb'
UNION SELECT field1, field2 FROM Customer IN 'db3.mdb'
Now I've tested this in Crystal Reports itself, i.e. setting the location to the new tables, and it works OK. I'm having to use an OLEDB connection otherwise the links get removed. When I try to set the location in code I just get no data on the report even though there is data available.
In code I'm going through all tables (and all tables in all sub reports) and I've tried setting the Location property to the name of the union, I've tried SetTableLocation "<path to db>", "<union name>" and I've tried both using SetLogOnInfo "<path to db>" as well.
I'm thinking perhaps converting the report to active data and plugging in an ADO recordset could be a solution but I'm not sure how feasible that is from code (i.e. converting the database driver from either pdsoledb.dll or pdbdao.dll to pdsmon.dll and creating a ttx on the fly...).
Any help much appreciated.
I have a number of reports which all work from a single database, this can be either Access or SQL Server, I'm currently testing on Access. I would like to use the same reports but print them for multiple databases, all of which have the same schema. To do this I'm creating UNION queries in one of the databases and I now want to change the location of the tables in the report to use these union queries instead.
For example, say the report uses table "Customer" in db1 but I have also have db2 and db3 with the same table, I'm enumerating all fields used in the report and creating a union in db1 something like:
SELECT field1, field2 FROM Customer IN 'db1.mdb'
UNION SELECT field1, field2 FROM Customer IN 'db2.mdb'
UNION SELECT field1, field2 FROM Customer IN 'db3.mdb'
Now I've tested this in Crystal Reports itself, i.e. setting the location to the new tables, and it works OK. I'm having to use an OLEDB connection otherwise the links get removed. When I try to set the location in code I just get no data on the report even though there is data available.
In code I'm going through all tables (and all tables in all sub reports) and I've tried setting the Location property to the name of the union, I've tried SetTableLocation "<path to db>", "<union name>" and I've tried both using SetLogOnInfo "<path to db>" as well.
I'm thinking perhaps converting the report to active data and plugging in an ADO recordset could be a solution but I'm not sure how feasible that is from code (i.e. converting the database driver from either pdsoledb.dll or pdbdao.dll to pdsmon.dll and creating a ttx on the fly...).
Any help much appreciated.
