Crystal Reports DB problem

Hi experts.

I have a report, designed with crystal Reports. This report has been designed using an ODBC connection, using some tables on an ORACLE server. However I have an MS Access (.mdb) database with the same tables.
I need to change the connection at runtime, so, depending on the user's selection, the report should read either the Oracle database or the MSAccess database.....

Is this possible?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Éric MoreauSenior .Net ConsultantCommented:
I think that it is easier to change your ODBC settings than your database location.

One easy way to change your ODBC settings is to use the RegisterDatabase method (found in DAO).
Are you talking about changing the title from 'Oracle database'
to 'MSAccess database'?

Are you using the Report Design Component?

Are you asking for the VB code to change the location from Access to Oracle?

All of these are possible, but I'm not sure which one to answer.
trilloAuthor Commented:
I'm looking for the VB code to change the ODBC connection... i've been trying the whole day long without success.

I've been changing the Connect propery for the Crystal Reports control, but it just doesn't work
Do you have two ODBC DSN set up? One for Oracle and one for Access. I'll assume that this is the case. I'll also have to assume that your are using the Seagate Report Design Component inside of VB.

Say that you have an ODBC entry for Oracle called MyOra and one for Access call MyAccess.The login for both is databases is MyLogin and the password for both databases is MyPassword.

To log in to Oracle
Report.Database.Tables.Item(1).SetLogOnInfo MyOra, "", MyLogin, MyPassword

To log in to Access
Report.Database.Tables.Item(1).SetLogOnInfo MyAccess, "", MyLogin, MyPassword

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.