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")
 App1.LogOnServer "p2smon.dll", "myDSNname", "myDatabase", "myname", "mypassword"
       Set Report = App1.OpenReport("report.rpt")

Debug.Print App1.GetVersion
CRViewer91.ReportSource = Report

Thanks for helping :-)
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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?

Do you have the developer edition of Crystal?

Does this perhaps apply?

JoceRakowerAuthor Commented:
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 :-)
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

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

JoceRakowerAuthor Commented:
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
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?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JoceRakowerAuthor Commented:
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!
JoceRakowerAuthor Commented:
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

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.