'create a reportdocument and load a reportfilename into it; the filenames are in a listbox 'populated(from) 'reportsOriginal' folder. 'When done, Copy (using saveas method) to 'reportsFinished' folder. 'But how do we Programmatically change the datasource from ODBC (RDO) to OLE DB (ADO)? 'We Can determine if it is RDO, but cannot figure out how to change it. Dim ciReportConnection As New ConnectionInfo Dim rptServerName = "" Dim fname As String = "" Dim OutPutDir As String = "" Dim knt As Integer = 0 Const cstAdo As String = "OLE DB (ADO)" 'populate using controls on the form With ciReportConnection 'First attempt to change the report to ADO 'This causes no error in the report but doesn't work .AllowCustomConnection = True 'First Attempt 'If the report was RDO, this causes the report to have no datasource. but causes no error if the report was ADO anyway. ''.Attributes.Collection.Add(New NameValuePair2("QE_DatabaseType", cstAdo)) 'second attempt-worthless too '.LogonProperties.Add(New NameValuePair2("QE_DatabaseType", cstAdo)) .Type = ConnectionInfoType.CRQE .ServerName = cboServer.Text .DatabaseName = txtDatabase.Text .UserID = txtuserId.Text 'note The only way to save a password in a report anyway is to use an odbc connection with a saved password. ' But just for fun include password. It is best to use integrated security if possible. .Password = txtPassword.Text .IntegratedSecurity = chkIntegrated.Checked End With Try For Each item As String In lstReports.SelectedItems Dim str() As String str = Split(item, "\") fname = str(UBound(str)) Dim crReportDocument As New ReportDocument crReportDocument.Load(item) 'another attempt to set everything to ADO ciReportConnection.LogonProperties.Add(New NameValuePair2("QE_DatabaseType", "OLE DB (ADO)")) If crReportDocument.DataSourceConnections.Count > 0 Then rptServerName = crReportDocument.DataSourceConnections(0).ServerName 'this at least alerts us if the report is RDO MessageBox.Show(GetRdoReportName(crReportDocument, fname)) End If rptServerName = UCase(rptServerName) lblReportCopy.Text = "Currently Working on " & fname 'Assign data source details to tables if original file servername doesn't match the desired value If rptServerName <> "" And rptServerName <> UCase(cboServer.Text) Then ' (copied from stackoverflow) The meat of the program is right here; this is what changes the logon info For Each table As Table In crReportDocument.Database.Tables table.LogOnInfo.ConnectionInfo = ciReportConnection table.ApplyLogOnInfo(table.LogOnInfo) Next For Each subrep As ReportDocument In crReportDocument.Subreports For Each table As Table In subrep.Database.Tables table.LogOnInfo.ConnectionInfo = ciReportConnection table.ApplyLogOnInfo(table.LogOnInfo) Next Next End If crReportDocument.Refresh() 'This shows a logon error for any originally (and unchanged) RDO report, but no error for an originally ADO report. 'crReportDocument.VerifyDatabase() crReportDocument.Refresh() OutPutDir = Application.StartupPath + "\ReportsFinished\" 'all 'ADO' changes seem to be lost when saved. Maybe they work when using a crystal viewer crReportDocument.SaveAs(OutPutDir + fname) knt += 1 Next Catch ex As Exception lstErrors.Items.Add(ex.Message) End Try MessageBox.Show(knt & " New files created in " & OutPutDir) End Sub Private Function GetRdoReportName(ByRef CrReportDocument As ReportDocument, ByVal fname As String) As String Const cstAdo As String = "OLE DB (ADO)" Dim test As ArrayList = CrReportDocument.DataSourceConnections(0).Attributes.Collection Dim i As Integer Dim Substr As String For i = 0 To test.Count - 1 If UCase(test(i).name) = "QE_DATABASETYPE" Then Substr = (test(i).value) If InStr(UCase(Substr), "RDO") > 0 Then 'Third attempt to change the report to ADO 'code below causes no error but does not change the report to ado.. test(i).value = cstado Return fname End If End If Next Return "" End Function
From novice to tech pro — start learning today.