Crystal reports 2016 connecting to Advantage database failing to connect to linked database

[jimcookson]

We have a 3rd party provided database - PICs - on an Advantage database engine V11.10 (32 bit) and our own database - Crystal_Reports_Pics - using the same engine.

We have readonly access to PICs and so to join with our own data, link PICs into CRP.

We used CrystalX1 until Jan this year to report using the Advantage ODBC V11.10 32 bit driver and have successfully reported using the linked database joined to our own Crystal_Reports_Pics database.

In Jan, we upgraded to Crystal Reports 2016 and using the same ODBC driver, can connect directly to both databases and extract data successfully into both Crystal Reports CrystalX1 and 2016. We can still report using the linked database through Crystal X1 but Crystal 2016 will not let us use the linked database.

Using Database Expert, we can select the tables from the link, get them into the field explorer and drop fields onto the report - but there is no data.

If we use a command and write for example SELECT * FROM PICS.TRAINEE and put this into the field explorer, we can drop the fields onto the report and the data is displayed.

The problem is that when we select the tables in the Database Expert, the text drops the "PICS." (required to select from a linked database) and so the tables cannot be seen properly.

I need to be able to tell Crystal 2016 that it needs to add the text in that tells the report that it is a linked database.

Is this possible or is Crystal Reports 2016 not capable of using linked databases (with Advantage)linked-databases.jpg?

Thanks for any help.

Jim

[jimcookson · Accepted solution]

I had a resolution for this from Rebecca in a company called DSCallards (dscallards.com) who we deal with. Rebecca told me:

once you have selected the tables and linked them, when in design before running the report and adding any fields to crystal reports, you could try the following:

go to database -> set data source location

on each of the tables selected open the properties up and you can set the Overidden Qualifies Table Name, to be the table name and schema if its needed.

So in my case, the database was called pics and the tables I wanted were called trainee and epidata, then in the Overidden Qualifies Table Name, I entered PICS.trainee and for the other one PICS.epidata.

The solution worked.

[joachim_drr]

Easiest: Use Views

create view TRAINEE as SELECT * FROM PICS.TRAINEE

[jimcookson]

Thanks Joachim.

I was aware that I could use views and if there is no other way then we will have to create views for all the tables and re-join the reports. I was just hoping there might be a setting in Crystal Reports 2016 that allowed us to carry on using the reports the same way that we did with CrystalX1 - you would hope that they wouldn't remove features in later versions 🙂

Thanks again,

Jim.