Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Convert Database Driver on the fly

Posted on 2004-03-31
6
Medium Priority
?
1,424 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
6 Comments
 
LVL 101

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 2000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

660 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