Change database type at runtime

[Former Member]

Hi,

Currently updating a VB6 application that uses CR XI R2.  The application itself can hook on to either a MDB or SQL SERVER backend, dependent on client needs.

At present, we have a number of reports that have been created against a jet MDB. Is it possible for me to simply point these reports at a SQL SERVER database at run time and generate the report, or do I need to maintain two copies of the reports (one created against each database type)?

Code used to generate against an MDB file is...

For Each CrystalTable In Rpt.Database.Tables

        CrystalTable.Location = <Full path to DB>

        CrystalTable.SetLogOnInfo "", "", "", <DB Password>

Next CrystalTable

I've tried the following two methods without success...  (Note: Values in <> are sample only - Code has proper values there)

1) Result: Cannot start your application.  The workgroup information file is missing or opened exclusively by another user

For Each CrystalTable In Rpt.Database.Tables

     CrystalTable.SetLogOnInfo <Server name>, <Database name>, <User name>, <DB Password>

Next CrystalTable

2) Result:  Logon Failed:  No database was specified

For Each CrystalTable In Rpt.Database.Tables

        CrystalTable.ConnectionProperties.DeleteAll

        CrystalTable.ConnectionProperties.add "Provider", "SQLOLEDB"

        CrystalTable.ConnectionProperties.add "Data Source", <Server name>

        CrystalTable.ConnectionProperties.add "Initial Catalog", <Database name>

        CrystalTable.ConnectionProperties.add "User Id", <User name>

        CrystalTable.ConnectionProperties.add "Password", <DB Password>

Next CrystalTable

Any help would be greatly appreciated.

Thanks,

    Anthony

[Former Member · Accepted solution]

After a bit more bashing, this is now up and running.  Option 2) above was close, but needed DLL name and only to be applied to first table.

Rpt.Database.Tables(1).DllName = "crdb_ado.dll"

Rpt.Database.Tables(1).ConnectionProperties.DeleteAll

Rpt.Database.Tables(1).ConnectionProperties.add "Provider", "SQLOLEDB"

Rpt.Database.Tables(1).ConnectionProperties.add "Data Source", <Server name>

Rpt.Database.Tables(1).ConnectionProperties.add "Initial Catalog", <Database name>

Rpt.Database.Tables(1).ConnectionProperties.add "User Id", <User name>

Rpt.Database.Tables(1).ConnectionProperties.add "Password", <DB Password>