Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Logon problem with ADO recordset in CR8.5 and VB

Posted on 2006-04-27
Medium Priority
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
  • 5
  • 3

Expert Comment

ID: 16557702
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

ID: 16557748

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 ?



Expert Comment

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Author Comment

ID: 16563108

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

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


Expert Comment

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

Author Comment

ID: 16564988
System DSN

Author Comment

ID: 16612290

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

CetusMOD earned 0 total points
ID: 16814122
PAQed with points refunded (500)

Community Support Moderator

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Create a Windows 10 custom Image with custom task bar and custom start menu using XML for deployment.
The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Starting up a Project

571 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