Solved

Convert Database Driver on the fly

Posted on 2004-03-31
6
1,401 Views
Last Modified: 2012-06-27
Hi,

I have a report in designed in access 2000. Usually I convert the report to Sql Server 2000 using the RDC ( Database-> Convertdatabasedriver) and give Server,Database,userid,pass. So the report now is converted to SqlServer.
Actually I want to run the convertdatabasedriver on the fly.
I didnot find any of the solution through forums in Internet.
Can any please send /post your solution with simple code which can work..
Thanks,
Mohammed Yassin...


     Dim report As CRAXDRT.report
     Dim crxApp As CRAXDRT.Application
     Dim I As Integer
     Set crxApp = New CRAXDRT.Application
     Set report = crxApp.OpenReport("D:\Maintenance\Reports\Test.rpt", 1)
     Dim crxDatabaseTable  As CRAXDRT.DatabaseTable
           
     
     
     For Each crxDatabaseTable In report.Database.Tables
      if dat="Access" then      
              crxDatabaseTable.SetLogOnInfo "", "Maintanance.mdb"
        Elseif dat="SQLSERVER" then
 crxDatabaseTable.SetLogOnInfo "Yassin", "Maintenance", "sa", "nitco"
Endif
     Next crxDatabaseTable
     report.Database.Verify
     CRViewer1.ReportSource = report
     CRViewer1.ViewReport
0
Comment
Question by:yassinf
  • 2
  • 2
6 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 10729698
I know the data source can be changed but that requires the database driver to remain the same.

This article discusses when and why to convert.
http://support.businessobjects.com/communityCS/TechnicalPapers/cr_convert_database_driver.pdf

Here is an article that indicates it can be done but only after applying the proper updates to CR8 and it is for the English version only.
http://support.businessobjects.com/library/kbase/articles/c2009586.asp

mlmcc

0
 
LVL 2

Expert Comment

by:Ivan_Skrinjaric
ID: 10744198
SetLogOnInfo and ConvertDatabaseDriver are deprecated methods. Insted you should use ConnectionProperties
Collection. This is just sample i wrote and it should work. If it doesn't or if you can give me more specific description what you need i will try to help you.

Public Sub ReportDatabaseConvert(oReport As CRAXDRT.Report, sDB_Type As String, sDatabase As String, sUsername, sPassword As String)
    Dim oTable As CRAXDRT.DatabaseTable
   
    For Each oTable In oReport.Database.Tables
        Select Case UCase(sDB_Type)
            Case "ORACLE"
                With oTable
                    .ConnectionProperties.DeleteAll
                    .ConnectionProperties.Add "Server", sDatabase
                    .ConnectionProperties.Add "Password", sPassword
                    .ConnectionProperties.Add "User ID", sUsername
                End With
               
            Case "ACCESS"
                With oTable
                    .ConnectionProperties.DeleteAll
                    .ConnectionProperties.Add "Database Name", sDatabase
                    .ConnectionProperties.Add "Database Password", sPassword
                End With
               
        End Select
    Next
End Sub
0
 

Author Comment

by:yassinf
ID: 10746612
Thanks to All in Advance,
  Mr  Ivan_Skrinjaric, code must work with me but, I did not get the "ConnectionProperties" with me.

oTable.ConnectionProperties  - is not found in my CRAXDRT

Referencing in the project "CRAXDRT" is ok with me.

Is there anything in need to Reference to my project...
Thanks,
Mohammed Yassin
0
 

Author Comment

by:yassinf
ID: 10748111
Thanks to All in Advance,
  Mr  Ivan_Skrinjaric, code must work with me I also get the "ConnectionProperties" with me.  This is the actual code testing with me. But I get the error as:


 Logon Failed
 Details : ADO Error Code : 0X80004005
Source : Microsoft OLE DB Provider for ODBC Drivers
         ....    Data Source Name not found or No Default Drivers Specified..

Note : Please correct if miss anything.. Like  may be need to use SetLogonInfo.. Actually I confused..
Thanks,
Mohammed Yassin..



     Dim report As CRAXDRT.report
     Dim crxApp As CRAXDRT.Application
     Set report = New CRAXDRT.report
     Set crxApp = New CRAXDRT.Application
     Set report = crxApp.OpenReport("D:\Maintenance\Reports\Test3.rpt", 1)
     Call ReportDatabaseConvert(report, "SQLSERVER", "Maintenance", "sa", "nitco")

Public Sub ReportDatabaseConvert(oReport As CRAXDRT.report, sDB_Type As String, sDatabase As String, sUsername, sPassword As String)
    Dim oTable As CRAXDRT.DatabaseTable

    For Each oTable In oReport.Database.Tables
        Select Case UCase(sDB_Type)
            Case "SQLSERVER"
                With oTable
                    .ConnectionProperties.DeleteAll
                    .ConnectionProperties.Add "Server", sDatabase
                    .ConnectionProperties.Add "Password", sPassword
                    .ConnectionProperties.Add "User ID", sUsername
                End With
                 
            Case "ORACLE"
                With oTable
                    .ConnectionProperties.DeleteAll
                    .ConnectionProperties.Add "Server", sDatabase
                    .ConnectionProperties.Add "Password", sPassword
                    .ConnectionProperties.Add "User ID", sUsername
                End With


            Case "ACCESS"
                With oTable
                    .ConnectionProperties.DeleteAll
                    .ConnectionProperties.Add "Database Name", sDatabase
                    .ConnectionProperties.Add "Database Password", sPassword
                End With

        End Select
    Next
     CRViewer91.ReportSource = oReport
     CRViewer91.ViewReport

End Sub
0
 
LVL 2

Accepted Solution

by:
Ivan_Skrinjaric earned 500 total points
ID: 10752450
Sorry Mohammed for waiting. I have been busy and I also wanted to test the code before posting it since last time I made a small mistake. This time for test i have created a report baset on MSSQL Nortwind database and converted it "on the fly" with this code. I have also tried opposite and it works. I have also added oracle to the code but havent tested it. If you need ODBC support please tell.

If you will have problems with this code or this is not what you have been looking for pleaes try to describe in more detail what you need and I will try to help you. Also look at techref.pdf on CR9 CD in DOCS dir and download "RDC Browser 9" from support.businessobjects.com.



Private Sub Form_Load()
    Dim oReport As CRAXDRT.Report
    Dim oApplication As New CRAXDRT.Application
   
    Set oReport = oApplication.OpenReport("test.rpt")
   
    ' Uncomment for MSSQL
    ' ReportDatabaseConvert oReport, "MSSQL", "Northwind", "sa", "", "localhost"
   
    ' Uncomment for ACCESS
    ReportDatabaseConvert oReport, "ACCESS", "Northwind.mdb", "Admin", "", ""
   
    crViewer.ReportSource = oReport
    crViewer.ViewReport
End Sub


Public Sub ReportDatabaseConvert(oReport As CRAXDRT.Report, sDB_Type As String, sDatabase As String, sUsername, sPassword As String, sServer As String)
    Dim oTable As CRAXDRT.DatabaseTable
   
    For Each oTable In oReport.Database.Tables
        Select Case UCase(sDB_Type)
            Case "MSSQL"
                With oTable
                    .DllName = "crdb_ado.dll"
                    .ConnectionProperties.DeleteAll
                    .ConnectionProperties.Add "Data source", sServer
                    .ConnectionProperties.Add "Database", sDatabase
                    .ConnectionProperties.Add "Password", sPassword
                    .ConnectionProperties.Add "User ID", sUsername
                    .ConnectionProperties.Add "Provider", "SQLOLEDB"
                End With
               
            Case "ORACLE"
                With oTable
                    .DllName = "crdb_oracle.dll"
                    .ConnectionProperties.DeleteAll
                    .ConnectionProperties.Add "Server", sDatabase
                    .ConnectionProperties.Add "Password", sPassword
                    .ConnectionProperties.Add "User ID", sUsername
                End With
               
            Case "ACCESS"
                With oTable
                    .DllName = "crdb_ado.dll"
                    .ConnectionProperties.DeleteAll
                    .ConnectionProperties.Add "Provider", "Microsoft.Jet.OLEDB.4.0"
                    .ConnectionProperties.Add "Data source", sDatabase
                    .ConnectionProperties.Add "User ID", sUsername
                    .ConnectionProperties.Add "Password", sPassword
                    .ConnectionProperties.Add "Database Type", "Access"
                End With
        End Select
    Next
End Sub
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

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 …
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…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

809 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