Solved

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

Posted on 2003-11-03
9
3,759 Views
Last Modified: 2012-06-27
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 :-)
0
Comment
Question by:JoceRakower
9 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 9674320
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
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 9674349
Do you have the developer edition of Crystal?

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

mlmcc
0
 

Author Comment

by:JoceRakower
ID: 9710176
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
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 9710273
During the day are these reports being run from a single machine?

mlmcc
0
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.

 

Author Comment

by:JoceRakower
ID: 9714469
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
0
 
LVL 13

Accepted Solution

by:
EwaldL earned 500 total points
ID: 9735908
Such development is not meant to work with the professional edition, so you might miss some files. In the developer or advanced edition you can try this for the logon. Test this first with report that do not have subreports

Dim myReport As CRAXDRT.Report
[...]
Dim myTable As CRAXDRT.DatabaseTable
For Each myTable In myReport.Database.Tables
  myTable.ConnectionProperties("Password") = "ThePassword"
  myTable.ConnectionProperties("User ID") = "TheUser"
Next

Your code might still work though if you change p2smon.dll to crdb_ado.dll

Just to make sure, you are not trying to point the report to a different datasource by the applicaton?
0
 

Author Comment

by:JoceRakower
ID: 10061758
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?
0
 

Expert Comment

by:panfp
ID: 12304538
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!
0
 

Author Comment

by:JoceRakower
ID: 12305968
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

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

747 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

12 Experts available now in Live!

Get 1:1 Help Now