Solved

Convert Database Driver on the fly

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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

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…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

630 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