Setting database path at Run-Time for Crystal Report

[Shafan]

Hi,
I've designed a report based on a ADO database using a connection string. While building the connection string I pointed out the database from which the data is extracted.
The report runs on the development machine no problems, but when I try to run it on a deployment machine I get a Database Error:
"[Microsoft][ODBC Microsoft Access Driver] '(Unknown)' is not a valid path. Make sure the path name is spelled correctly and that you are connected to the server on which the file resides".

I assume that the report tries to look for the database where it was at the design stage.

My question is: how can I set the database's path at run time?

[TonySaliba]

Note that I have a similar thread on this, I spent the whole weekend working on this one. The only way I found that it works always is if I use ODBC. Also if I change the DNS name from that of used by the crystal reports designer I get also into problems.

Still trying to find a solution but it seems that there is some trick somewhere. As a test I created a form with DatabaseName, ServerName, UserId and Password but could not make the system to work always. One of the major issues is the Password authentication with Access. I also noticed that with Crystal ver 9 there are more properties that one can use and possibly making the system more consistent.

[rodrog]

'Try this code: (Working with CR 8.5 and Access 2000)

Public reporte As craxdrt.Report 'Need Crystal Report Viewer Control component installed

'In Form code:

Private Sub ImprimeInforme()
Dim CRXTables As craxdrt.DatabaseTables
Dim CRXTable As craxdrt.DatabaseTable
Set reporte = crxapplication.OpenReport("file.rpt") ' Name of report file

For Each CRXTable In reporte.Database.Tables
CRXTable.Location = App.Path & "\database.mdb" 'Location and name of .mdb
next
frmReportView.show ' Form to show report
end sub

'I hope this can help you..

[RichardAtherton]

Okay well first of all I am going to point you to a url which is www.crystalreportsbook.com

and secondly ask you this.

When you designed your reports, did yoou chooose an ADO OLEDB connection onject and then connect to your already built database.

If so wrong, that was my mistake, it embeds in the designer the physical path of the databse and it will only ever take data from there and yes will fail in execution.

Chapter 14, specifically deals with this problem on the book of the URL, I gave you

Sometimes the terminology can be confsuing because what you actuall need to do, is creat an XML datset which contains the structure of your data and not the actual data itself.

It's called whether you use the
'push' or 'pull' data method.

Believe me I have spent weeks, possibly months on this problem and finally got it right.

In summary,ifyou wish to produce reports that are independent of method the default for the designer, no the ways it expects you to build the reports is the pull methodwhere the actual data is , you have to use the 'push.

Just a pointer here in the right direction, reply to this and I if necessary I will give more specifics.

I spent 2 months not full time but 2 months getting this to work the way I wanted to and now have to redo 30 reports because as soon as change my datasource in the designer , I lose all the datafields on the rreports.

[RichardAtherton]

Okay well first of all I am going to point you to a url which is www.crystalreportsbook.com

and secondly ask you this.

When you designed your reports, did yoou chooose an ADO OLEDB connection onject and then connect to your already built database.

If so wrong, that was my mistake, it embeds in the designer the physical path of the databse and it will only ever take data from there and yes will fail in execution.

Chapter 14, specifically deals with this problem on the book of the URL, I gave you

Sometimes the terminology can be confsuing because what you actuall need to do, is creat an XML datset which contains the structure of your data and not the actual data itself.

It's called whether you use the
'push' or 'pull' data method.

Believe me I have spent weeks, possibly months on this problem and finally got it right.

In summary,ifyou wish to produce reports that are independent of method the default for the designer, no the ways it expects you to build the reports is the pull methodwhere the actual data is , you have to use the 'push.

Just a pointer here in the right direction, reply to this and I if necessary I will give more specifics.

I spent 2 months not full time but 2 months getting this to work the way I wanted to and now have to redo 30 reports because as soon as change my datasource in the designer , I lose all the datafields on the rreports.

[Shafan]

Hi Richard,
First of all, thanks for your detailed answer.
I've read the 14th chapter but couldn't find the 'ServerName' property used to set an Access database which is the one I use (maybe because I use Crystal Reports 8.5 and not NET).
I've tried the following code but no joy:
For Each tbl In rpt.Database.Tables
tbl.Location = DBPath
Next tbl

I'm yet to try the XML datasets. I'll give it a go if I can't solve the problem in any other way.

[RichardAtherton]

No its there, for access the server name is the database path

[Shafan]

I did a search in the object browser on all libraries but couldn't find 'ServerName' anywhere.

Also - when creating a report and choosing the data source (data explorer), where do I select the XML dataset?

Thanx mate

[RichardAtherton]

follow the example in the book, but basically it is project:

add new item

select dataset and add to your project

[Shafan]

I've followed the book's example on how to create the XML file using the IDE, but I can't find the right service through which I can point to my Access database.

Also, I've manually created the XSD file and tried to point to it in the Data Explorer but in which section? The 'Metadata' and 'More Data sources' didn't have an XSD option neither did I find it anywhere else.
Any Ideas?

Thanx