Native connection in Crystal Report 9 Viewer

[nasreen]

Hi All,
I am using Crystal Report 9 and VB6.

I want to display the reports using the native connection to my MS SQL 2005 dbase

And I use the following code to view the crystal report

Dim CrystApp As CRAXDRT.Application
Dim CrystRpt As CRAXDRT.Report
 
Set CrystApp = New CRAXDRT.Application
Set CrystRpt = CrystApp.OpenReport(App.Path & "\PymntRpt.rpt")
CrystRpt.Database.Tables(1).SetLogOnInfo "MyServer", "MyDb", "MyUsr", "MyPwd"

I get the following Error :
Details: IM002:[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

But when I use the same code to connect using ODBC connection it work properly

Dim CrystApp As CRAXDRT.Application
Dim CrystRpt As CRAXDRT.Report
 
Set CrystApp = New CRAXDRT.Application
Set CrystRpt = CrystApp.OpenReport(App.Path & "\PymntRpt.rpt")
CrystRpt.Database.Tables(1).SetLogOnInfo "MyODBC", "MyDb", "MyUsr", "MyPwd"

This code work with ODBC connection.

In my case, I need to connection using Native connection method

How can I solve this problem and connect to my SQL Sever using native connection method in Crystal Report 9.

With regards,
Nasreen

[jggtz]

Crystal Reports 9
To use the native driver, try :

CrystRpt.Database.Tables(1).SetLogOnInfo "MyServer", "MyServer", "MyUsr", "MyPwd"

Also:
The 'SetLogonInfo' is a deprecated method in Crystal Reports 9
You have to use the 'ConnectionProperty' instead

[nasreen]

Hi jggtz
Thanks for your replay.

Your code :
CrystRpt.Database.Tables(1).SetLogOnInfo "MyServer", "MyServer", "MyUsr", "MyPwd"
is the same that I am trying with, but there is not database name in your code. However I get error by this code also

Regarding to the use of 'ConnectionProperty' :
I have no much idea about it. Can u pls give me an example how to use this 'ConnectionProperty' modifying my codes.

With regards,
Nasreen

[jggtz]

The next is an example to send data path to a Crystal Reports' report using VB6.
In this example, the report is designed using ADO OLE DB (MS Access) as data source, then you have to modify to your data source requirements;
(in order to know these requirements , enter to Crystal Reports, select: menu / Database / SetDatasourceLocation / Current Data Source / Properties to display the properties you have to provide thru vb code )

'Declarations

Public CRApplication    As New CRAXDRT.Application
Public CRReport         As New CRAXDRT.Report
Public CRConProp        As CRAXDRT.ConnectionProperty
Public CRTabla          As CRAXDRT.DatabaseTable
Public GRpt             As String  'Contains the report's name
Public MyFolder		As String  'Contains the relative path to data and reports
Public MInt2		As Integer 'Just a counter 
Public GSelFor		As String  'Contains the RecordSelectionFormula
Public GTitleA		As String  'Contains the Company's name
Public GTitleB		As String  'Contains the Report's name
Public GTitleC		As String  'Contains the dates's range
'
'
'
Sub PrintReport()
    'here define the report to show
    Set CRReport = CRAplication.OpenReport(MyFolder & "\" & GRpt, 1)

    'here change the relative data path to all the tables defined in the rpt
    'in this example all the tables are inside the same mdb file
    'here the datasource is thru ADO OLEDB (MS Access), 
    'if it is other datasource, as SQL, the ConecctionProperties will be different
    For MInt2 = 1 To CRReport.Database.Tables.Count
        Set CRTabla = CRReporte.Database.Tables(MInt2)
        Set CRConProp = CRTabla.ConnectionProperties("Database Type")
            CRConProp.Value = "OLE DB (ADO)"
        Set CRConProp = CRTabla.ConnectionProperties("Provider")
            CRConProp.Value = "Microsoft.Jet.OLEDB.4.0"
        Set CRConProp = CRTabla.ConnectionProperties("Data Source")
            CRConProp.Value = MyFolder & "\MyDataFile.mdb"
        Set CRConProp = CRTabla.ConnectionProperties("User Id")
            CRConProp.Value = "Admin"
        Set CRConProp = CRTabla.ConnectionProperties("Database Type")
            CRConProp.Value = "Access"
        Set CRConProp = CRTabla.ConnectionProperties("Locale Identifier")
            CRConProp.Value = "1033"
        Set CRConProp = CRTabla.ConnectionProperties("OLE DB Services")
            CRConProp.Value = "-6"
    Next MInt2
    
    'Modify existing rpt Formulas 
    For MInt2 = 1 To CRReport.FormulaFields.Count
        Select Case CRReport.FormulaFields(MInt2).Name
            Case "{@a}"
                CRReport.FormulaFields(MInt2).Text = "Trim(" & Chr(39) & GTitleA & Chr(39) & ")"
            Case "{@b}"
                CRReport.FormulaFields(MInt2).Text = "Trim(" & Chr(39) & GTitleB & Chr(39) & ")"
            Case "{@c}"
                CRReport.FormulaFields(MInt2).Text = "Trim(" & Chr(39) & GTitleC & Chr(39) & ")"
        End Select
    Next MInt2
    
    'Modify record selection criteria
    If Trim(GSelFor) <> "" Then
        CRReport.RecordSelectionFormula = GSelFor
    End If
    
    'frmReport is the form that contains the CR9Viewer
    frmReport.Show vbModal   
    
    'Release resources
    Set CRTabla = Nothing
    Set CRConProp = Nothing
    Set CRReport = Nothing
    Set CRAplicacion = Nothing

And this is the code in frmReport :

Private Sub Form_Load()
    
    Me.Caption = GTitleA
    
    CRViewer1.ReportSource = CRReport
    CRViewer1.Zoom 1
    CRViewer1.ViewReport
    
End Sub