• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 589
  • Last Modified:

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
0
imarshad
Asked:
imarshad
  • 3
  • 3
  • 2
1 Solution
 
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
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now