Changing Connection - Visual Basic 6 - Using Connection String

Hello there,

I'm currently using Crystal Report 9 Enterprise.

I've seen the previous topics about changing connection programatically using DSN / ODBC. What I want to ask if it is possible to use standard connection string from Visual basic 6 ?

For example, I have created a report using my local SQL 2000 server named SERVER-A. Now I'd like to deploy my report to other computer, which use another SQL 2000 server named SERVER-B with similar database. And I use datalink to set visual basic connection string. Is it possible for Visual Basic to transfer its connection string to Crystal Report ?

I have also tried to make a report using a datalink file, it works perfectly fine, but I encountered some 'annoying' problem. I found that Crystal report is unable to use Datalink file in relative path. How to change the datalink path of the report from Visual Basic ?

Thank you for your kind and helpful answers.

Regards

Saxon Wica
LVL 5
SaxonWicaAsked:
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.

mlmccCommented:
Not unless you build the report to use an ADO-type connection.

mlmcc
0
SaxonWicaAuthor Commented:
Yes, I built the report using ADO type connection. But how do I change the connection string from Visual basic?
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

SaxonWicaAuthor Commented:
I've checked your given link, but it seems that it uses insertable designer component in Visual Basic 6, not from a separate rpt files. Note that this feature only available in version 8.5, not in version 9 (As far as I know, please correct me if I am wrong).

Thank you very much.

Regards
0
kssaranCommented:
Hi Saxon.

My first suggestion to you would be why dont you consider changing the connection method of Crystal report to OLEDB? That way you can simply pass the connection string without any problem like

rptAuthSellduty.Connect = strGlobalConnectionString

Where strGlobalConnectionString is
strGlobalConnectionString  "Provider=SQLOLEDB;Server=Sever;Database=DB1;UID=username;PWD=password; and is definited at the Global module level.

Normally those above mentioned DatabaseName, User Id and password are not hardcoded like that. We stored all that in a INI File with Encryped manner. Later we just fetch using a seperate function to read the file and decrypt then assign the values to strGlobalConnectionString. This is the best suggested method.

If you still wanna go by your method also you can proceed that way...

  cn.open "file name=someudl.udl" 'Opening the connection based on UDL File

   strSQL = "..." ' you can mention the same SQL what you've used to create the report
   Set rs = New ADODB.Recordset
   rs.Open strSQL, cn, adOpenKeyset  'Assigning result to a recordset
   sReportName = "abc.rpt" 'Specify your report name here
   Set CrxReport = CrxApplication.OpenReport(sReportName)
   
   CrxReport.DiscardSavedData
   CrxReport.Database.SetDataSource rs, , 1 'This is where i am telling that the source is this record set as a result it will use record set
   CRViewer1.ReportSource = CrxReport
   CRViewer1.DisplayGroupTree = False
   CRViewer1.ViewReport

Any further query? Post back here...

Thanks & Rgds
Sara
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
SaxonWicaAuthor Commented:
Dear Sara,

Your code almost meet my expected result, but with one exception.

What if I just want to pass the connection string, not the recordset?

By using your code, if I did some changes on the report (e.g. adding some more table), I think I must change the recordset from VB too. Isn't that correct?


Thank you very much
0
SaxonWicaAuthor Commented:
http://support.businessobjects.com/communityCS/TechnicalPapers/cr_rdc9_connectionproperties.pdf.asp?ref=devzone_comaspzone_main


I have just found the link, and it also helps a lot

'Declare a Connection Info Object
Dim ConnectionInfo As CRAXDRT.ConnectionProperties

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

'Set the OLE DB Provider
ConnectionInfo.Item(“Provider”) = “SQLOLEDB”

'Set the physical server name
ConnectionInfo.Item(“Data Source”) = “Server name”

'Set the database name
ConnectionInfo.Item(“Initial Catalog”) = “Database name”

'Set the integrated security
ConnectionInfo.Item(“Integrated Security”) = True

'Set the user name
ConnectionInfo.Item(“User ID”) = “User name”

'Set the password
ConnectionInfo.Item(“Password”) = “Password”

'Set the fully qualified table name if different from
‘the original data source
Report.Database.Tables(1).Location = “database.owner.tablename”




But it still doesn't answer my question if I just want to use a connection string, for example :
"Data Source=eiffel; Initial Catalog=SmartCard; Integrated Security=SSPI"

Thank you very much

Regards
0
kssaranCommented:
What I was telling you is that, Instead of the database connection and fectch the records from database you can directly assign the recordset results to the report and run it.

Rgds
Sara
0
SaxonWicaAuthor Commented:
Well, it worked, but if I change any design from the report (e.g. adding more tables) means I have to change the recordset results too, right? That is what I am trying to prevent.

I just want to change the report only, but nothing to do with the coding from visual basic, which means I only need the connection string. Is there a way to do this?

Thank you
0
mlmccCommented:
Unfortunately, when you change the the connection string, you also have to reset all information for the database and tables.  Thus if your change the design (add a table, delete a table) the application will have to reflect the change.

I was able to avoid this problem by creating a small development LAN that we setup identically to the production LAN.  Server is named the same, drives are mapped the same, reports are in the same directories, etc.  We also use integrated securty & TRUSTED CONNECTIONS which eliminates the need to log into the database.  If you can get the dev environment to be the same as the production environment (this does require a separate LAN) then you can change the reports any way you wish and the users never see a problem.

mlmcc
0
SaxonWicaAuthor Commented:
Dear mlmcc,

I think that's also the only way to do it. Thank you for answering my questions. I think I have to split the points, since you both really helped me a lot. I hope you two don't mind

Regards
0
mlmccCommented:
Glad i could help

mlmcc
0
kssaranCommented:
Its our pleasure to help each other. I'll do more R&D if there is anything that i got, I shall post it.

Rgds
Sara
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.