One Report - both Access and SQL - in VB

[delu0007]

I have a VB application that uses the Crystal Automation Server to view reports. The reports have all been created based on MS Access 2000 databases, but I now wish to use them on MS SQL Server 2000 databases. The application will support both Access and SQL Server, so I need to make the changes in code.

The objects I am using are:

Dim crApp As CRPEAuto.Application
Dim crRpt As CRPEAuto.Report
Dim DBName As CRPEAuto.Database
Dim DbTables As CRPEAuto.DatabaseTables
Dim DbTable As CRPEAuto.DatabaseTable
Dim crSub As CRPEAuto.Report
Dim SubName As CRPEAuto.Database
Dim SubTables As CRPEAuto.DatabaseTables
Dim SubTable As CRPEAuto.DatabaseTable
Dim crSections As CRPEAuto.Sections
Dim crSection As CRPEAuto.section
Dim crSubreportObj As CRPEAuto.SubreportObject
Dim crReportObjects As CRPEAuto.ReportObjects
Dim ReportObject As Object

Dim DbFields As CRPEAuto.DatabaseFieldDefinitions
Dim DbField As CRPEAuto.DatabaseFieldDefinition
Dim DbSortFields As CRPEAuto.SortFields
Dim DbSortField As CRPEAuto.SortField

I have everything working with Access(sub-reports, setting selectionformulas, using sorts, ...), but I can't even view the report with SQL Server. The error I am getting is: '20533 - Unable to open database'. I think my problem is in 'DbTable.SetLogOnInfo'. I have also experimented with 'crApp.LogOnServer' -- no luck. I want to use a Trusted_Connection (no UserID and password).

Has anyone done this before? I think I just need the basics of connecting a report to a SQL Server Database in Visual Basic code, keeping in mind that the report was originally defined with an Access database and that I am using a "Trusted Connection".

Thanks.

 

[kenhamady]

You might be able to use the SetLogOnInfo command, but I am not sure if it will change database drivers for you.

You could create 2 reports, and launch the appropriate one based on code.

Or you could create a report that accepts a recordset, and get the data in the app, then pass that to the report. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[delu0007]

I kind of came to the same resolution: either write a duplicate report for each of my existing ones, or change them all to the active data driver. Neither solution is what I really wanted to do -- there are already 200+ reports written, so either solution will be time consuming. Thanks for the response anyway -- at least I know I'm not missing something obvious.

 

[balves]

It shouldn't take too long. For each report, do a Save As and then change the database driver for the second version of the report. I've done it for changing 50-60 reports from SQL Server to Oracle (identical databases) and it goes pretty quickly.

 

[kenhamady]

The LogOnServer command allows you to specify a driver at logon, and therefore use a different driver, but I don't think this is available for the Automation server. It is available in the RDC.

I think with the AS you have to use SetLogonInfo. Someone may prove me wrong here though. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/