Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.
Solved
Changing Database location
Posted on 2011-03-05
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 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=
'I use theg_objcon.connectionstri
'connection is open, I simply use it's connection string to avoid repeating the logic to choose which database.
Next
CrystalActiveXReportViewer
CrystalActiveXReportViewer
CrystalActiveXReportViewer
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.OL
username and password are not required as the program generates the data
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 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=
'I use theg_objcon.connectionstri
'connection is open, I simply use it's connection string to avoid repeating the logic to choose which database.
Next
CrystalActiveXReportViewer
CrystalActiveXReportViewer
CrystalActiveXReportViewer
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.OL
username and password are not required as the program generates the data
[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
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.
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.
Active today
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).Loc
Also fo the user/password (default user)
'sets the database password enabling the report to connect
rpt.Database.Tables(1).Set
mlmcc
When changing the Access database, is it using the same MDW file?
Try it this way for Access
rpt.Database.Tables(1).Loc
Also fo the user/password (default user)
'sets the database password enabling the report to connect
rpt.Database.Tables(1).Set
mlmcc
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.
Active today
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
If you are trying to use 1 report against 2 different types of databases that won't work.
mlmcc
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.
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)
CrystalActiveXReportViewer
CrystalActiveXReportViewer
End Sub
Private Sub Form_Resize()
CrystalActiveXReportViewer
CrystalActiveXReportViewer
CrystalActiveXReportViewer
CrystalActiveXReportViewer
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(FileNam
For i = 1 To oCrxReport.Database.Tables
If bReportChanged Or oCrxReport.Database.Tables
bReportChanged = True
oCrxReport.Database.Tables
oCrxReport.Database.Tables
oCrxReport.Database.Tables
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(o
For i = 1 To oCrxSubReport.Database.Tab
oCrxSubReport.Database.Tab
oCrxSubReport.Database.Tab
oCrxSubReport.Database.Tab
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
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)
CrystalActiveXReportViewer
CrystalActiveXReportViewer
End Sub
Private Sub Form_Resize()
CrystalActiveXReportViewer
CrystalActiveXReportViewer
CrystalActiveXReportViewer
CrystalActiveXReportViewer
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(FileNam
For i = 1 To oCrxReport.Database.Tables
If bReportChanged Or oCrxReport.Database.Tables
bReportChanged = True
oCrxReport.Database.Tables
oCrxReport.Database.Tables
oCrxReport.Database.Tables
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(o
For i = 1 To oCrxSubReport.Database.Tab
oCrxSubReport.Database.Tab
oCrxSubReport.Database.Tab
oCrxSubReport.Database.Tab
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
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
Featured Post
Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
- Node.js
- IT Administration
- JavaScript
- Server Software
- MySQL Server
- Percona, *Clustering
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…
Suggested Courses
Course of the Month5 days, left to enroll
670 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.