VB6 and CR 8.5 OCX Viewer

[Former Member]

I know this is old technology, but I have to update an old program to work temporarily while it is being rewritten.  I have a couple of issues.

It was viewing files with no problems.  It used the Connect property to change the datasource when running the reports.  Designs are done on different databases, either text or MySQL.  Now there has been a request to support the dynamic changing of databases for subreports that are different from main reports.  So the users wish to have reports that the main report is run off the MS Access text driver and subreports that run from MySQL ODBC driver (or other combinations).

So I was trying to figure out if a given report may have a subreport that is MySQL or Text.  I was testing if the .RetrieveLogonInfo command will give me the information I need (DSN Name) and now I am getting a message that I cannot use the LogonInfo and Connect at the same time.

Problem 1:

I am not able to get the proper DSN from the .RetrieveLogonInfo command for the subreports using the following code.

intNumSubs = crReports.GetNSubreports
    For x = 0 To intNumSubs - 1
        sConnect = ""
        title = crReports.GetNthSubreportName(x)
        With crReports
            .SubreportToChange = title
            number = .RetrieveLogonInfo
            strCurrentConnect = .LogonInfo(0)                    'still giving me the main report DSN
            sConnect = GetReportDSN(strCurrentConnect)
                    
            For y = 0 To number
                crReports.LogonInfo(y) = sConnect     'replaced .Connect = sConnect with this for loop
            Next y
            
            Debug.Print strCurrentConnect
'            '.Connect = sConnect
        End With
    Next x

I've made a couple of notes in the code above.  When the main report is the text driver and the subreport is MySQL, the subreport .LogonInfo(0) will not give me the correct information.

Second Problem:

If I can get the proper information, what is the proper way to use the .LogonInfo command to reset the information once I figure out which one it should be (text or MySQL)?

If anyone has any insight, I would appreciate some help.  If you need more info, I can do that too.  TIA rasinc.

[former_member183750 · Accepted solution]

Ouch, talk about the good ol times before the Solar System was spawned

This so long ago I really don't remember much. There are eources of info you can look up. A search string like 'crystal ocx' will bring up a number of hits. (Search box is n top right corner). If you google the same search string you'll get a few more hits.

And then, you have worry about CR 8.5 actually supporting the database you want to be hitting. E.g.; CR 8.5 would not connect to MS SQL 2012, or Oracle 11g, etc.,etc. Text files will obviously not be a problem, MySQL could be.

- Ludek

Senior Support Engineer AGS Product Support, Global Support Center Canada

Follow us on Twitter

[Former Member · Accepted solution]

Thanks Ludek.

I've been working with Crystal 8.5 and MySQL for over 6 years so I know it's not a problem.  I only care about MySQL and the MS text driver.  I've been searching for a week for some good information on how to check which DSN is already stored in the reports and subreports and thought the index for the LogonInfo(x) was for the zero-based table number so I thought it required logging onto each table with the proper connection info.

However, I have been reading the Developr.hlp file and found the code to pull the logon info but RetrieveLogonInfo does not seem to function for subreports.  So I am not sure what to do in order to determine which DSN is used in each subreport.  Of course, now when I am searching, this thread comes up as number 1 in the google results.  I'll search more directly on this site to see if I can find anything.

[ido_millet · Accepted solution]

One of the 3rd-party Crystal Reports viewers listed at http://kenhamady.com/bookmarks.html provides options to interactively, via ini file settings, or via command line arguments to selectively change ODBC data source used by the main report or any of the subreports.

[Former Member · Accepted solution]

Thank you Ido.  I am familiar with Ken's website but I created this original viewer back in 1999 and updated it for a few years.  The clients have been using it ever since.  It does change drivers when necessary and in the setup of each report, I have a flag that indicates which DSN should be used for the whole report.  My viewer will then change the DSN from the one the report was created on to the one the viewer uses of the same type.  However, in the past it is always assumed the subreports are based on the same DSN-type as the original main report.  If the main report is based on a MySQL database, then the subreports are based on the same MySQL database as well.  This type of programming has been working since last century.  From what I have read of other viewers, they handle it the same and assume the drivers for each subreport are the same as the main report.  My viewer is not just a dumb viewer either, it has business logic built into it for some things, like starting the database is if it not currently running, setting up the ODBC DSN's if they don't exist, etc.

The issue currently in place, is that several clients are now wishing to combine the databases in a single report.  For example, they will run the main report off the text driver but will need more information from the MySQL database.  This is fine if you are in Crystal, but in the viewer, I cannot tell which DSN a subreport is using through code with the ocx control.  I thought I could just use SubreportToChange  and then get the RetrieveLogonInfo from the subreport.  However, it looks like that doesn't work.  These new reports are roughly mission critical at this point and I do not always have control over the design.

I need to try to get it working until I can finish the rewrite.  I've been rewriting the program in VB.NET and have tested support for Crystal XIR2 and Crystal 2011.  I have programmed it (with help from Don, Ludek and others here) to determine which subreport uses which DSN and it is working fine but not all the features of the old program are fully converted and it will still take me another 2 or 3 months to finish it.  Meanwhile, my clients can't get the reports they need.

Thank you again.  If you or anyone else has an idea how to get the DSN information from the subreports, I would appreciate the help.

[ido_millet · Accepted solution]

While it's true most viewers don't differentiate when a report uses multiple data sources, the viewer mentioned above DOES differentiate, and allows you to be selective about what data source should be switched to what data source (in either the main report or the subreports).

[Former Member · Accepted solution]

Thank you, I'll have a look through them again.