Solved

Seagate Crystal Report 7

Posted on 2002-07-15
6
575 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 101

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

734 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