Changed datasource to OLEDB at runtime

[chriscboy]

I'm using Crystal XI and Visual FoxPro 7. We have a number of crystal reports that were written

in 8.5 and have been upgraded to XI. Some of the reports using ODBC and some use OLEDB. What I

want to do at run time is to change report to use OLEDB to connect to the vFP database. I have

tried myself but whenever the report is run I get messagebox like the following:

Crystal Reports ActiveX Designer
================================
Prompting failed with the following error message:".
Error source: Error code: 0x80004005

Below is my code that I am using. What am i doing wrong?

* Create a reference to the report
Thisform.oApplication = _Screen.oCrystal
Thisform.oReport = Thisform.oApplication.OpenReport(cReportName)

* Log on to the database server
cDSN = "Provider=vfpoledb.1;Data Source=\\fs01\emsdemo\databases\live\ems.dbc;Collating

Sequence=machine;"
Thisform.cDSN = cDSN
Thisform.oApplication.SetMatchLogonInfo(.T.) && This will set database

location for sub-reports

This.Reportfilename = cReportName

* Create a reference to the database object in the report
oDB = Thisform.oReport.Database()

* Get a references to the DatabaseTables collection
ocDBT = oDB.Tables()

* Set the database location for each of the tables in the report
For nCount = 1 To ocDBT.Count

oDBT = ocDBT.Item(nCount)
oDBT.SetLogOnInfo(cDSN)

* Delete connection properties for table and add OLEDB settings
oDBT.DllName = "crdb_odbc.dll"
With oDBT.ConnectionProperties
.DeleteAll()
.Add("Provider","vfpoledb.1")
.Add("Data source","\\fs01\emsdemo\databases\live\ems.dbc")
.Add("Database Type","OLE DB (ADO)")
.Add("User ID","")
.Add("Use DNS Default Properties","FALSE")
.Add("Locale Identifier","1033")
.Add("OLD DB Services","-5")
.Add("Collating Sequence","Machine")
.Add("DSN","")
EndWith
*!* For Each CPProperty In oDBT.ConnectionProperties
*!* MessageBox (Transform(CPProperty.Name)+"="+Transform(CPProperty.Value))
*!* EndFor

EndFor
oDB = .NULL.
ocDBT = .NULL.
oConnection = .NULL.

* Loop round each sections
oSections = Thisform.oReport.Sections
For Each oSection In oSections

oObjects = oSection.ReportObjects

For nCount = 1 To oObjects.Count

* If the object is a sub-report, open it
If oObjects.Item(nCount).Kind = 5 &&

crSubreportObject

oSubReportObject = oObjects.Item(nCount)
oSubReport = oSubReportObject.OpenSubreport
oDB = oSubReport.Database

* Get a references to the DatabaseTables collection
ocDBT = oDB.Tables()
* Set the database location for each of the tables in the sub-report
For nCount2 = 1 To ocDBT.Count
oDBT = ocDBT.Item(nCount2)
oDBT.SetLogOnInfo(cDSN)

oDBT.DllName = "crdb_odbc.dll"
With oDBT.ConnectionProperties
.DeleteAll()
.Add("Provider","vfpoledb.1")
.Add("Data

source","\\fs01\emsdemo\databases\live\ems.dbc")
.Add("Database Type","OLE DB (ADO)")
.Add("User ID","")
.Add("Use DNS Default Properties","FALSE")
.Add("Locale Identifier","1033")
.Add("OLD DB Services","-5")
.Add("Collating Sequence","Machine")
.Add("DSN","")
EndWith

EndFor
oDBT = .NULL.
ocDBT = .NULL.
oDB = .NULL.

If oSubReport.HasSavedData()
oSubReport.DiscardSavedData()
EndIf

oSubReport = .NULL.
oSubReportObject = .NULL.

EndIf

Next

Next

* Now open report