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

Posted on 2008-11-04
Medium Priority
Last Modified: 2008-11-13
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
crxRpt.Application.SetMatchLogOnInfo True
SetSubreports crxRpt, _
              strConnect, _
              txtServer.Text, _
              txtDatabase.Text, _
              txtUser.Text, _
'To View the report'
CrystalActiveXReportViewer1.ReportSource = crxRpt
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
            End If
            '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, _
        End If
End Sub

Open in new window

Question by:WernerVonBraun
  • 2
LVL 101

Expert Comment

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


Author Comment

ID: 22917569
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 :-)
LVL 101

Accepted Solution

mlmcc earned 2000 total points
ID: 22918975
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.


Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

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…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses
Course of the Month16 days, 18 hours left to enroll

864 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