Changing DSN or ODBC Connection at Runtime

[jtrapat1]

Background:
I have a VB6 application which uses ADO 2.1 to connect to an Access 2000 database, and subsequently uses Crystal Reports 4.6 for reporting. I need to execute paramaterized queries directly through the Crystal Report, so I have to use an ODBC connection for the Crystal Reports.

My application allows users to interact with a number of site-specific databases, each of which will be based on a template mdb and so will have identical structure (queries and tables), but will have varying data depending on what they retrieve from each database. The same set of Crystal Reports are available for any of the databases that the user has set up, but when a report is run, it must refer to the database the user is currently working with.

Problem:
When referring to an ODBC database through Crystal Reports, you cannot change the database being used only which data source is being used.
I've set up a DSN for each database but how would I go about changing these different DSNs at runtime?

Any ideas out there??

 

[emcevoy]

hello

This might help

In the Crystal Report Designer:
1. Open the report.
2. On the 'Database' menu click 'Set Location'
3. In the 'Table' box remove the Database and Owner. For example:
'pubs.dbo.Proc(byroyalty;1)' becomes 'Proc(byroyalty;1)'
4. Click 'OK' and save the report

In Microsoft Visual Basic:
1. Open the application
2. Enter the following code to change the Server and Store Procedure

'Connect to the new server
CrystalReport1.Connect = "DSN=NewServername;UID=username;PWD=password;DSQ=databasename" E. McEvoy
Crystal Reports Consultant

http://www.pacificridgesolutions.com