Link to home
Start Free TrialLog in
Avatar of JoceRakower
JoceRakower

asked on

CR9 & VB6 & LogOnServer change UID & PWD at run time

I've got 234 Crystal Reports v.9 which I'm trying to get VB6 to batch run overnight using a specific UID and password for each report - during the day these reports are run ad-hoc by users (and the reports seem to remember the last user) - so I want to pass fresh UID and PWD for each report at run time using VB6 - there are several databases and database types being used although each report is limited to using one database

Having seen a previous thread I've just re-installed CR9 using custom installation and putting in EVERY driver it can give me so I don't think that's the problem.

I've been trying to use the LogOnServer method of the RDC (as recommended by Crystal Decisions) but to no avail (getting fault -2147189172) and the code I'm using is:

Sub Form_Load()

Dim App1 As CRAXDRT.Application
Dim Report As CRAXDRT.Report
       
       Set App1 = CreateObject("CrystalRuntime.Application")
       
 'XXX NEXT LINE IS WHERE IT ALWAYS FALLS OVER    
 App1.LogOnServer "p2smon.dll", "myDSNname", "myDatabase", "myname", "mypassword"
       
       
       Set Report = App1.OpenReport("report.rpt")
       Report.DiscardSavedData

Debug.Print App1.GetVersion
Report.Database.Verify
CRViewer91.ReportSource = Report
CRViewer91.ViewReport

Thanks for helping :-)
Avatar of Mike McCracken
Mike McCracken

Have you added the correct CR9 references?
  I thought CRAXDRT was for pre CR9 and CRAXDRT9 was for CR9.

Do you perhaps have 2 versions of Crystal loaded on the machine?

mlmcc
Do you have the developer edition of Crystal?

Does this perhaps apply?
http://support.crystaldecisions.com/library/kbase/articles/c2011875.asp

mlmcc
Avatar of JoceRakower

ASKER

Thanks for comments.  

No I don't have CR9 developer only CR9 Professional (and can't afford the CR9 developer license).  

References CRAXDRT refers to CRAXDRT9.dll - I've checked it refers to the latest version of CR installed.

I've followed the suggested link & applied all patches, hot fixes and service packs as suggested as well as SP5 for VB6 but to no avail - any further ideas are very welcome as I'm still stuck - I've also increased the points as this one seems a tad "challenging" :-)

Thanks :-)
joce
During the day are these reports being run from a single machine?

mlmcc
Nope (sorry) - the reports live on a network server and attach to different databases (one database per report though) and a whole team of people run these ad-hoc from their own PCs across the domain - I'm having a single logon created with all permissions specifically to run these overnight as a batch - thanks for your help.  Joce
ASKER CERTIFIED SOLUTION
Avatar of EwaldL
EwaldL

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Sorry about the delay for this comment - I've tried your suggestion above & it has been running for 2 months now - it seems to work perfectly for 2 of the databases and not at all for another 2 - somewhere it's still loosing the plot - have you any further advice please?
what is the final vb code for this problem?? i still cant get a solutiion for this because i also have the same problem with you. Pls do guide me on how to write a correct vb code. thanks!
OK this is what I use, and it seems to work most of the time (if you can improve on it please let me know)

Firstly, I have a form which has got a CrystalViewer on it, NOTE: it loops through the report 3 times to allow for changes to the database etc and seems to work even though it's whacky, ALSO NOTE: I've deleted some irrelevant code before posting so the "end if" "end with" and so on may be mismatched, and the code is: (the code for the module is shown after the code for the form)

Option Explicit

Private Sub CRViewer91_RefreshButtonClicked(UseDefault As Boolean)

    UseDefault = True

End Sub

Sub Form_Load()

're-set the parameters 3 times - this is a quirk from Crystal to force it to refresh properly you need to do it twice in code
'I know this seems daft but it seems to work so ......

Dim strTempFileName As String
Dim CRXApp1 As New CRAXDRT.Application
Dim CRXReport As New CRAXDRT.Report
Dim CRXDatabase As CRAXDRT.Database
Dim CRXParamDefs As CRAXDRT.ParameterFieldDefinitions
Dim CRXParamDef As CRAXDRT.ParameterFieldDefinition

Dim i As Long
Dim j As Long
Dim blnTempFound As Boolean
Dim intRecursiveLoop As Integer

On Error GoTo paramError

    strCrystalInputFileName = arrayOtherParam(1)
   
    Set CRXApp1 = New CRAXDRT.Application
   
    If Len(strCrystalInputFileName) < 4 Then Exit Sub
   
    Set CRXReport = CRXApp1.OpenReport(strCrystalInputFileName, 1)
   
    Set CRXDatabase = CRXReport.Database
    SetTable CRXReport, DataSource, Login, PWD
   
    For intRecursiveLoop = 1 To 3         'loop 3 times .... if the Crystal report cant pick up new
                                                            'parameter values by now well then ..... ***** **!
       
        CRXReport.DiscardSavedData
        CRViewer91_RefreshButtonClicked (True)
        Set CRXParamDefs = CRXReport.ParameterFields
           
        i = 1
   
        For Each CRXParamDef In CRXParamDefs
   
                With CRXParamDef
               

                       For j = 1 To lgParamCounter
                       
                            'now find a match
                            If arrayReportParam(j, 1) = .Name Then
                                 
                                 'now check its a date and pass the correct parameters if it is
                                 If arrayReportParam(j, 3) = "True" Then 'XXX check it returns true and not something else
                                     If .ValueType = 16 Then
                                         .SetCurrentValue arrayReportParam(j, 7)
                                     ElseIf .ValueType = 10 Then
                                         .SetCurrentValue arrayReportParam(j, 6)
                                     End If
                                 
                                 'if its not a date pass the misc value available
                                 Else
                                     
                                     If IsNull(arrayReportParam(j, 5)) = True Then arrayReportParam(j, 5) = ""
                                     .SetCurrentValue arrayReportParam(j, 5)
                                 
                                 End If
                                     
                                 'found so exit this loop and go to next parameter
                                 blnTempFound = True
                                 Exit For
                                 
                             Else
                             
                                'not found so set the boolean to false
                                blnTempFound = False
                       
                            End If
                           
                        Next j

            End With
   
            i = i + 1 'not sure this is actually used
   
        Next
   
    Next intRecursiveLoop
       
    CRXReport.EnableParameterPrompting = False
   
    With CRXReport.ExportOptions
        .DestinationType = crEDTDiskFile
        .FormatType = arrayOtherParam(3)
        .DiskFileName = strOutputFileName & arrayOtherParam(4)
    End With
   
    CRXReport.Export False
    Call writeSuccessLog
    CRXApp1.CanClose
    Exit Sub
   
paramError:

    strErrorDetails = Err.Number & " " & Err.Description
    Call writeFailureLog
    Err.Clear
    CRXApp1.CanClose
    Exit Sub

End Sub

Sub Form_Resize()

    With CRViewer91
        .Top = 0
        .Left = 0
        .Width = Me.ScaleWidth
        .Height = Me.ScaleHeight
    End With

End Sub

Now here's the code for the module:

Public Sub SetTable(Rpt As CRAXDRT.Report, DataSource As String, Login As String, PWD As String)


    Dim CRXTable As CRAXDRT.DatabaseTable
    Dim CRXTables As CRAXDRT.DatabaseTables
    Dim connects As Boolean
    Dim bError As Boolean
    Dim sErrorString As String
   
    bError = False
   
    On Error GoTo errSetTable

    Set CRXTables = Rpt.Database.Tables
    Set CRXTable = CRXTables.Item(1)
          CRXTable.SetLogOnInfo DataSource, "", Login, PWD
         connects = CRXTable.TestConnectivity
         
         'THIS IS WHERE WE WANT TO RUN THE FORMS?
         
    Set CRXTable = Nothing
    Set CRXTables = Nothing
   
    If bError Then
        'debug.print "An error occurred setting report tables " & vbCrLf & _
                sErrorString, vbOKOnly + vbExclamation, "Set Table Error"
    End If

Exit Sub

errSetTable:
    sErrorString = sErrorString & Err.Description & vbCrLf
     App.LogEvent "ERROR-" & Err.Number & "-" & Err.Description
    bError = True
    Resume Next
   
End Sub