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("CrystalRunti me.Applica tion")
'XXX NEXT LINE IS WHERE IT ALWAYS FALLS OVER
App1.LogOnServer "p2smon.dll", "myDSNname", "myDatabase", "myname", "mypassword"
Set Report = App1.OpenReport("report.rp t")
Report.DiscardSavedData
Debug.Print App1.GetVersion
Report.Database.Verify
CRViewer91.ReportSource = Report
CRViewer91.ViewReport
Thanks for helping :-)
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("CrystalRunti
'XXX NEXT LINE IS WHERE IT ALWAYS FALLS OVER
App1.LogOnServer "p2smon.dll", "myDSNname", "myDatabase", "myname", "mypassword"
Set Report = App1.OpenReport("report.rp
Report.DiscardSavedData
Debug.Print App1.GetVersion
Report.Database.Verify
CRViewer91.ReportSource = Report
CRViewer91.ViewReport
Thanks for helping :-)
Do you have the developer edition of Crystal?
Does this perhaps apply?
http://support.crystaldecisions.com/library/kbase/articles/c2011875.asp
mlmcc
Does this perhaps apply?
http://support.crystaldecisions.com/library/kbase/articles/c2011875.asp
mlmcc
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
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
mlmcc
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
ASKER
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_RefreshButtonCl icked(UseD efault 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.ParameterFieldDefi nitions
Dim CRXParamDef As CRAXDRT.ParameterFieldDefi nition
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(strCrystalInputFileNam e) < 4 Then Exit Sub
Set CRXReport = CRXApp1.OpenReport(strCrys talInputFi leName, 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_RefreshButtonCl icked (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.EnableParameterP rompting = 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
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_RefreshButtonCl
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.ParameterFieldDefi
Dim CRXParamDef As CRAXDRT.ParameterFieldDefi
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(strCrystalInputFileNam
Set CRXReport = CRXApp1.OpenReport(strCrys
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_RefreshButtonCl
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,
.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.EnableParameterP
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
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