Solved

Seagate Crystal Report 7

Posted on 2002-07-15
6
569 Views
Last Modified: 2011-10-03
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
Comment
Question by:VICKRAM
6 Comments
 

Accepted Solution

by:
RobDillon earned 50 total points
ID: 7156560
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
 
LVL 13

Author Comment

by:VICKRAM
ID: 7156865
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
 

Expert Comment

by:RobDillon
ID: 7157232
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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 100

Expert Comment

by:mlmcc
ID: 7157674
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
 
LVL 49

Expert Comment

by:DanRollins
ID: 8012800
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
 
LVL 1

Expert Comment

by:Computer101
ID: 8096037
Comment from expert accepted as answer

Computer101
E-E Admin
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question