Solved

Convert Database Driver on the fly

Posted on 2004-03-31
6
1,394 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
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 …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

772 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now