VB6,CR9 DSN-Less Database Connection to SQL Server

[agersh]

Development Environment: WinXP, Visual Basic 6, Crystal Reports 9, SQL Server 2000

Using the following VB code I am opening a Crystal Reports .RPT report. Which I then set the required connection properties for the report. As you can see the code/report currently uses a File DSN. I would like to change this to a DSN-Less Connection. But I seem to be unable to figure out the correct CR9 code syntax for a DSN-Less connection.

Anyone know how I can accomplish this?


Set CrApplication = New Application
Set CrReport = CrApplication.OpenReport(App.Path & "\Reports\Detail.rpt", 1)

CrReport.Database.Tables(1).ConnectionProperties.Item("FileDSN&quot = "C:\Program Files\Common Files\ODBC\Data Sources\MMHS_DB.dsn"
CrReport.Database.Tables(1).ConnectionProperties.Item("database&quot = DBConnString.Database
CrReport.Database.Tables(1).ConnectionProperties.Item("password&quot = DBConnString.Password
CrReport.Database.Tables(1).ConnectionProperties.Item("user ID&quot = DBConnString.UserID

CrReport.DiscardSavedData

CrReport.SQLQueryString = "SELECT * " & _
"FROM vw_CLAIM " & _
"WHERE APPLICATION_ID = " & gAPPLICATION_ID & " AND CLAIM_ID = " & gClaimId & " " & _
"ORDER BY NAME;"

 

[vidru]

In thread766-759502, I posted a link to a .pdf that has all the info on ConnectionProperties that you need.

Have you looked at it yet?

-dave

 

[agersh]

vidru,

Yes, After reviewing the .pdf I have changed my code to the following.

Thank you for your help.


'* Set DB Connection to SQL Server (OLE DB at Runtime
' Declare a Connection Info Object
Dim ConnectionInfo As CRAXDRT.ConnectionProperties

' Set the Connection Info to Connection Properties of the table object
Set ConnectionInfo = CrReport.Database.Tables(1).ConnectionProperties

' Set the DLL name to the ODBC diver
CrReport.Database.Tables(1).DllName = "crdb_ado.dll"

' Clear the ConnectionProperties collection
ConnectionInfo.DeleteAll

' Add the OLE DB - SQL Server connection properties
ConnectionInfo.Add "Provider", "SQLOLEDB"
ConnectionInfo.Add "Data Source", DBConnString.Server
ConnectionInfo.Add "Initial Catalog", DBConnString.Database
ConnectionInfo.Add "User ID", DBConnString.UserID
ConnectionInfo.Add "Password", DBConnString.Password

'Reset declared Connection Info Object
Set ConnectionInfo = Nothing