Unable to change USER ID in Crystal 9 from VB6

[Former Member]

I have made a report(crystal 9 talking to SQL server) and when I fire it from VB6 it works fine, as long as the user ID through which the report was made is not changed. But If I have a scenario where the user ID is changing the report still looks at the original USER ID.

I have got the following code to change the connection properties from the VB6 application.

For Each CrRepTable In CrRep.Database.Tables

CrRepTable.ConnectionProperties("Data Source") = mServerName

CrRepTable.ConnectionProperties("INITIAL CATALOG") = mDatabaseName

CrRepTable.ConnectionProperties("USER ID") = mUserID

CrRepTable.ConnectionProperties("PASSWORD") = mUserPassword

Next

Now suppose the report has been made in CRW 9 with the user ID 'X'. At a user site, the database has been installed with user ID 'Y'. When the application fires the report it still tries to fire the report with the User ID 'X'.

I have been banging my head over this for a week now and have not progressed anywhere.

PLEASE HELP

Thanks

[former_member11696 · Accepted solution]

Go to this link: http://www.sdn.sap.com/irj/boc/samples

And download the RDC samples to set location. You should find you are missing one line .SetLocation to update your logon info.

Thank you

Don

[Former Member · Accepted solution]

I could not find any sample which could help me with my problem. Also I could not find the SETLOCATION command anywhere. I have checked

[former_member11696 · Accepted solution]

Well you didn't look that hard then.

Sorry the name is database.Location

Use the Object browser in VB 6 and search on location and you'll find the references.

Thank you

Don

[Former Member · Accepted solution]

Thanks DON,

I tried the LOCATION property that you mention. But this is just the table name or the procedure name that the report is using.

Let me re-iterate my problem again.

In the development area, the report is referring to an SQL server A and to a table within it which has been created with the user ID X

In the LIVE area, the report is referring to the same table. However this time the server is B and the table has been created with another user ID Y.

I have managed to change the SERVER from A to B. But not the USER ID. The report still tries to fire the Select from the table with the old User ID X.

Please help me.... I am really stuck with this.

Thanks

[Former Member · Accepted solution]

Further to my previous reply, I found something strange within Crystal Reports IDE, which I would like to share at this point

When I open a report and click on Set Datasource Location, in the Current Data source I see something like this

Report

Orchid\WISH2 (My data server)

+ Properties

+ Th03q_18;1 (My stored Procedure)

On expanding the Properties, I see the following

Database Type: OLE DB (ADO)

Data Source : Orchid\wish2

Initial Catalog : master

User ID : sa

and so on.

However when I click on the + sign next to the Stored Procedure, I see another set of Properties as follows

Table Name: Th03q_18:1

Table Type: Stored Procedure

Qualifier 1: posdb

Qualifier 2: OPM1

The 1st set of properties do get changed...from VB6 but not the 2nd set of properties.

Even from Crystal Reports, in the above scenario, when the Procedure is fired it is fired with the User ID as OPM1 and not with sa

How can I change the 2nd set of properties????? 1st from Crystal reports and then from VB6

Thank you

[Former Member]

I am surprised no one has faced this problem in CR9 and VB6.

Is nobody from the SAP-CR technical team watching the forum.

Can nobody give me a solution for this????

[former_member11696]

This is a public forum and is not monitored constantly by CR Rep's. If you need help directly you can purchase a case and have a rep work with you directly.

Search the Object Browser and SDK help files for more info also.

Or Purchase a case from: http://store.businessobjects.com/store/bobjamer/DisplayProductByTypePage&parentCategoryID=&categoryI...

Should look something like this:

With rpt.Database.Tables(1)

.ConnectionProperties("User ID").Value = "sa"

.ConnectionProperties("Password").Value = "password"

.ConnectionProperties("Data Source Name").Value = "ServerNameI"

.ConnectionProperties("Database").Value = ""

End With

Thank you

Don