Switch from ODBC to OLEDB and change database. And all this goodness at runtime.

Hello Crystal Reports Experts!

I've got a good'n for you. Now this is the problem:

I have Crystal Reports that were originally designed against an ODBC connection. At run time, I need to change this to make them use an OLEDB connection instead. Not only THAT, but they must also connect to a database with a different name than the original.

Now, I DID manage to make this work in VB6, using a CrystalReportViewer control. I will attach the code for same to this question, so you can see what I am doing.

The problem is that the software I'm modifying was NOT written in VB6. It was written in Clarion 6, and it does NOT use that OCX. So instead of using the OCX I need to be using the API - crpe32.dll and all the wonders it brings.

The problem I'm having is this: when you look at the code I attached, you can see I am checking the Table Properties for the DLLName. If it isn't "crdb_ado.dll", I change the .ConnectBufferString to strConnect, which is an OLEDB connection string, and I change the .DLLName property. THAT is where the problem lies. I can use PEGetNthTableType to do the equivalent of checking the .DLLName property. But crpe32.dll does not expose a PESetNthTableType method, so I can't figure out how to do the equivalent of SETTING the .DLLName property. Failing that, I cannot make the report connect successfully to the OLEDB database.

Any ideas?

I'm using Crystal Reports 10. Upgrading is not an option.
Private Sub cmdGo_Click()
 
Dim crxApp As CRAXDRT.Application
Dim crxRpt As CRAXDRT.Report
Dim lngTableCounter As Long
 
Dim strConnect As String
 
strConnect = "provider=sqloledb;Data Source=" & txtServer.Text & ";Initial Catalog=" & txtDatabase.Text & ";User Id=" & txtUser.Text & ";Password=" & txtPassword.Text
 
Set crxApp = New CRAXDRT.Application
Set crxRpt = crxApp.OpenReport(txtReport.Text)
 
For lngTableCounter = 1 To crxRpt.Database.Tables.Count
    With crxRpt.Database.Tables.Item(lngTableCounter)
        
        'Make it use OLE DB'
        If .DllName <> "crdb_ado.dll" Then
            .ConnectBufferString = strConnect
            .DllName = "crdb_ado.dll"
        End If
        .SetTableLocation txtDatabase.Text & ".dbo." & .Location, txtDatabase.Text & ".dbo." & .Location, strConnect
 
        'Ensure the connection is pointing at the correct server and database'
        If .DllName = "crdb_ado.dll" Then
            With .ConnectionProperties
                .Item("Provider") = "SQLOLEDB"
                .Item("Data Source") = txtServer.Text
                .Item("Initial Catalog") = txtDatabase.Text
                .Item("User ID") = txtUser.Text
            End With
        End If
        With .ConnectionProperties
            .Item("Password") = txtPassword.Text
        End With
    
    End With
Next
 
crxRpt.Application.SetMatchLogOnInfo True
crxRpt.DiscardSavedData
 
SetSubreports crxRpt, _
              strConnect, _
              txtServer.Text, _
              txtDatabase.Text, _
              txtUser.Text, _
              txtPassword
              
 
'To View the report'
CrystalActiveXReportViewer1.ReportSource = crxRpt
CrystalActiveXReportViewer1.ViewReport
 
 
Set crxRpt = Nothing
Set crxApp = Nothing
 
'To print the report straight to the printer, prompting _
the user for the # of pages'
'crxRpt.PrintOut False'
 
End Sub
Private Sub SetSubreports(ByVal crxRpt As CRAXDRT.Report, _
                          ByVal strConnect As String, _
                          ByVal strServer As String, _
                          ByVal strDatabase As String, _
                          ByVal strUser As String, _
                          ByVal strPassword As String)
 
Dim objSect As CRAXDRT.Section
Dim objRO As Object
Dim objSub As CRAXDRT.Report
 
Dim lngTableCounter As Long
 
For Each objSect In crxRpt.Sections
    For Each objRO In objSect.ReportObjects
        If objRO.Kind = CRObjectKind.crSubreportObject Then
            Set objSub = crxRpt.OpenSubreport(objRO.SubreportName)
            If Not (objSub Is Nothing) Then
                For lngTableCounter = 1 To objSub.Database.Tables.Count
                    With objSub.Database.Tables.Item(lngTableCounter)
                        
                        'Make it uses OLE DB'
                        If .DllName <> "crdb_ado.dll" Then
                            .ConnectBufferString = strConnect
                            .DllName = "crdb_ado.dll"
                        End If
                        .SetTableLocation strDatabase & ".dbo." & .Location, strDatabase & ".dbo." & .Location, strConnect
                
                        'Ensure the connection is pointing at the correct server and database'
                        If .DllName = "crdb_ado.dll" Then
                            With .ConnectionProperties
                                .Item("Provider") = "SQLOLEDB"
                                .Item("Data Source") = strServer
                                .Item("Initial Catalog") = strDatabase
                                .Item("User ID") = strUser
                            End With
                        End If
                        With .ConnectionProperties
                            .Item("Password") = strPassword
                        End With
                    
                    End With
                Next
            End If
            objSub.DiscardSavedData
            
            'And each subreport may have'
            'a smaller one that bites ''em'
            'and so the sub-reports go on'
            'and on, ad infinitum'
            SetSubreports objSub, _
                          strConnect, _
                          strServer, _
                          strDatabase, _
                          strUser, _
                          strPassword
        End If
    Next
Next
 
End Sub

Open in new window

LVL 4
WernerVonBraunAsked:
Who is Participating?
 
mlmccConnect With a Mentor Commented:
I haven't used the CRPE32 interface so I don't know for sure about that one.

The OCX is rather old technology and perhaps the older versions of Crystal allowed you to change it on the fly like that.  I was no aware you could do it through the OCX but then I never had a reason to do so.  We always did it through the Change Database Driver In CR8 and now through Set Datasource Location in CR XI.

mlmcc
0
 
mlmccCommented:
Sorry.  To the best of my knowledge you cannot change the database connection type (ODBC to OLE) at runtime.

mlmcc
0
 
WernerVonBraunAuthor Commented:
Well, you can using the OCX control but it looks like you may be right wrt doing it using crpe32

If I don't get a comment denying this within the next while I'll give you the points. After all, if you're right, "you can't do it" is a valid answer, even though I mightn't like it :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.