Solved

Seagate Crystal Report 7

Posted on 2002-07-15
6
561 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
Comment Utility
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
Comment Utility
Comment from expert accepted as answer

Computer101
E-E Admin
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now