Logon Problems w/ CR9 Report Using Access2K Query 1

[theseus1976]

If it's not one thing, it's another...

OK, I've got a CR9 report that uses an Access 2000 query as a table (what CR9 refers to as a View). The report itself runs fine (w/ manual password entry), but I can't seem to get everything straightened out in VB6. :/

Background:

Using VB6 (version 8176) and CR9 Professional (9.2.3.970)
VB6 Project has the following References:

VBA
VB Runtime Objects & Procedures
VB Objects & Procedures
OLE Automation
CR Export 9
CR Viewer Control 9
CR 9 ActiveX Designer Runtime Library
CR SmartTag 9 Type Library
CR Viewer Control 9 (second instance)
MS ActiveX Data Objects 2.5 Library

CR9 Report has one database: the View (qryAttended) from Access 2000 mdb (Groups.mdb)

qryAttended pulls from one local table (tblAttended) and 7 linked DB2 tables (dbo_B_JOB, dbo_T_STAFF, dbo_T_PERSON, dbo_T_PAT, dbo_B_INT, dbo_IB_OBJ_INT, and dbo_B_OBJ).

Both the Access 2K and DB2 databases are password protected. What I'd like to do is programmatically run the report _without_ having to prompt the user to login to EITHER database.

My VB6 CR9 Viewer form has the following code:

Dim Appl As New CRAXDRT.Application
Dim Report As New CRAXDRT.Report

Sub Form_Load()
   Set Report = Appl.OpenReport(App.Path & "\Outcomes.rpt")

' I expect the logon info to go here

   CRViewer91.ReportSource = Report
   CRViewer91.ViewReport
End Sub

I've tried searching through the forums to no avail. Is what I want to do possible? If so, how would I do that?

MANY thanks in advance! Let me know if I need to further clarify anything.

-ej

 

[vidru]

Not sure how you'd handle the connection to the DB2 database, but that might not matter, since you're connecting to Access to run the query. If you're using a native connection, try this:

Dim ConnectionInfo As CRAXDRT.ConnectionProperties

Set ConnectionInfo = Report.Database.Tables(1).ConnectionProperties

'You can probably comment out the next line unless 
'you need to change the path to the db at runtime

ConnectionInfo.Item("Database Name") = "C:\PathToDatabase\DB_Name.mdb"

ConnectionInfo.Item("Database Password") = "Password"

'Set the session level user and password
ConnectionInfo.Item("Session UserID") = "User"
ConnectionInfo.Item("Session Password") = "Password"

If you're not using a native connection, there's a pdf on ConnectionProperties on the Business Objects site that will give you the info for ODBC and OLEDB as well:

http://support.businessobjects.com/communityCS/TechnicalPapers/cr_rdc9_connectionproperties.pdf.asp

-dave

 

[theseus1976]

vidru,

Thanks, that DOES at least let me connect and run the report, but I'm still getting prompted for the connection info to the DB2 tables...

Strange, the System DSN connecting to the DB2 tables says it's an SQL Server connection... :/ Ok, after some investigation it turns out it IS an SQL Server connection -- there also exists DB2 connections, but apparently they aren't being used in this report... (I _love_ how they tell the report writers everything they need to know up front!)

Now that I've got everything straight (if you can't tell, I was simply handed this project mid-stream), I need to know how to programmatically set the password for the prompt to connect to the SQL Server database. This is happening AFTER I've connected to the Access View for the report. (The View itself is needing the connection info to the SQL Server since it's using the linked tables.) There is an existing System DSN with all the connection info (except password) if that helps/can be used.

Any ideas? Thanks again for all the help!

-ej

 

[vidru]

The only way I could get it to work was by using the method described here unter the section heading Creating the SQL Pass-Through Query:

http://support.microsoft.com/?kbid=303968

Essentially, when you set up the query in Access, if you set it up as a Pass-Through, you have the option to have it save the username and password with the connection. If I did that, I was able to bring up the report without any trouble.

-dave

 

[theseus1976]

Thank you! That worked like I wanted it to!

Quick question, though... Before it seemed to run fairly quickly and now it's slowed down considerably... Is that due to the pass-through query?

I tried multiple variations on the theme: creating one pass-through query for each SQL Server table, then using those queries instead of the linked tables; using just one pass-through query and the rest linked tables...

The pass-through queries themselves run very quickly, but including them in a select query seems to at least triple the amount of time it takes to return records. Using the linked tables only, I don't have to wait as long... Linked table query takes 7 seconds... Include ONE pass-through query and that jumps to 21-25 seconds... Using ALL pass-through queries (except for the one local Access table) takes 45 seconds...

I have read-only access to the SQL Server. (Otherwise, I think I could make a heterogeneous query on the SQL Server side -- linking the Access table -- that would run a lot quicker.) So, given that restriction, is the 21-25 seconds the best I can hope for?

Thanks again for all your help!

-ej