Go Premium for a chance to win a PS4. Enter to Win


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

Posted on 2003-11-03
Medium Priority
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")
 App1.LogOnServer "p2smon.dll", "myDSNname", "myDatabase", "myname", "mypassword"
       Set Report = App1.OpenReport("report.rpt")

Debug.Print App1.GetVersion
CRViewer91.ReportSource = Report

Thanks for helping :-)
Question by:JoceRakower
LVL 101

Expert Comment

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?

LVL 101

Expert Comment

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

Does this perhaps apply?


Author Comment

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 :-)
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

LVL 101

Expert Comment

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


Author Comment

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
LVL 13

Accepted Solution

EwaldL earned 1500 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"

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?

Author Comment

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?

Expert Comment

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!

Author Comment

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 ..... ***** **!
        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
                                     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
                                '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 intRecursiveLoop
    CRXReport.EnableParameterPrompting = False
    With CRXReport.ExportOptions
        .DestinationType = crEDTDiskFile
        .FormatType = arrayOtherParam(3)
        .DiskFileName = strOutputFileName & arrayOtherParam(4)
    End With
    CRXReport.Export False
    Call writeSuccessLog
    Exit Sub

    strErrorDetails = Err.Number & " " & Err.Description
    Call writeFailureLog
    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
    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

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


Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Loops Section Overview

927 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