Solved

Convert Database Driver on the fly

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Execute a crystal report from vb2010 3 43
cross tab report accumulative amount 9 53
Pagination Difference  in crystal report 7 51
free Crystal Report viewer 4 21
Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

823 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