Go Back  Xtreme Visual Basic Talk > Legacy Visual Basic (VB 4/5/6) > Database and Reporting > Crystal 9 and changing tables from access to SQL server


Reply
 
Thread Tools Display Modes
  #1  
Old 07-13-2004, 01:02 AM
kesho kesho is offline
Regular
 
Join Date: Jul 2003
Posts: 79
Default Crystal 9 and changing tables from access to SQL server


How do I go about changing a table in a crystal 9 report to point to a database on SQLServer rather than a Access MDB file, and vice versa.

I nead to be able to switch between these 2 types of database so just changing them on the report is not a option. I have to be able to switch at runtime.

The problem I have encountered is that the connection properties differ depending on which type of database it is, so it causes an error when you just try changing the connectionstring. I have also tryed changnig the database type but it appears to be read only.

Any help would be appreciated.
Reply With Quote
  #2  
Old 07-13-2004, 05:01 AM
MKoslof's Avatar
MKoslof MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

What data types are on this report? You realize that SQL Server and the Jet Provider analyze data differently..just making sure you understand the basics behind this. So are these two tables the same in structure (number of fields, the fields on the report that are being used).

You would have to change all the connectionProperties at run-time and the location of each CRAXDRT table.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #3  
Old 07-13-2004, 05:39 AM
kesho kesho is offline
Regular
 
Join Date: Jul 2003
Posts: 79
Default

Quote:
Originally Posted by MKoslof
You realize that SQL Server and the Jet Provider analyze data differently..
Im not sure what you mean by this.

The Tables are as close as we can make them in terms of fields and data types. The hope is that they will be interchangeable for this report between databases.

The problem is that when I try and change the connection properties to allow a connection to SQL Server it generates an error. I am assuming that this is because the access databases have different properties (i.e. calling crxTable.connectionProperty.Items("Data source") = "KESHO", raises an error).
Reply With Quote
  #4  
Old 07-13-2004, 06:18 AM
MKoslof's Avatar
MKoslof MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

The Jet Provider doesn't process the physical data the same as SQL Server. The same goes with Oracle. That is what I mean by this, you can't just take Jet Provider data and transfer it into a sql server table or Oracle. I assume you are using some sort of upsizing utility or you have already handled how to migrate your data from Access to SQL Server. The problem is, you have declared a default provider and datasource of Access. You need to clear all existing connectionProperties and re-assign new ones. Can you post the code you are currently using?
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #5  
Old 07-13-2004, 08:07 AM
kesho kesho is offline
Regular
 
Join Date: Jul 2003
Posts: 79
Default

Quote:
Originally Posted by MKoslof
The problem is, you have declared a default provider and datasource of Access. You need to clear all existing connectionProperties and re-assign new ones. Can you post the code you are currently using?
Well, Here is my code for attempting what you have suggested I realy don't know what I am doing here.

Code:
Dim crxApplication As New CRAXDRT.Application Dim crxReport As CRAXDRT.Report Dim crxDatabaseTable As CRAXDRT.DatabaseTable With frmPrev .txtReportName = FileName .txtSelectFormula = SelectionFormula Set crxReport = crxApplication.OpenReport(.txtReportName, 1) If UBound(Arg) <> crxReport.Database.Tables.Count - 1 Then err.Raise vbObjectError + 1, "frmPreview.LoadIt", _ "frmPreview.LoadIt" & vbCr & "Wrong number of databases passed as arguments." End If Count = 0 For Each crxDatabaseTable In crxReport.Database.Tables With crxDatabaseTable.ConnectionProperties .Item("Database Password") = "" .Item("Session UserID") = "" .Item("System Database Path") = "" .Item("Database Name") = "" .Item("Database Type") = "" End With ‘I am trying this out of desperation crxDatabaseTable.DllName = “crdb_ado.dll” With crxDatabaseTable.ConnectionProperties .Item("Provider") = "SQLOLEDB" .Item("Data source") = "KESHO" .Item("Initial Catalog") = Arg(Count) .Item("User ID") = "" .Item("Password") = "" .Item("Integrated Security") = "True" End With Debug.Print crxDatabaseTable.ConnectBufferString Debug.Print crxDatabaseTable.Location Count = Count + 1 Next crxDatabaseTable If txtSelectFormula <> "" Then crxReport.RecordSelectionFormula = txtSelectFormula End If crxReport.DiscardSavedData CRV.ReportSource = crxReport CRV.ViewReport CRV.Refresh .Show , frmMe End With end with

Edit by mkoslof: Just added a line break so it is easier to read, without having to scroll the page. Thanks

Last edited by MKoslof; 07-13-2004 at 08:35 AM.
Reply With Quote
  #6  
Old 07-13-2004, 08:34 AM
MKoslof's Avatar
MKoslof MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

OK, try this. First remove all existing connection properties, and then add new ones, and set the dynamic location to where it needs to be.

And make sure the SQL Server datasource is completely compatible with the old Access datasource. If the report has "field1, field2, field3 and field4" on it, make sure this new table has field1, field2, field3 and field4 with compatible data types. Try something like:

Code:
Dim crxApp As CRAXDRT.Application Dim Report As CRAXDRT.Report Dim crxDatabaseTable As CRAXDRT.DatabaseTable Dim lstrDataSource As String Dim lstrCatalog As String Dim lstrUser As String Dim lstrPassword As String Dim objDB_Owner As String Dim objDBView_Owner As String Dim lsrtLocation As String Dim lstrTableName As String Set crxApp = New CRAXDRT.Application 'assign the string variables that we need to connection 'this includes the datasource of the report, the intial catalog 'owner info, some of this you may or may not need, depends on your server 'connection, etc. lstrDataSource = "MY_TEST" lstrCatalog = "catalog_abc" lstrUser = "ID" lstrPassword = "PASS" objDB_Owner = "dbo" objDBView_Owner = "dbo" 'pass in the rpt file or create a new instance of the dsr object Set Report = crxApp.OpenReport("RDCTest.rpt") 'now remove any existing properities and add your new ones 'only use the ones pertinent to you, might be all of them For Each crxDatabaseTable In Report.Database.Tables lstrTableName = crxDatabaseTable.Location crxDatabaseTable.ConnectionProperties.DeleteAll crxDatabaseTable.ConnectionProperties.Add "DSN", lstrDataSource crxDatabaseTable.ConnectionProperties.Add "Database", lstrCatalog crxDatabaseTable.ConnectionProperties.Add "User ID", lstrUser crxDatabaseTable.ConnectionProperties.Add "Password", lstrPassword crxDatabaseTable.ConnectionProperties.Add "Owner", objDB_Owner crxDatabaseTable.ConnectionProperties.Add "Table Schema", objDBView_Owner crxDatabaseTable.ConnectionProperties.Add "Table Owner", objDBView_Owner crxDatabaseTable.ConnectionProperties.Add "Schema", objDBView_Owner crxDatabaseTable.ConnectionProperties.Add "Schema Owner", objDBView_Owner 'reassign the table location to its new location lstrLocation = lstrCatalog & "." & objDB_Owner & "." & lstrTableName crxDatabaseTable.Location = lstrLocation Next crxDatabaseTable 'now do your recordset and setDatasource code, .discardsavedData, etc. 'view or printout the report, etc.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #7  
Old 07-14-2004, 09:18 AM
kesho kesho is offline
Regular
 
Join Date: Jul 2003
Posts: 79
Default

[QUOTE=MKoslof]OK, try this. First remove all existing connection properties, and then add new ones, and set the dynamic location to where it needs to be.
[QUOTE]

O.K. I seem to have made some progress with your adivce, but it is still not connecting as it should. This may be beause I was relaying on some of the settings you posted but the appeared to be for a ODBC connection rather than a OLE DB connection.

Any way the error I am getting is as follows
Code:
RTE -2147189176
Logon failed
Details: ADO Error Code: 0x80004005
Source: Microsoft OLE DB Provider for SQL Server
Description: [DBNETLIB][ConnectionOpen (Connect()).]SQl Server does not exist or access denied.
SQL State: 08001
Native Error: 17
The connection should be working as i am trying to logon to the Master catalog with the 'sa' user (who we have left with no password for testing purposes).

Here is the code i am using:

Code:
Dim I As Variant Dim TableName As String Dim Count As Long Const objDB_Owner = "dbo" Const objDBView_Owner = "dbo" '*** ' Set the Crystal Report path to the Current Appilcation path '*** With frmPrev Set crxReport = crxApplication.OpenReport(.txtReportName, 1) If UBound(Arg) <> crxReport.Database.Tables.Count - 1 Then err.Raise vbObjectError + 1, "frmPreview.LoadIt", "frmPreview.LoadIt" & vbCr _ & "Wrong number of databases passed as arguments." End If Count = 0 For Each crxDatabaseTable In crxReport.Database.Tables '*** ' Get the Table name '*** 'This is the Table for the test report (I am just using it for testing) TableName = "MemberBalances" '*** ' Delete the old properties '*** crxDatabaseTable.ConnectionProperties.DeleteAll '*** ' Add the new properties '*** 'Properties taken out of a working SQL Server ConnectionBufferString crxDatabaseTable.ConnectionProperties.Add "Provider", "SQLOLEDB" crxDatabaseTable.ConnectionProperties.Add "Data source", CurtOpen.txtDatabase crxDatabaseTable.ConnectionProperties.Add "Initial Catalog", Arg(Count) crxDatabaseTable.ConnectionProperties.Add "User ID", "sa" crxDatabaseTable.ConnectionProperties.Add "Password", "" crxDatabaseTable.ConnectionProperties.Add "Integrated Security", "0" crxDatabaseTable.ConnectionProperties.Add "Locale Identifier", "2057" crxDatabaseTable.ConnectionProperties.Add "Connect Timeout", "15" crxDatabaseTable.ConnectionProperties.Add "General Timeout", "0" crxDatabaseTable.ConnectionProperties.Add "OLE DB Services", "-5" crxDatabaseTable.ConnectionProperties.Add "Current Language", "" crxDatabaseTable.ConnectionProperties.Add "Initial File Name", "" crxDatabaseTable.ConnectionProperties.Add "Use Encryption for Data", "0" 'Properties taken from your posting crxDatabaseTable.ConnectionProperties.Add "Owner", objDB_Owner crxDatabaseTable.ConnectionProperties.Add "Table Schema", objDBView_Owner crxDatabaseTable.ConnectionProperties.Add "Table Owner", objDBView_Owner crxDatabaseTable.ConnectionProperties.Add "Schema", objDBView_Owner crxDatabaseTable.ConnectionProperties.Add "Schema Owner", objDBView_Owner 'change the dll to the SQL server connection dll (I am guessing but it 'didn't even get to the login failure error without it). crxDatabaseTable.DllName = "crdb_ado.dll" 'reassign the table location to its new location (this is where the error 'ocurs). crxDatabaseTable.Location = Arg(Count) & "." & objDB_Owner & "." & TableName Count = Count + 1 Next crxDatabaseTable If txtSelectFormula <> "" Then crxReport.RecordSelectionFormula = txtSelectFormula End If crxReport.DiscardSavedData CRV.ReportSource = crxReport CRV.ViewReport CRV.Refresh .Show , frmMe End With
Reply With Quote
  #8  
Old 07-14-2004, 09:47 AM
kesho kesho is offline
Regular
 
Join Date: Jul 2003
Posts: 79
Default Data Source not taken

I have overcome the problem above. The data source did not take when I added the connection property, so I set it again. The report is no longer generating any errors, but neither is it showing the report.

Does anyone have any ideas why that might be?

It is just showing a white screen. Is this its way of refusing to change the datasource from access to sql?

Here is the code now:

Code:
Dim I As Variant Dim TableName As String Dim Count As Long Const objDB_Owner = "dbo" Const objDBView_Owner = "dbo" '*** ' Set the Crystal Report path to the Current Appilcation path '*** With frmPrev Set crxReport = crxApplication.OpenReport(.txtReportName, 1) If UBound(Arg) <> crxReport.Database.Tables.Count - 1 Then err.Raise vbObjectError + 1, "frmPreview.LoadIt", "frmPreview.LoadIt" & _ vbCr & "Wrong number of databases passed as arguments." End If Count = 0 For Each crxDatabaseTable In crxReport.Database.Tables '*** ' Get the Table name '*** TableName = "MemberBalances" '*** ' Delete the old properties '*** crxDatabaseTable.ConnectionProperties.DeleteAll '*** ' Add the new properties '*** crxDatabaseTable.ConnectionProperties.Add "Provider", "SQLOLEDB" crxDatabaseTable.ConnectionProperties.Add "Data Source", CurtOpen.txtDatabase crxDatabaseTable.ConnectionProperties.Add "Initial Catalog", Arg(Count) crxDatabaseTable.ConnectionProperties.Add "User ID", "sa" crxDatabaseTable.ConnectionProperties.Add "Password", "" crxDatabaseTable.ConnectionProperties.Add "Integrated Security", "0" crxDatabaseTable.ConnectionProperties.Add "Locale Identifier", "2057" crxDatabaseTable.ConnectionProperties.Add "Connect Timeout", "15" crxDatabaseTable.ConnectionProperties.Add "General Timeout", "0" crxDatabaseTable.ConnectionProperties.Add "OLE DB Services", "-5" crxDatabaseTable.ConnectionProperties.Add "Current Language", "" crxDatabaseTable.ConnectionProperties.Add "Initial File Name", "" crxDatabaseTable.ConnectionProperties.Add "Use Encryption for Data", "0" 'reassign the table location to its new location crxDatabaseTable.DllName = "crdb_ado.dll" crxDatabaseTable.ConnectionProperties.Item("Data Source") = CurtOpen.txtDatabase 'I have also tried '= Arg(Count) & "." & objDB_Owner & "." & TableName 'with the same result crxDatabaseTable.Location = TableName Count = Count + 1 Next crxDatabaseTable If txtSelectFormula <> "" Then crxReport.RecordSelectionFormula = txtSelectFormula End If crxReport.DiscardSavedData CRV.ReportSource = crxReport CRV.ViewReport CRV.Refresh .Show , frmMe End With
Reply With Quote
  #9  
Old 07-14-2004, 12:34 PM
MKoslof's Avatar
MKoslof MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

What is the value of this at run-time?

Set crxReport = crxApplication.OpenReport(.txtReportName, 1)

so when you get to the CRV.ReportSource line what is the report bound to, what object name. And are you sure you are passing the correct location, given the parameters you have named, I am not sure that is a valid location of a data table or view.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #10  
Old 07-15-2004, 04:14 AM
kesho kesho is offline
Regular
 
Join Date: Jul 2003
Posts: 79
Default

Quote:
Originally Posted by MKoslof
What is the value of this at run-time?

Set crxReport = crxApplication.OpenReport(.txtReportName, 1)

so when you get to the CRV.ReportSource line what is the report bound to, what object name. And are you sure you are passing the correct location, given the parameters you have named, I am not sure that is a valid location of a data table or view.
Sorry I was trying to siplify some of the rubbish in the form before posting and missed this. The report is valid. If I remove the lines of code to delete the connectionParameters than the report will show without any problem. The table name seems to be correct because the report raises an error when I change it to something unsuitable.

SO it is just that it won't show it.
Reply With Quote
  #11  
Old 07-15-2004, 06:37 AM
MKoslof's Avatar
MKoslof MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

OK, you have not set a run-time datasource via the SetDataSource method . I am somewhat confused here. Obviously you want to set a run-time datasource, because you are setting a run-time table and database location. Your report is currently looking at your SQL Server tables, but you have not told it what data to return. The used to be looking at Access with a default queries in the Jet Provider. Now, it is looking at SQL Server. The fields are still on the report, but it needs to send the query to use in SQL Server.

You could create an ADODB connection object and ADODB recordset object and connect to the database and open the recordset that would want (this should be the same as the Access table and query, since you said these have the same structure), then pass as the runtime datasource to the report.

And most importantly, this line is typically your Data Source and then the table or view name:

crxDatabaseTable.Location = TableName

I believe you are simply passing a table name string. This should be more along the lines of: dbo.ViewName, or dbo.TableName, etc
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
 
 
-->