How to change Database of an Access based report

[asimasm]

Hi
I have created report in Microsoft Acccess. Normally my report and database reside in the same folder. Now i have to change that by calling the same report based on same Database that resides in another folde. Now my problem is how do i tell the report in Visual Basic application where to get data from.
I m using the folowing menthod

Dim mApp As New CRAXDDRT.Application
Dim mRep As New CRAXDDRT.Report
Set mApp = CreateObject("CrystalRuntime.Application&quot
Set mRep = mApp.OpenReport(App.Path & "\MyReportFile.rpt&quot

CRViewerControl.ReportSource = mRep
CRViewerControl.ViewReport

Now the report runs fine when the Database is in the same folder as the report. Now how do i run this report based on Database from another folder tell.

Can any body tell me how to do this. I have to change the Data source from withing my application.

Thanks

 

[synapsevampire]

I'd think that you set App.Path

-k

 

[asimasm]

i think you havent got wt i meant. wt should i set the App.Path of. I need to pass the database location to the report so that it takes data from the location. My report is based on pdbdao.dll database dll. And i cant use any DSN

 

[asimasm]

hi
I still havent got the solution to the problem. Can any body plz help me out.

 

[alhamil5]

If your report doesn't always get its data from the same source, I think you'd have to use an Active Data source when you first set up the report and then point to the desired data in your code.

I recently finished an app that does this. Users of a certain program do a data dump out of that program into a file from which my application runs a Crystal Report, and the name of the file varies each time the data dump is run. I set things up so that the program passes the data dump file name to my VB 6 executable as a command line argument, then I use the Command method in my VB code to capture the file name and store it as a variable.

One part of my code declares all the report info, then separate functions are used to get the data. Here's a link from the Crystal Knowlege Base about setting up reports with Active Data sources:
-------------------------------------------------------------------------------

http://support.crystaldecisions.com/library/kbase/articles/c2004983.asp

And here's my VB 6 code:
-------------------------------------------------------------------------------
'A Global variable is declared in Module1
'to store the PLNumber as varFolder

'Set Global varFolder = PLNumber entered as command-line argument
varFolder = Command

'***********************************************************
'SET THE REPORT OBJECT
'***********************************************************
'Here's where I declare and set report objects; my main report has 2 subreports
Set crxApplication = CreateObject("CrystalRuntime.Application&quot
Dim crxReport As CRAXDRT.Report
Set crxReport = crxApplication.OpenReport(App.Path & "\SPL.rpt&quot

Dim crxNotesreport As CRAXDRT.Report
Set crxNotesreport = crxReport.OpenSubreport("spl_notes.rpt&quot

Dim crxSubreport As CRAXDRT.Report
Set crxSubreport = crxReport.OpenSubreport("Is_Was_sub.rpt&quot

'Here's where I point to functions that fetch the data
'These functions appear near the end of this code
crxReport.Database.SetDataSource Read_Text_File_Main, 3, 1
crxNotesreport.Database.SetDataSource Read_Text_File_Notes, 3, 1
crxSubreport.Database.SetDataSource Read_Text_File_Sub, 3, 1

'**********************************************************
'SET .PDF PARAMETERS AND OPTIONS
'**********************************************************
'This report is designed for export to Adobe Acrobat
'specify pdf format for output file
crxReport.ExportOptions.FormatType = crEFTPortableDocFormat

'specify output destination as disk file
crxReport.ExportOptions.DestinationType = crEDTDiskFile

'Assign the export file name and location - exported file will
'be stored in the same folder where source data resides, and will
'be named with the same PL number as that folder + .pdf file extension
crxReport.ExportOptions.DiskFileName = varFolder & "/" & varFolder & ".pdf"

'Set option to export all pages of report to .pdf file to true
crxReport.ExportOptions.PDFExportAllPages = True

'************************************************************
'RUN THE EXPORT
'************************************************************
'Export report
crxReport.Export (True)

'************************************************************
'CLOSE THE APPLICATION WINDOW, SHUT DOWN
'************************************************************
Unload Me
End Sub

'************************************************************
'FETCH THE ACTIVE DATA SOURCES
'************************************************************
Function Read_Text_File_Main() As ADODB.Recordset
Dim rsMain As ADODB.Recordset
Set rsMain = New ADODB.Recordset
Dim connMain As ADODB.Connection
Set connMain = New ADODB.Connection
connMain.Open "DRIVER={Microsoft Text Driver (*.txt; *.csv)}" & _
";DBQ=" & App.Path & "\" & varFolder & "\" & _
";DefaultDir=" & App.Path & _
";Uid=Admin;Pwd=;"

rsMain.Open "SELECT * FROM " & App.Path & "\" & varFolder & "\spl_reportdata.csv", connMain, adOpenStatic, adLockReadOnly, adCmdText
Set Read_Text_File_Main = rsMain
Set rsMain = Nothing
Set connMain = Nothing
End Function

Function Read_Text_File_Notes() As ADODB.Recordset
Dim rsNotes As ADODB.Recordset
Set rsNotes = New ADODB.Recordset
Dim connNotes As ADODB.Connection
Set connNotes = New ADODB.Connection
connNotes.Open "DRIVER={Microsoft Text Driver (*.txt; *.csv)}" & _
";DBQ=" & App.Path & "\" & varFolder & "\" & _
";DefaultDir=" & App.Path & _
";Uid=Admin;Pwd=;"

rsNotes.Open "SELECT * FROM " & App.Path & "\" & varFolder & "\spl_notes.csv", connNotes, adOpenStatic, adLockReadOnly, adCmdText
Set Read_Text_File_Notes = rsNotes
Set rsNotes = Nothing
Set connNotes = Nothing
End Function

Function Read_Text_File_Sub() As ADODB.Recordset
Dim rsSub As ADODB.Recordset
Set rsSub = New ADODB.Recordset
Dim connSub As ADODB.Connection
Set connSub = New ADODB.Connection
connSub.Open "DRIVER={Microsoft Text Driver (*.txt; *.csv)}" & _
";DBQ=" & App.Path & "\" & varFolder & "\" & _
";DefaultDir=" & App.Path & _
";Uid=Admin;Pwd=;"

rsSub.Open "SELECT * FROM " & App.Path & "\" & varFolder & "\spl_iswas.csv", connSub, adOpenStatic, adLockReadOnly, adCmdText
Set Read_Text_File_Sub = rsSub
Set rsSub = Nothing
Set connSub = Nothing
End Function

'*******************************************************
'CLEAN UP
'*******************************************************
Private Sub Form_Unload(Cancel As Integer)
'Set the objects to 'Nothing' to remove them from memory.
'This ensures that each time you use these objects
'they are recreated and populated with new data.

Set crxReport = Nothing
Set crxNotes = Nothing
Set crxSubreport = Nothing
Set objectvariable = Nothing

End Sub