Shifting backend database from Access to Oracle...

Hi all,
       I have developed an application in VB6 with MS Access 2003 database and Crystal Reports (10). Now according to the new requirements of the client we have to shift the backend database from Access to Oracle. It is almost done successfully except the Crystal Reports.

Since the database was entirely shifted from Access to Oracle (With the same table structure) so converting the connection from Access to Oracle was simple. What I did was to "Set Datasource Location" | OLE DB (ADO) | "Microsoft OLE DB Provider for Oracle" and then providing the user name and password etc. and updating the tables in the report. Now in the Crystal Reports application my reports were opening and functional.

The problem I am facing is that when I try to open the reports from my VB6 application I get the following error

**********************************************
Crystal Report Viewer
Logon Failed
Details: ADO Error Code 0x80040e4d
Source Microsoft OLE DB provider for Oracle
Description: ORA-01017: invalid username/password;
Native Error 1017
**********************************************

Here is how I open the reports in my Vb code

Private Sub mnuGFull_Click()

Dim crxApplication As New CRAXDRT.Application
Dim CrxReport As CRAXDRT.Report
DimCrxSubreport As CRAXDRT.Report

reportfile = "C:\Program Files\XXX.rpt"
Set CrxReport = crxApplication.OpenReport(reportfile)

frmReport.CRViewer91.ReportSource = CrxReport
frmReport.CRViewer91.ViewReport
frmReport.CRViewer91.Zoom 125
frmReport.Show vbModal, Form1

End Sub


Now my question is shouldn't the User name and Password be saved with the Report( .rpt file)? If not then how can I provide the login information from my VB6 code?
 
Imran
LVL 13
imarshadAsked:
Who is Participating?
 
frodomanCommented:
You'll still need to pass logon credentials to Oracle.  I believe it's along these lines but I've been using .net for so long I may be a little off...

Dim crtable As CRAXDRT.DatabaseTable
For Each crtable In CrxReport.Database.Tables
  ctable.ConnectionProperties("user id") = "xxx"
  crtable.ConnectionProperties("password") = "xxx"
Next crtable
0
 
mlmccCommented:
That looks right but we used Windows authentication to the database and were able to avoid that problem.

mlmcc
0
 
imarshadAuthor Commented:
OK I am going to try these.....How can I use Windows Authentication to the database?

Imran
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
imarshadAuthor Commented:
frodoman your suggestion is working..... I will finalize the question after I get some response for the Windows Authentication method.....

Imran
0
 
frodomanCommented:
I'll let mlmcc respond - I'm not sure that actually works with Oracle, he may have been thinking from a SQL Server standpoint.

frodoman
0
 
mlmccCommented:
I missed the Oracle in the question.  As frodoman guessed we use MS SQL server and it is selected by the DBA when the database is created or SQL Server is installed.  Been too long since we did it.

I don't know if Oracle can use that method.  Check with your DBA.

mlmcc
0
 
imarshadAuthor Commented:
OK thanks for your good suggestions......

Imran
0
 
frodomanCommented:
Just to clarify, Oracle cannot use the integrated authentication model.

Glad you've got it working.
0
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.