Crystal Reports 8.5 - Cannot Programmatically Connect Until Manually Opened

Posted on 2007-07-23
Last Modified: 2013-12-25
Hi everyone!  I have another Crystal Reports 8.5 question that has me scratching my head. =)

I am programatically changing the database source, login name and password for Crystal Reports 8.5 reports (.rpt files).  My intent is to be able to simply copy the .rpt files to any system with Crystal Reports 8.5 and my software installed, and when my software is run it will run the reports on that system's database.

I've noticed that, if I create a set of .rpt files on a particular system and then copy those .rpt files to another system, when I run my code that programatically changes the database source and tries to open the .rpt file it says that it cannot connect to the database (even if both the original and the new system that the .rpt files now reside on were using the same database and connection information).

However, if I open any one of the .rpt files in the Crystal Reports program itself and give it the database username/password manually, *ALL* of the .rpt files (not just the one that was directly opened) will now open correctly when I programmatically change their connection information.

Is there some kind of hidden INI file or Registry setting that Crystal Reports creates that is causing this to suddenly work if I open a file and connect it to the database manually?  If I cannot create said file/setting manually, that means that I'll have to have the end user open one of the reports manually before they can use them automatically through my program... and that's less than ideal. =)

Thanks for any help you can provide!
Question by:ISCANTEAM
    LVL 100

    Expert Comment

    No setting that I am aware of.  We didn't have any problems.

    Make sure you aren't saving data with the report
    In code

    In the Crystal designer
    Open a report
    Click FILE
    Ensure SAVE DATA WITH REPORT is not checked

    LVL 42

    Expert Comment

    How are you connecting to the database?  Sounds like you might be using an ODBC connection that isn't saving the login credentials so until you supply them your reports won't be able to connect?


    Author Comment


    Hmm, I'll try that and let you know.


    If it helps, we're connecting using the Active Data (ADO) Connection Source, and we are programatically providing login credentials.  The following is a relevant source code clipping from where we do it:

    Private Sub ShowReport(strPath As String, strSelection As String)
    '  Purpose: Show the given report.
        Dim crxApp As CRAXDRT.Application
        Dim crxReport As CRAXDRT.Report
        Dim crxDataTable As CRAXDRT.DatabaseTable
        Set crxApp = New CRAXDRT.Application
        Set crxReport = crxApp.OpenReport(GetExePath(strPath, True), 1)

        ' Set the main report's database location
        For Each crxDataTable In crxReport.Database.Tables
            With crxDataTable
                .Name = .Name
            End With
            Call MsgBox("Path: " & gUserSettings.DatabasePath & vbCrLf & "User: " & gUserSettings.DatabaseUser)
            crxDataTable.SetLogOnInfo gUserSettings.DatabasePath, "Projects", gUserSettings.DatabaseUser, gUserSettings.DatabasePassword
            crxDataTable.Location = Right(crxDataTable.Location, Len(crxDataTable.Location) - InStrRev(crxDataTable.Location, "."))
            Call MsgBox("LOGONPath: " & crxDataTable.LogOnServerName & vbCrLf & "LOGONUser: " & crxDataTable.LogOnUserID & vbCrLf & "LOGONDB: " & crxDataTable.LogOnDatabaseName & vbCrLf & "Location: " & crxDataTable.Location & vbCrLf)
            If Not crxDataTable.TestConnectivity Then
                Call MsgBox("Unable to connect to database: " & crxDataTable.Name, vbInformation + vbOKOnly, "Error Connecting to Database")
                Set crxReport = Nothing
                Set crxApp = Nothing
                Exit Sub
            End If
        CRViewer.ReportSource = crxReport
    End Sub

    It's especially odd because we only have to open *one* report through the normal Crystal Reports program for *all* of them to suddenly work using the code above.  If I run the code above on a computer before I open any one of the reports up manually, the messageboxes in the code above still show that the connection information has been properly set... it just doesn't actually make the connection (I get a "Cannot connect to database" error from Crystal Reports) when I get to that .TestConnectivity line.

    But I have a good number of reports (a dozen or more), and opening any one of them manually will then allow the code above to work (even if I haven't opened any of the others on that system).  Hopefully this helps with the mystery. =)

    Author Comment


    Aye, it looks like "Save Data with Report" was already unchecked for all of the reports.  Shoot, I was hoping that might be what was up. =(
    LVL 42

    Expert Comment

    What is the exact error message you get - is it actually "cannot connect to database"?

    Author Comment

    Well, it's not an error message from Crystal Reports itself.  I get the error from the message box I had made above:

    Call MsgBox("Unable to connect to database: " & crxDataTable.Name, vbInformation + vbOKOnly, "Error Connecting to Database")

    Which indicates that the .TestConnectivity method failed.  No error from Crystal Reports itself though other than the failed connectivity.  And once I open any one of the reports manually, the TestConnectivity works for any of the other reports from then on.

    (needless to say, it's a pain to test this problem repeatedly... I've run out of computers at my office to test it on because once I open it manually it all works =)  I'm gonna nuke one of the systems and install Norton Ghost on it so I can test this unusual problem without having to re-nuke or find completely new systems every time. ;))
    LVL 42

    Accepted Solution

    Well, I'm not really sure what would be causing this.  If you were using a repository data source then I'd say the repository needs to be initialized on the new machine but I don't think that's it.


    #1)  Comment out the testconnectivity method in your code and see if you get a more helpful error message when you associate the report to the viewer.

    #2) If you have the tools to do it, compare the system before you open one of the reports and then after you open one to determine what has changed.  If nothing else I'd export the system registry before and after and use MS Word to compare them - any differences may give you a clue what's happening.


    Author Comment

    Aye, good ideas.  I'll see what I can discover... hopefully I'll have some time to wipe a system tomorrow and get started on that.  I'll report here when I get more info. =)

    Author Comment

    Aye, I haven't had time to find the actual solution yet (I was redirected to other work by my boss).  Sorry about the delay there: I've tenatively accepted your solution, but I won't add it to the Knowledgebase since we didn't figure out what was going on.  When I get some time, I'll repost this question and we'll see what we can do. =)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
    This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    779 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

    14 Experts available now in Live!

    Get 1:1 Help Now