Logon problem with ADO recordset in CR8.5 and VB

Posted on 2006-04-27
Last Modified: 2012-05-05

I need help desparely and I have been my head for 2 months and tried and debugged everything but of no use.

I am using Crystal Report 8.5 development and VB to generate, view and export to pDF format using the following code (partly shown here).

    Dim OutputFile, AutogenFile As String
    Dim fs As New FileSystemObject
    Dim crApplication As New CRAXDRT.Application

-- report generation code here

    rptDSN = "gomsMasters"
    rptUID = "oms"
    rptPWD = "Kom1Shee2"

    crApplication.LogOnServer "p2sodbc.dll", rptDSN, strDatabase, rptUID, rptPWD

    mReport.txtUserName.SetText "User: " & mUserName
    mReport.txtCompanyName.SetText mCompanyName
    mReport.txtOMSname.SetText mOMSName
    mReport.txtAPPname.SetText App.ExeName
    mReport.txtDbStatus.SetText DbState

    nTables = mReport.Database.Tables.Count

    Write #ff, "---------------------------------------"
    Write #ff, Now()

    Write #ff, mMachine
    Write #ff, gomsLog.UserName
    Write #ff, strServer
    Write #ff, strDatabase
    Write #ff, Report1, mCrystalRptName
    Write #ff, nTables

    For i = 1 To nTables
        mReport.Database.Tables(i).SetLogOnInfo rptDSN, strDatabase, rptUID, rptPWD
        Write #ff, i, nTables
        Write #ff, mReport.Database.Tables(i).Name
        Write #ff, mReport.Database.Tables(i).LogOnServerName
        Write #ff, mReport.Database.Tables(i).LogOnDatabaseName
        Write #ff, mReport.Database.Tables(i).LogOnUserID
        Write #ff, mReport.Database.Tables(i).TestConnectivity
    Next i

    Write #ff, Now()
    Write #ff, "---------------------------------------"

    Close #ff

    OutputFilename = gstrRootFolder & "\Reports\" & rptName & ".pdf"

    '    Call PrintToPDFPrinter(mReport, "DocuCom PDF Driver", mCrystalRptName, OutputFilename, mReport.PaperOrientation)

    Block = "Export to ADOBE PDF Format"
    mReport.ExportOptions.FormatType = crEFTPortableDocFormat
    mReport.ExportOptions.PDFExportAllPages = True
    mReport.ExportOptions.DestinationType = crEDTDiskFile
    mReport.ExportOptions.DiskFileName = OutputFilename
    mReport.Export False

    CRViewer1.ReportSource = mReport

    CRViewer1.EnableExportButton = True
    CRViewer1.EnableGroupTree = True
    CRViewer1.EnableNavigationControls = True


    Set mReport = Nothing
    Screen.MousePointer = vbDefault

The problem is, this code and all others work on my machine (W2K Prof and SQL 2000). I have installed this application for last 3 years for a client overseas and was
working fine until I migrated from CR7.0 to CR8.5. On my client machine (Window 2003 server and SQL 2000), this code complains about not being able to logon to the database. I can logon OK externally using enterprise or SQL query analyzer or ODBC at my client machine (which I can access remotely). I have put debugs to test connectivity in the code. It is success at my machine but fails at my client's machine and I have exhausetd all possible reasons.

I have seen great solutions to my problems in this forum and I hope somebody can help me out here. The problem happens only when the report uses ADO recordset and does happen if report uses only one table for report data.

Please help !!! and thanks a million inadvance,

Question by:sheekom
    LVL 7

    Expert Comment

    Ok, so assuming the following points are correct:
    1) SQL2000 (Not MSDE) is installed on your Windows 2003 Server
    2) From the 2003 Server, Enterprise manager works fin
    3) From your W2K PRo Desktop, you can still connect to the SQL Server instance running on Windows 2003

    Check the installed version of MDAC at the server and compare with MDAC installed on your development
    machine.  Likely your desktop was updated when Crystal Reports was updated.


    Author Comment


    I even installed complete CR8.5 dev system on my client machine and still the same result. I did so for 3-4 times. I access
    the client machine (overseas) via citrix. Although I will compare MDAC at server and my machine tonite when I get access
    to the client machine.

    Any other ideas ?


    LVL 7

    Expert Comment

    The main areas you will run into are MDAC or permissions, if MDAC is good, then likely your provider credentials are
    out, check and try to login with the credentials coded in your report.

    You probably didn't want to hear that :)


    Author Comment


    MDAC version on my machine here and client machine are same 2.8Sp1. I can logon the server and databases via SQL query, enterprise managers using
    the same credentials as shown in the code above. Funny things is that my application/s have about 100+ reports. All reports which have direct tables (regardless of
    number) and fields from them on the report work fine without any problem. All those reports where I use ADO recordset and fields from it on the report, give logon error. Strange ? What am I missing here ? Any ideas ?



    Author Comment

    Correction - My client's machine has W2k Server SP4 /SQL server 2000 and I have W2K professional /SQL server 2000

    LVL 7

    Expert Comment

    Can you check to see if your DSN is a user DSN or a System DSN?

    Author Comment

    System DSN

    Author Comment


    I have worked around the problem and used "push" method by using *.rpt reports rather than using *.dsr files and "pull" method. It seems to be weird combination of CR8.5 and W2K Server with pull method. We tried virtually everything. Anyway, our alternate push method has worked well on my machine as well as client machine. We had to just conver dsr files to rpt formats, which was not a big problem as we used export feature in VB code.

    I consider this thread closed.

    Thanks everybody,


    Accepted Solution

    PAQed with points refunded (500)

    Community Support Moderator

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    twoTwo  challenge 35 74
    bunnyEars challenge 6 47
    stringclean challenge 26 43
    strCount chalenge 3 36
    This is an explanation of a simple data model to help parse a JSON feed
    Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
    In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
    In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

    746 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

    13 Experts available now in Live!

    Get 1:1 Help Now