Logon problem with ADO recordset in CR8.5 and VB


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,

Suresh AgrawalPresidentAsked:
Who is Participating?
PAQed with points refunded (500)

Community Support Moderator
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.

Suresh AgrawalPresidentAuthor Commented:

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 ?


Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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 :)

Suresh AgrawalPresidentAuthor Commented:

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 ?


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

Can you check to see if your DSN is a user DSN or a System DSN?
Suresh AgrawalPresidentAuthor Commented:
System DSN
Suresh AgrawalPresidentAuthor Commented:

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,

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.