Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Changing Connection - Visual Basic 6 - Using Connection String

Posted on 2004-11-29
13
Medium Priority
?
2,815 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:SaxonWica
  • 6
  • 4
  • 3
13 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 12698894
Not unless you build the report to use an ADO-type connection.

mlmcc
0
 
LVL 5

Author Comment

by:SaxonWica
ID: 12704681
Yes, I built the report using ADO type connection. But how do I change the connection string from Visual basic?
0
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 450 total points
ID: 12712461
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 5

Author Comment

by:SaxonWica
ID: 12716342
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
 
LVL 4

Accepted Solution

by:
kssaran earned 750 total points
ID: 12722614
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
 
LVL 5

Author Comment

by:SaxonWica
ID: 12735892
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
 
LVL 5

Author Comment

by:SaxonWica
ID: 12737178
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
 
LVL 4

Expert Comment

by:kssaran
ID: 12743102
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
 
LVL 5

Author Comment

by:SaxonWica
ID: 12744821
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 12746425
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
 
LVL 5

Author Comment

by:SaxonWica
ID: 12749261
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 12749430
Glad i could help

mlmcc
0
 
LVL 4

Expert Comment

by:kssaran
ID: 12750744
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

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question