Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 581
  • Last Modified:

Seagate Crystal Report 7

im using MS Access and Seagaet Crystal report 7 for report generation. i have 2 queries..please help

1)When my package is deployed, the user has the choice of setting the location to store his database file(.mdb file). While designing the report im forced to set the absolute path of the database. This means that if im going to design the report with the database at D:\Projects\Library\Data.mdb, then the .rpt
file will try to look for the .mdb file at that location.
Suppose the user chooses C:\Library\Data.mdb to store the data then how to do i set the location of the database in my report to this path at runtime?

How do i solve this ?


2)Suppose the data for a report comes from executing a sql statement at run time something similar to this sql statement..its a join of 3 tables...

"SELECT tblMemMast.MEM_NAME, tblFines_H.MEM_ID, tblFines_D.ACC_NO, tblIssues.ISSUE_DATE,
[tblReturns].[RET_DATE]-[tblFines_D].[DELAY] AS DUE_DATE, tblReturns.RET_DATE, tblFines_D.DELAY,
tblFines_D.AMOUNT, tblFines_H.FINE_ID, tblFines_H.FINE_DATE FROM (((tblFines_H INNER JOIN tblFines_D ON tblFines_H.FINE_ID = tblFines_D.FINE_ID) INNER JOIN tblReturns ON tblFines_D.RET_ID = tblReturns.RET_ID) INNER JOIN tblIssues ON tblReturns.ISSUE_ID = tblIssues.ISSUE_ID) INNER JOIN tblMemMast ON tblIssues.MEM_ID = tblMemMast.MEM_ID
WHERE (((tblFines_H.FINE_ID) = 101))"

how do i set the report data at run time in such a case? How do i design such a report first!!

vickram
0
VICKRAM
Asked:
VICKRAM
1 Solution
 
RobDillonCommented:
One way to avoid your problem is to use an ODBC connection to your database which you can set up and change at runtime using the code which follows.

Alternatively, I am aware of a method of a crystal report object which allows you to send a pre-built ADO recordset as a parameter to the report. This will only work if the field names and data types in the recordset match those in the report. Unfortunately I can't remember the method's name but it is in Crystal's help.

--Create DSN at RunTime--

Public Declare Function SQLConfigDataSource _ Lib "ODBCCP32.DLL" (ByVal hwndParent As Long, ByVal _ fRequest As Long, ByVal lpszDriver As String, ByVal _ lpszAttributes As String) As Long

Public Function CreateAccessDSN(DSNName As String, DatabaseFullPath As String) As Boolean

'PURPOSE: 'CREATES A SYSTEM DSN FOR AN ACCESS DATABASE
'PARAMETERS: 'DSNName = DSN Name
             'DatabaseFullPath = Full Path to .mdb file
'RETURNS: True if successful, false otherwise
'EXAMPLE: CreateAccessDSN "MyDSN", "C:\MyDb.mdb"

    Dim sAttributes As String
   
    'TEST TO SEE IF FILE EXISTS: YOU CAN REMOVE IF YOU
    'DON'T WANT IT
    If Dir(DatabaseFullPath) = "" Then Exit Function
   
sAttributes = "DSN=" & DSNName & Chr(0)
sAttributes = sAttributes & "DBQ=" & DatabaseFullPath & Chr(0)
CreateAccessDSN = CreateDSN("Microsoft Access Driver (*.mdb)", _
   sAttributes)

End Function
0
 
VICKRAMAuthor Commented:
hi RobDillon

following your advice i created a DSN, myDSN which pointed to D:\Library\Store.mdb
Then i launched Crystal Report then chose the Standard report from the report gallery..
Then i chose the SQL/ODBC type..and i was shown a list of tables and queries from my
access database. I chose a table and selected some fields and previewed the report..
everything ran fine :-) and i saved the report.

now i changed the database myDSN pointed to as D:\Store.mdb through the
Control Panel->ODBC Data Sources. Now i tried previewing the report and i got the
"Could not find the D:\Library\Store.mdb" error message.

how do i rectify this.

can u also help me with my second question..how can i design a report based on a query that
will be generated only at runtime?

thanks for ur reply
0
 
RobDillonCommented:
I have only used the method I described of changing the DSN at runtime in a scenario where the application was closed and then re-opened after the change. Perhaps if you close your connections to the DB through that DSN and then re-open them after the change this will solve the problem.

Insofar as your 2nd question, I was not aware that your field and table names were going to change, I incorrectly assumed it was only your 'WHERE' clause that would change. In this case you could have connected to the database at design time to place your fields and labels and then pass an ADO recordset (with matching field names and data-types) at runtime. If indeed the 'SELECT' statement in your query is dynamic, then I am unfortunately not able to help. Notice that Expert's-Exchange does have a topic dedicated to Crystal.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
mlmccCommented:
TO generate a report at runtime you might look at

http://www.experts-exchange.com/crystal/Q_20323320.html

If that helps, you should give the points to dbirdman

mlmcc
0
 
DanRollinsCommented:
Hi VICKRAM,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Accept RobDillon's comment(s) as an answer.

VICKRAM, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
0
 
Computer101Commented:
Comment from expert accepted as answer

Computer101
E-E Admin
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now