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.
LVL 7
EYoungAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

janmariniCommented:


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:  

http://www.experts-exchange.com/Databases/Crystal_Reports/Q_21587941.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

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

Jan's code is pretty good.

mlmcc
0
EYoungAuthor Commented:
I am building it against a production database.

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

Thanks for the help.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

mlmccCommented:
If you use OLE connection and Windows Authentication for the database you won't have to log in.  You will have to put all the Windows logins into a databse group and give permissions to the group.

mlmcc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EYoungAuthor Commented:
Thanks everyone for the help.

Can you also look at this question:
http://www.experts-exchange.com/Databases/Crystal_Reports/Q_21797586.html

Thanks
0
mlmccCommented:
Gald i could help

mlmcc
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.