asked on

Upgrade Crystal Reports from Access to SQL Server and open it from VB6

I'm busy upgrading my Crystal reports 8 from MS Access database to SQL Server.
I open the report, go to Database on the menu and then set location and update the location for each report.

Then I preview it in Crystal Reports and it display perfectly with all the data.

When I open the report from Visual Basic 6 using a query it display it perfectly if there is no data in the report.
If use different criteria that return data it just does not get finished loading and freezes everything up.

Any ideas?

Brook Braswell

Are you using parameters on your report?
How does your code look to open the modified report ?

koossa

ASKER

Hi

Here is my code

  Set CystalApp = New CRAXDRT.Application
  Set Report = CystalApp.OpenReport(App.Path & "\repa\" & sReportFileName)
  
  'try this loop structure instead
  For n = 1 To Report.Database.Tables.Count
    Report.Database.Tables(n).SetLogOnInfo ".\SQLEXPRESS", PlaasNaam, "dbo", ""
    Report.Database.Tables(n).Location = PlaasNaam & ".dbo." & Report.Database.Tables(n).Name
  Next n
  
  For Each Section In Report.Sections
    For Each RObject In Section.ReportObjects
      If RObject.Kind = crSubreportObject Then
        Set SubReportObj = RObject
        Set SubReport = SubReportObj.OpenSubreport
        For i = 1 To SubReport.Database.Tables.Count
          SubReport.DiscardSavedData
          SubReport.VerifyOnEveryPrint = True
          SubReport.Database.Tables(i).SetLogOnInfo ".\SQLEXPRESS", PlaasNaam, "dbo", ""
          SubReport.Database.Tables(i).Location = PlaasNaam & ".dbo." & SubReport.Database.Tables(i).Name
        Next i
      End If
    Next
  Next
  Call GetSortingVariables(Report, sSortBy, lTableNum, lFieldNum, SortDirection)
  If lTableNum <> -1 And lFieldNum <> -1 Then
    Set DatabasieFieldForSorting = Report.Database.Tables.Item(lTableNum).Fields.Item(lFieldNum)
    Report.RecordSortFields.Item(1).Field = DatabasieFieldForSorting
    Report.RecordSortFields.Item(1).SortDirection = SortDirection
  End If
  If sQuery <> "" Then
    Report.RecordSelectionFormula = sQuery
  End If
  CRViewer1.ReportSource = Report
  CRViewer1.ViewReport
  CRViewer1.Zoom 120
  Call ShowRelativeToFormMain(Me)   '.Show vbModal, frmMain

Open in new window

Brook Braswell

I see what you are doing and wonder if it would be easier for you to have your report data and connection saved..the only pass a parameter to your report or only the query to the report.
The connection should be saved in the report when you created it.
Why attempt to reset the connection ?

Brook Braswell

Sorry for the previous comment....
After re-thinking this I came up with only about a dozen reasons that this would be necessary.

1. Double check your query.
2. you may want to put your connection string in the report in the ConnectBufferString...

Public Sub SetReportDatabase(ByRef RPT As CRAXDRT.Report)
           Dim t As Integer
           Dim srT As Integer
10         iErr = 0
30         For t = 1 To RPT.Database.Tables.count
40            RPT.Database.Tables(t).ConnectBufferString = "DSN=" & gDBNAME & ";;UseDSNProperties=0"
50         Next
           SetSubReportDatabase RPT

End Sub

Public Sub SetSubReportDatabase(ByRef RPT As CRAXDRT.Report)
           Dim t As Integer
           Dim srT As Integer

           Dim objSect As CRAXDRT.Section
           Dim objRO As Object
           Dim objSub As CRAXDRT.Report
10         iErr = 0
30         For Each objSect In RPT.Sections
40             For Each objRO In objSect.ReportObjects
50                 If objRO.Kind = CRObjectKind.crSubreportObject Then
60                    Set objSub = RPT.OpenSubreport(objRO.SubreportName)
70                    If Not (objSub Is Nothing) Then
80                       For srT = 1 To objSub.Database.Tables.count
90                           With objSub.Database.Tables.Item(srT)
100                             .ConnectBufferString = "Your database connection String"
110                          End With
120                      Next
130                   End If
140                   objSub.DiscardSavedData
150                   SetSubReportDatabase objSub
160                End If
170            Next
180        Next

End Sub

Open in new window

koossa

ASKER

It highlights the .ConnectBufferString =

'Can't assign read-only property'

Brook Braswell

In some cases this will happen, I usually trap for errors and skip when that error occurs

Public Sub SetSubReportDatabase(ByRef RPT As CRAXDRT.Report)
           Dim iErr As Integer
           Dim t As Integer
           Dim srT As Integer

           Dim objSect As CRAXDRT.Section
           Dim objRO As Object
           Dim objSub As CRAXDRT.Report
10         iErr = 0
20         On Error GoTo PROC_ERR
30         For Each objSect In RPT.Sections
40             For Each objRO In objSect.ReportObjects
50                 If objRO.Kind = CRObjectKind.crSubreportObject Then
60                    Set objSub = RPT.OpenSubreport(objRO.SubreportName)
70                    If Not (objSub Is Nothing) Then
80                       For srT = 1 To objSub.Database.Tables.count
90                           With objSub.Database.Tables.Item(srT)
100                             .ConnectBufferString = "Your Connection"
110                          End With
120                      Next
130                   End If
140                   objSub.DiscardSavedData
150                   SetSubReportDatabase objSub
160                End If
170            Next
180        Next
PROC_EXIT:
190        Exit Sub
PROC_ERR:
200        If InStr(1, UCase(ERR.Description), "READ-ONLY", vbTextCompare) > 0 Then Exit Sub
210        If iErr > 3 Then
220           ' LOG YOUR ERRORS HERE
              ' THIS IS HOW I DO MINE
              LogTheError Erl(), ERR, "SetSubReportDatabase [ Name = " & RPT.ReportTitle & " ]", "Calling Proc" ' if in a form write me.name
230           Resume PROC_EXIT
240        Else
250           iErr = iErr + 1
260           Resume
270        End If
           
End Sub


Public Sub LogTheError(ByVal LineNo As Single, ByVal ERR As ErrObject, ByVal Proc As String, ByVal M As String, Optional BroadCast As Boolean = True)
           Dim EM As String
           Dim sLINENO As String
           Dim eUserName As String
10         If ERR.Number = 3709 Or (ERR.Number < 0 And InStr(1, UCase(ERR.Description), "CONNECTION WAS TERMINATED") > 0) Then
20            MsgBox "Connection to the Server has been lost" 
              ' reconnect if you wish...I will just leave
50            End ' We should just stop Everything...
60            Exit Sub
70         End If
80         sLINENO = Trim(Str(LineNo))
90         sLINENO = Space(7 - Len(sLINENO)) & sLINENO
100        EM = "Error on Line " & sLINENO & " in Proc " & Proc & " of " & M & " # " & ERR.Number & " (" & ERR.Description & ") "
           ' I have a flag for testmode set when running in the IDE
110        If Not TestMode Then
120           If BroadCast Then
130              SendingMessage = True
140              MsgBox EM
150              SendingMessage = False
160           End If
170        Else
180           SendingMessage = True
190           If MsgBox(EM & vbCrLf & "DO YOU WISH TO STOP AND DEBUG ?", vbCritical + vbYesNo, "Application is Broken Here !!!") = vbYes Then
200              Stop
210              Exit Sub
220           End If
230           SendingMessage = False
240        End If
250        If Len(gUserName) > 30 Then
260           eUserName = Mid(gUserName, 1, 25)
270        Else
280           eUserName = Trim(gUserName) & Space(25 - Len(gUserName))
290        End If
310        Open App.Path & "\AppError.log" For Append As #1
           ' gAppRevision is a Global ver for the application version
320        Print #1, "Version " & gAppRevision & " At " & Format(Now, "MM/DD/YY hh:mm") & " " & EM
330        If ERR.Number < 0 Then
              ' in the event the error may be from SQL, show the last query
340           Print #1, "SQL = [" & SQL & "]"
350        End If
360        Close #1
      
End Sub

Open in new window

koossa

ASKER

But the ConnectBufferString property is a read only property, so you cannot assign a value to it?

Brook Braswell

it is not always a READ-ONLY property.
In your openreport...try openmethod 1 ( view )

Set Report = CystalApp.OpenReport(App.Path & "\repa\" & sReportFileName, 1)

Open in new window

koossa

ASKER

Ok, but the problem is I cannot run it in Visual basic, it pops up with a messagbox "Compile error : Can't assign to read-only property"

Brook Braswell

How do you have your options set for error trapping?
Should be Brak on Unhandled Errors
and compile on demand checked...

Brook Braswell

I never thought to ask... you are upgrading your clients From Crystal 8 to what?

koossa

ASKER

I'm only upgrading from Access to SQL Server, stay on Crystal 8.

Brook Braswell

That may be why it will not allow you to write to that value.
I get the error only if on a sub report in rare instances but it is a runtime.
The application would run normally.
I will look to see if CR8 will not allow that whereas CRXI will.

koossa

ASKER

I have upgraded to Crystal XI, which references do I add to use the above code?
For ver 8 I have used "Crystal reports 8 Activex Designer Design Time library" and "Crystal reports 8 Activex Designer Run Time Library", but I don't see any similar ones in ver 11

ASKER CERTIFIED SOLUTION

Brook Braswell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mike McCracken

Which edition of CR XI do you have?

The last one should be. The other requires additional runtime licenses
Crystal Reports ActiveX Designer Runtime Library 11.0

mlmcc

koossa

ASKER

Thank you!