asked on

How connect to database without DSN?

I have written many applications in vb 6 that produce reports in cr 9. Each time I use a DSN to connect to the MS SQL Server database both in my vb 6 applications and in the cr 9 reports. All the cr reports are imported into vb 6. That always works just fine.

Now, however, my company is asking me to not use DSNs any more and to, instead, use connection strings. So in my vb 6 code I now use:

cnOCCS_Job_Request.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=OCCS_Job_Request;Data Source=LVXS7601"

instead of:

cnOCCS_Job_Request.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=DSN-OCCS_Job_Request"

That method works fine in vb 6. It also works fine in cr 9 when I create and run the report from within the cr developement environment. But when I import the cr report into vb 6 and run it, I get the following error message: "Crystal Reports Viewer Logon failed. Details: ADO Error Code: xxxx Source: Microsoft OLE DB Provider for SQL Server. Description: Login failed for user 'sa'...."

I have tried in cr 9 using both the ODBC (RDO) and the OLE DB (ADO) connection methods, but I can't figure out how to get them to work, i.e. to successfully run in my vb 6 application.

Thank you for the help.

janmarini

I'm thinking that you probably have to log in to each of the tables in your report (and any subreports). I have code to do this for VB.Net - but it's a little different for VB6 which I haven't used in years, but the concept's pretty much the same - just different syntax.

Here's a link to a similar question that might help you:

https://www.experts-exchange.com/questions/21587941/Crystal-Report-11-not-changing-the-login-when-running-from-Vb6.html?query=vb6+login+table&topics=40

Here's a code snippet for VB.Net
'Logs into the tables in the report
crReportDocument = rptReport
crDataBase = crReportDocument.Database
crTables = crDataBase.Tables
For Each crTable In crTables
With crConnInfo
.ServerName = strServer
.DatabaseName = strDBase
.UserID = ConfigurationSettings.AppSettings("strUID")
.Password = ConfigurationSettings.AppSettings("strPWD")
End With

crTableLogonInfo = crTable.LogOnInfo
crTableLogonInfo.ConnectionInfo = crConnInfo
crTable.ApplyLogOnInfo(crTableLogonInfo)
crTable.Location = strDBase & ".dbo." & crTable.Name
crTable.ApplyLogOnInfo(crTableLogonInfo)
Next

'Logs into the tables in the Sub-reports
crSections = crReportDocument.ReportDefinition.Sections
For Each crSection In crSections
crReportObjects = crSection.ReportObjects
For Each crReportObject In crReportObjects
If crReportObject.Kind = ReportObjectKind.SubreportObject Then
crSubreportObject = CType(crReportObject, SubreportObject)
subRepDoc = crSubreportObject.OpenSubreport(crSubreportObject.SubreportName)
crDataBase = subRepDoc.Database
crTables = crDataBase.Tables
For Each crTable In crTables
With crConnInfo
.ServerName = strServer
.DatabaseName = strDBase
.UserID = ConfigurationSettings.AppSettings("strUID")
.Password = ConfigurationSettings.AppSettings("strPWD")
End With
crTableLogonInfo = crTable.LogOnInfo
crTableLogonInfo.ConnectionInfo = crConnInfo
crTable.ApplyLogOnInfo(crTableLogonInfo)
crTable.Location = strDBase & ".dbo." & crTable.Name
Next
End If
Next
Next

Mike McCracken

Are you building your reports against the prod database or a development one?

Jan's code is pretty good.

mlmcc

EYoung

ASKER

I am building it against a production database.

Why do I have to log into each table in vb6?

Thanks for the help.

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

EYoung

ASKER

Thanks everyone for the help.

Can you also look at this question:
https://www.experts-exchange.com/questions/21797586/Export-Crystal-Report-9-report-to-TIFF-format.html

Thanks

Mike McCracken

Gald i could help

mlmcc