Solved

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

Posted on 2011-03-01
17
401 Views
Last Modified: 2012-05-11
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?

0
Comment
Question by:koossa
  • 9
  • 7
17 Comments
 
LVL 14

Expert Comment

by:Brook Braswell
Comment Utility
Are you using parameters on your report?
How does your code look to open the modified report ?
0
 

Author Comment

by:koossa
Comment Utility
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

0
 
LVL 14

Expert Comment

by:Brook Braswell
Comment Utility
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  ?
0
 
LVL 14

Expert Comment

by:Brook Braswell
Comment Utility
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

0
 

Author Comment

by:koossa
Comment Utility
It highlights the .ConnectBufferString =

'Can't assign read-only property'
0
 
LVL 14

Expert Comment

by:Brook Braswell
Comment Utility
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

0
 

Author Comment

by:koossa
Comment Utility
But the ConnectBufferString property is a read only property, so you cannot assign a value to it?
0
 
LVL 14

Expert Comment

by:Brook Braswell
Comment Utility
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

0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:koossa
Comment Utility
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"
0
 
LVL 14

Expert Comment

by:Brook Braswell
Comment Utility
How do you have your options set for error trapping?
Should be Brak on Unhandled Errors
and compile on demand checked...
0
 
LVL 14

Expert Comment

by:Brook Braswell
Comment Utility
I never thought to ask... you are upgrading your clients From Crystal 8 to what?
0
 

Author Comment

by:koossa
Comment Utility
I'm only upgrading from Access to SQL Server, stay on Crystal 8.
0
 
LVL 14

Expert Comment

by:Brook Braswell
Comment Utility
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.
0
 

Author Comment

by:koossa
Comment Utility
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
0
 
LVL 14

Accepted Solution

by:
Brook Braswell earned 500 total points
Comment Utility
In references I use :
BusinessObjects Enterprice UI Controls Library 11.0
Crystal ActiveX Report Viewer Library 11.0
Crystal Reports ActiveX Designer Design and Runtime Library 11.0
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
0
 

Author Closing Comment

by:koossa
Comment Utility
Thank you!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now