• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5133
  • Last Modified:

CR 8.5 / VB 6.0 / ODBC dsn ; Change Server/database at run time ; URGENT!!!!!!!!!!

Hello all,
I have created few reports using CR 8.5. I have used ODBC dsn file to connect to the database (SQL Server). There is a procedure , that takes an input parameter. I am executing it and filling the report. everythings working fine.
Now I need to change the server/database, by pointing to a different dsn file. The new database will have the same procedure in it. Heres what I am doing..

==================================================================================
     Dim Report As CRAXDRT.Report
     Dim RepAppl As New CRAXDRT.Application

    Set Report = RepAppl.OpenReport(App.Path & "/CycleCount_Report" & reportIndex & ".rpt", 1)
    Report.DiscardSavedData

    Dim i As Integer
    For i = 1 To Report.Database.Tables.Count
        Report.Database.Tables.Item(i).SetLogOnInfo App.Path & "/App2_PICS.dsn", "PICS"    ' App2_PICS is my new dsn file
    Next i

    With Report
        .ParameterFields.Item(1).ClearCurrentValueAndRange
        .EnableParameterPrompting = False
        .ParameterFields.Item(1).AddCurrentValue (reportIndex)
    End With

    CRViewer1.ReportSource = Report
    CRViewer1.ViewReport

==================================================================================

But unfortunately it is still using the older connection to the older database. I even deleted the older dsn file.

Any help will be appreciated  !!!!

-Baan
0
DotNetLover_Baan
Asked:
DotNetLover_Baan
  • 3
  • 2
1 Solution
 
vidruCommented:
Put in a Debug.Print line where you're looping to set the login to have a look at the Location property of each table.  It could be using the schema pointing to the old db (e.g. Database1.dbo.ProcName instead of Database2.dbo.ProcName).

For i = 1 To Report.Database.Tables.Count
   With Report.Database.Tables.Item(i)
      .SetLogOnInfo App.Path & "/App2_PICS.dsn", "PICS"    ' App2_PICS is my new dsn file
      Debug.Print .Location
      'I don't use File DSN's, but this next line of code works 99% of the time to 'lose' the schema
      .Location = .Name
   End With
Next i
     
-dave
0
 
DotNetLover_BaanAuthor Commented:
Alright.. I figured that out already. But thanks for your help any way...

I think it is very important...... Heres what I should be doing....

    For i = 1 To Report.Database.Tables.Count
        Report.Database.Tables.Item(i).SetLogOnInfo "MyNewDataBase",App.Path & "/App2_PICS.dsn"   ' App2_PICS.dsn is my new dsn file
    Next i

SetLogOnInfo  takes two required parametrs and two optional. they are as follows....
     SetLogOnInfo (ServerName As String, DatabaseName As String, UserID As String, Password As String)

when using ODBC (by a dsn file) ,
    ServerName = "Any name you want to give to the new server" .... (I am using "MyNewDataBase")
    DatabaseName= "Filepath to the dsn file".... (I am using App.Path & "/App2_PICS.dsn")
    UserId = "your user name"      ----- Can leave it blank when using windows authentication
    Password="your password"     ----- Can leave it blank when using windows authentication

Thanks to all....

-Baan
0
 
vidruCommented:
I just ran a couple of tests.  If you have the DSN set up with the correct database, then leave the DatabaseName parameter as an empty string - it'll read from the DSN.  If not, the the DatabaseName should be the name of the database, not the name of the DSN:

Report.Database.Tables.Item(i).SetLogOnInfo App.Path & "/App2_PICS.dsn", ""

-dave
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
DotNetLover_BaanAuthor Commented:
Hi vidru,
can you please explain that issue to me ? Sounds like something important.

FYI, I did try that...
Report.Database.Tables.Item(i).SetLogOnInfo App.Path & "/App2_PICS.dsn", ""

But still it was reading from the older connection. The bitter part is, I already deleted the older dsn file, so that the report can not use it at run time. I guess it has embaded the connection in it some how. I will do some research on it.

Anyway, I want to understand your point. Please explain...

-Baan
0
 
vidruCommented:
0
 
OzzModCommented:
Closed, 500 points refunded.
OzzMod
Community Support Moderator (Graveyard shift)
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now