Solved

Changing Database location

Posted on 2011-03-05
11
1,096 Views
Last Modified: 2012-05-11
I need to be able to change the database location in Crystal 10, in vb code I have searched for solutions here
and have found one that would be just right for my problem.
I get the following error when I try to print a report on the mySQL data base which is located on the web.



Run-time error '-2147189180(80047e44)'
The table 'tblproject' could not be found.

this is the code I am using:-


 

    Dim crx As New CRAXDDRT.Application ' creates an instance of the Crystal Report application
    Dim rpt As CRAXDDRT.Report 'will hold the report object later
    Dim i As Integer
   
    Set rpt = crx.OpenReport(strReport) 'asks the crystal application to open a report file named C:\blah\CoverPage.rpt and returns it to rpt
    rpt.DiscardSavedData 'throws out any saved data the report might already have

    For i = 1 To rpt.Database.Tables.Count 'the database.tables collection is all the tables used by the report

------------------  The following Line Gives The Error -------------------------------------

        rpt.Database.Tables.Item(i).SetTableLocation rpt.Database.Tables.Item(i).Location, "", m_objConnConnectString
            'SetTableLocation sets where Crystal will look for that table.  g_objcon is just an ADO connection I've already opened on my dataset
            'the connectionstring could simply be specified here, e.g. "provider=SQLOLEDB;server=MySQLServer;database=MyDatabaseName;UID=MyUser;PWD=MyPassword"
            'I use theg_objcon.connectionstring because in most of my apps, I have logic to choose which database the user will connect to, and once the
            'connection is open, I simply use it's connection string to avoid repeating the logic to choose which database.
    Next
    CrystalActiveXReportViewer1.ReportSource = rpt
    CrystalActiveXReportViewer1.Refresh
    CrystalActiveXReportViewer1.ViewReport


Although not related Using the same code but the connect string below I have a strange problem with the same code but attempting to print a report from a local Access database for each table in the report it asks for database location ans user and password the connect string is:-
myDBConnectString = "provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Data\" & strMyDB
username and password are not required as the program generates the data
0
Comment
Question by:RichardLudkiewicz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
11 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 35044313
Are you trying to change database types as well as location?

mlmcc
0
 

Author Comment

by:RichardLudkiewicz
ID: 35045248
well yes but I am using the code twice in different forms, one form for the mysql database, and a different one for the Access.

In Access:
But essentially if I create a report and then place it in a different location the the problem arises for each table in the report it asks for user name and password to be entered.

In mysql:
The mysql problem is that it does not seem to find the first table in the report as it falls over at that point I cant say it wont find any other table.
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 35046901
So you aren't trying to change a report to use 2 different database types just  different reports use different databases

When changing the Access database, is it using the same MDW file?

Try it this way for Access

rpt.Database.Tables(1).Location = App.Path & "\Cust_Orders.mdb"

Also fo the user/password (default user)
'sets the database password enabling the report to connect

rpt.Database.Tables(1).SetSessionInfo "", Chr(10) & "bits"

mlmcc
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:RichardLudkiewicz
ID: 35046925
ok will try that, am I correct inthinking that I dont need to use the connect string and should I just use the code as you have written it except use my database name.
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 500 total points
ID: 35047087
I think so.  That should work for Access.  If MySQL has the same type of file then it may work for that also.

If you are trying to use 1 report against 2 different types of databases that won't work.

mlmcc
0
 

Author Comment

by:RichardLudkiewicz
ID: 35067217
Yes thank you it worked for the access database, now all we need to do is to get the mysql working, this is important because most of the reports will be run for there.
0
 

Author Comment

by:RichardLudkiewicz
ID: 35115910
Hi
I recieved an email with a possible solution, but have lost it I thought the solution worked but have just tried it against a second database and the report printed the data from the original database, this is the code I am now using


Private Sub Form_Load()
    Dim App As New CRAXDDRT.Application
    Dim rpt As Report
    SetReportConnection (strReport) ' strReport is the path to the file and the report name
    Set rpt = App.OpenReport(strReport)
   
    CrystalActiveXReportViewer1.ReportSource = rpt
    CrystalActiveXReportViewer1.ViewReport
End Sub

Private Sub Form_Resize()
    CrystalActiveXReportViewer1.Top = 0
    CrystalActiveXReportViewer1.Left = 0
    CrystalActiveXReportViewer1.Height = ScaleHeight
    CrystalActiveXReportViewer1.Width = ScaleWidth
End Sub



Private Sub SetReportConnection(ByVal FileName As String)
  On Error GoTo ErrorHandler
 
  Dim x
  Dim oCrxApp           As New CRAXDDRT.Application
  Dim oCrxReport        As CRAXDDRT.Report
  Dim oCrxSubReport     As CRAXDDRT.Report
  Dim oSection          As CRAXDDRT.Section
  Dim oReportObject     As Object
  Dim sConnectionString As String
  Dim bReportChanged    As Boolean
  Dim i                 As Integer
 
  'sConnectionString = "Driver={SQL Server}; UID=sa; PWD=$password$; Server=MYSQL; Database=MyArchive;"
  bReportChanged = False
  Set oCrxReport = oCrxApp.OpenReport(FileName)
 
  For i = 1 To oCrxReport.Database.Tables.Count
    If bReportChanged Or oCrxReport.Database.Tables(i).ConnectionProperties("Connection String") <> m_objConnConnectString Then
      bReportChanged = True
      oCrxReport.Database.Tables(i).ConnectionProperties.DeleteAll
      oCrxReport.Database.Tables(i).ConnectionProperties.Add "Connection String", m_objConnConnectString
      oCrxReport.Database.Tables(i).SetTableLocation oCrxReport.Database.Tables(i).Location, "", ""
    End If
  Next i
 
  If bReportChanged Then
    For Each oSection In oCrxReport.Sections
      For Each oReportObject In oSection.ReportObjects
        If TypeOf oReportObject Is ISubreportObject Then
          Set oCrxSubReport = oCrxReport.OpenSubreport(oReportObject.SubreportName)
         
          For i = 1 To oCrxSubReport.Database.Tables.Count
            oCrxSubReport.Database.Tables(i).ConnectionProperties.DeleteAll
            oCrxSubReport.Database.Tables(i).ConnectionProperties.Add "Connection String", m_objConnConnectString
            oCrxSubReport.Database.Tables(i).SetTableLocation oCrxSubReport.Database.Tables(i).Location, "", ""
          Next i
        End If
      Next
    Next
  End If
 
  If bReportChanged Then
    On Error Resume Next
    oCrxReport.SaveAs FileName, crDefaultFileFormat
  End If
 
ErrorResume:
  Set oCrxApp = Nothing
  Set oCrxReport = Nothing
  Set oCrxSubReport = Nothing
  Set oSection = Nothing
  Set oReportObject = Nothing
  Exit Sub
 
ErrorHandler:
  x = Err.Number
  GoTo ErrorResume
End Sub
0
 
LVL 6

Expert Comment

by:judgeking
ID: 35123369
Not very nice to use my code without assigning me any points...
0
 
LVL 6

Expert Comment

by:judgeking
ID: 35123934
It was in his original, previous question that was PAQs'd.
0
 

Author Comment

by:RichardLudkiewicz
ID: 35126740
my appoligies I could not find the thread that your code came from and as I could not get the code as presented to work. I did mention that I couldnt find the thread and in the end did not use the code although the ideas from the code did come in usefull. if it is possible please divide the points between the two of them
0

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MySQL Persistent Connections 10 56
Help with a Crystal Formula 6 43
MySQL programmer starter 25 65
Crystal Report with Dynamic Page Headers? 5 38
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

752 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