[Solved] No connection to database exists when open report

Creating and using forms

[Solved] No connection to database exists when open report

Postby Nocton » Sat Nov 12, 2011 6:44 pm

I have a menu form, MainMenu, with buttons which open other forms or report documents. The buttons call macros with the lines:
Code: Select all   Expand viewCollapse view
ThisDatabaseDocument.FormDocuments.getbyname("FormName").open

for a form
Code: Select all   Expand viewCollapse view
ThisDatabaseDocument.reportDocuments.getbyname("ReportName").open

for a report.
This all works fine.

However, if I load MainMenu automatically when the database opens, by calling the following macro from the database's Open Document event
Code: Select all   Expand viewCollapse view
Sub AutoExec
On Error Goto HandleError
   Dim FormDocs, DBDoc, DataSource, Conn, FormDoc As Object
   Dim Args(1) As New com.sun.star.beans.PropertyValue
   Dim FormName, UserName, Password As String
   
   UserName="" : Password=""
   DBDoc=ThisComponent
   DataSource=DBDoc.DataSource
   Conn=DataSource.getConnection(UserName,Password)
   
   FormName="MainMenu"
   
   FormDocs=DBDoc.FormDocuments
   Args(0).Name="ActiveConnection" : Args(0).Value=Conn
   If FormDocs.hasByName(FormName) Then
      FormDoc=FormDocs.loadComponentFromURL(FormName,"_self",0,Args() )
      'FormDoc.CurrentController.Frame.ContainerWindow.setFocus()
      FormDoc.CurrentController.Frame.ComponentWindow.setFocus()
   Else
      MsgBox "No form named: " & FormName
   End If
HandleError:
   If Err<>0 Then
      MsgBox "An error occured while loading the form [ " & FormName & " ]"
      Exit Sub
   End If
End Sub

although the forms open OK as before, the report gives an error, including at the end of the error message "No connection to the database exists".

If I close MainMenu and reopen manually, then the report opens without an error. Can anyone explain what is going on here? And perhaps how to solve the problem.

Regards,
Nocton
Last edited by Nocton on Sun Nov 13, 2011 3:02 pm, edited 1 time in total.
OpenOffice 4.2.0 on Windows 10
Nocton
Volunteer
 
Posts: 506
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: No connection to the database exists when opening report

Postby RPG » Sat Nov 12, 2011 7:32 pm

Hello

Maybe this can help you.
viewtopic.php?f=13&t=32823&p=150827&hilit=flush#p150827
Maybe you find the solution nearly half way the thread or more to the end.

It is not clear to me what you are doing.

Romke
LibreOffice 6.2.5.2 on openSUSE Leap 15
RPG
Volunteer
 
Posts: 2172
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: No connection to the database exists when opening report

Postby DACM » Sat Nov 12, 2011 8:59 pm

Nocton wrote:However, if I load MainMenu automatically when the database opens, by calling the following macro from the database's Open Document event

That is precisely the culprit.

You cannot open a Form/Report using the 'Open Document' event without adding additional "connection" and "flush" code to your macro because this method bypasses the connection/flush code in Base somehow. I know for a Form, you'll have to make sure the connection is established programmatically, and flush the connection regularly, or risk losing all session data.
See: http://user.services.openoffice.org/en/forum/viewtopic.php?p=184851#p184851 for the download and code I've used in the past to circumvent this issue. I'm not exactly sure how much of this applies to opening Reports as opposed to Forms, but Romke has been instrumental in identifying and contributing code to this issue.

 Edit: After looking over my old notes and macros pertaining to this issue, I can report that there's no need to 'flush' the connection if you utilize Thisdatabasedocument.currentcontroller.connect to establish the data-source connection...that is when utilizing the 'Open Document' Form Event in a macro to open a Form/Report automatically upon starting Base. The code is remarkably similar to that derived from the same sources by Nocton below:
Code: Select all   Expand viewCollapse view
Sub OpenForm 'Form > Events > Open Document
    With Thisdatabasedocument.currentcontroller
        If  Not .isConnected Then .connect
    End With
    ThisDatabaseDocument.FormDocuments.getbyname("MainMenu").open
End Sub
 


As I recall, the best answer is to use standalone Forms and avoid "embedded databases"... Although I don't think you're an embedded database user...so that may not be a contributing factor here...?
Last edited by DACM on Sat Dec 17, 2011 10:42 am, edited 6 times in total.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
DACM
Volunteer
 
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: No connection to the database exists when opening report

Postby Nocton » Sun Nov 13, 2011 11:30 am

Thank you DACM and Romke. DACM you have pointed me in the right direction and I shall look at the use of 'standalone' forms. You are correct that I am not an embedded database user, but while developing I am using the database in embedded form, because it allows one to edit the tables easily. When I have finished I shall convert it to file server mode for practical use.

I have used the Switchboard extension, but have found it to often give errors and there seems to be no support for it. At least one does not get a reply when one asks a question on the extension page. For example, in the present database which is not particulrly complicated the Switchboard extension gives an error and does not create the form and the tables. But never mind, I would rather be in complete control of the database - hence my 'MainMenu' form.

I am still not clear from your explanation, why opening a form always works, but opening a report gives a problem in the circumstances I mention.

At any rate, after looking through the links suggested I have solved my immediate problem by added the code:
Code: Select all   Expand viewCollapse view
with Thisdatabasedocument.currentcontroller
   if  not .isConnected then  .connect
end with


As mentioned, I shall look also at the use of standalone forms.

Regards, Nocton
OpenOffice 4.2.0 on Windows 10
Nocton
Volunteer
 
Posts: 506
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: No connection to the database exists when opening report

Postby RoryOF » Sun Nov 13, 2011 11:36 am

Might it be that there is a race condition? Perhaps worth putting a delay at the start of the macro run at the opendocument event to see if that cures the problem.
Apache OpenOffice 4.1.7 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29745
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: No connection to the database exists when opening report

Postby RPG » Sun Nov 13, 2011 1:07 pm

Hello

It is not clear to me what is your problem. You are talking about the error and that people do not give answers but there is no real information. What are you doing. Step by step.

Romke
LibreOffice 6.2.5.2 on openSUSE Leap 15
RPG
Volunteer
 
Posts: 2172
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: No connection to the database exists when opening report

Postby Nocton » Sun Nov 13, 2011 1:38 pm

Sorry, RPG, but I think I gave the problem step by step in my original post. (The Switchboard extension problem is something else which I mentioned in passing, but is not the topic of this thread.) DACM seems to have understood the original post clearly and as a result I have a solution plus suggestion for improved
OpenOffice 4.2.0 on Windows 10
Nocton
Volunteer
 
Posts: 506
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: No connection to the database exists when opening report

Postby RPG » Sun Nov 13, 2011 1:49 pm

Hello

Nocton wrote:I am still not clear from your explanation, why opening a form always works, but opening a report gives a problem in the circumstances I mention.


Maybe I do react only to much on this part!
You can open a report from a standalone form when the form does have a connection to the database. But it is not clear to me if that is the problem.

Romke
LibreOffice 6.2.5.2 on openSUSE Leap 15
RPG
Volunteer
 
Posts: 2172
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: No connection to the database exists when opening report

Postby Nocton » Sun Nov 13, 2011 2:27 pm

In the circumstances, exactly as I described in my original post after auto-loading the menu form when the databse opens the button calling the report document gave the error, but the button calling the form document did not. My 'final' solution is to get rid of the AutoExec code that I gave and replace it with the simple code:
Code: Select all   Expand viewCollapse view
sub Start
with Thisdatabasedocument.currentcontroller
   if  not .isConnected then  .connect
end with
ThisDatabaseDocument.FormDocuments.getbyname("MainMenu").open
end sub


which I assign to the Open Document event of the database.

All the buttons on MainMenu then work OK.

Separately, re the Switchboard extension, when I try to use it I get the error message:
"Unable to establish connection to [FEMCC]. Could not create/access required tables." where the database name is FEMCC and it is properly registered. I have even tried deleting and re-registering. It would be nice to know what the cause of this is, but in the end I am happier with some simple code which works and which I can understand. And I don't have to spend more time on this, but can get on developing the database itself.

Regards, Nocton
OpenOffice 4.2.0 on Windows 10
Nocton
Volunteer
 
Posts: 506
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: No connection to the database exists when opening report

Postby RPG » Sun Nov 13, 2011 2:41 pm

Hello

Partly I do write on this code so there can be an error but I have test it real as possible I can. Pass parameter in a tag of a button. The code is a long code but I have also time the code. I does not made a great difference between short code long code. It can be when there is no connection then it cost more time but that is only the first time. When you want closing the old form I think do it after making the report and not before making the report: You need the connection.

Romke


Code: Select all   Expand viewCollapse view
sub CreateReportCalledfromButton(oEvent as object)
' We need this sub for a button in a standalone form
dim oCreateButton,oForm ' This are form details
dim oConnection,oReportsDocuments
' The connection we need for finding the database document
oCreateButton=oEvent.source.model

if oCreateButton.tag = "" then
   msgbox "No report name in the button tag" & chr(13) & "This is the third from below of the property on the common tabpage"
else ' Now we have to start to find all kind of objects
   oForm=oCreateButton.parent
   if oForm.filter="" then
      print "You have not filtered data in your form"
   else
      oConnection=oForm.Activeconnection
      oReportsDocuments=oConnection.parent.DatabaseDocument.ReportDocuments ' This point to the report containers
      ' Test if the name exist.
      if oReportsDocuments.hasByHierarchicalName(oCreateButton.tag) then
         'Call openreport with the parameters
         openReport(oConnection, oReportsDocuments, oCreateButton.tag , oForm.Filter)
      else print "There is a wrong report name"
      end if
   end if
end if
end sub





function openReport(oConnection, oReportsDocuments,aReportName,sFilter) as object
   ' With working in the way I do now I can use these sub for calling from a document
   ' what is stored in a databasedocument but also from a standalone form
   ' It is assumend you do all the test before you call this sub. This means
   ' You must be sure there is a connection to the database.
   ' The document you want call must be
   '   a)in the databasedocument from this connection.
   '    b) The document must be a report
   
   ' When I use a standalone form then the databasedocument does not have a
   ' currentcontroller. It can be that the database is already and it does have a currentroller
   ' but this is also working
         
   ' We make here the array we use in both commands
   dim aProp(2) as new com.sun.star.beans.PropertyValue
   dim aProp2 ' Make a variant
   dim oReportDesign
   aProp(1).Name = "ActiveConnection"
   aProp(1).Value = oConnection
   aProp(0).Name = "Hidden"
   aProp(0).Value = true   
   aProp(2).Name = "OpenMode"
   aProp(2).Value = "openDesign"
   let aProp2=aProp 'assign the array. The reason it is possible that the values
   ' are changed when I use it in loadComponentFromURL
   'Open report in design mode and hidden
   oReportDesign=oReportsDocuments.loadComponentFromURL(aReportName ,"",0,aProp())
   oReportDesign.filter=sFilter ' Change the filter
   
   ' Two Values must be changed
   aProp2(0).Value = false
   aProp2(2).Value = "open"
   ' Now the result of the report wil be showed. and the the hidden report in design mode is closed
   openReport=oReportsDocuments.loadComponentFromURL(aReportName ,"",23,aProp2())
end function
LibreOffice 6.2.5.2 on openSUSE Leap 15
RPG
Volunteer
 
Posts: 2172
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: No connection to the database exists when opening report

Postby Nocton » Sun Nov 13, 2011 2:59 pm

Thank you, Romke. A very useful bit of code. I think I shall set this thread as solved now.

Regards, Nocton
OpenOffice 4.2.0 on Windows 10
Nocton
Volunteer
 
Posts: 506
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: [Solved] No connection to database exists when open repo

Postby arfgh » Thu May 18, 2017 2:02 pm

i tried these openReport functions, but it isnt accepting 'oReport.filter', why ?
OpenOffice last version - Windows 8.1 Enterprise x64 - Windows XP x64 SP2
arfgh
 
Posts: 452
Joined: Tue Mar 05, 2013 6:44 pm

Re: [Solved] No connection to database exists when open repo

Postby Villeroy » Thu May 18, 2017 3:50 pm

What makes you believe that your oReport object has a property "filter"?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27292
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] No connection to database exists when open repo

Postby arfgh » Thu May 18, 2017 11:23 pm

good question, just because i have seen these functions, so i tried.
OpenOffice last version - Windows 8.1 Enterprise x64 - Windows XP x64 SP2
arfgh
 
Posts: 452
Joined: Tue Mar 05, 2013 6:44 pm

Re: [Solved] No connection to database exists when open repo

Postby Villeroy » Fri May 19, 2017 7:52 am

Can you manually apply filters to reports? No? Then it is very unlikely that reports have a "Filter" property and as a programmer you should be able to find out.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27292
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] No connection to database exists when open repo

Postby arfgh » Fri May 19, 2017 3:11 pm

so then Villeroy, how you explain the RPG's functions ? it is related to reports ....
OpenOffice last version - Windows 8.1 Enterprise x64 - Windows XP x64 SP2
arfgh
 
Posts: 452
Joined: Tue Mar 05, 2013 6:44 pm

Re: [Solved] No connection to database exists when open repo

Postby Villeroy » Fri May 19, 2017 10:38 pm

I don't know. He refers to a report's design object. Possibly it's a new style report. YOU are the programmer. It is YOUR task to find out about these things. YOU have to test code, inspect objects and variables until you understand every single word in every line. This may take hours. I don't have that much time.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27292
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] No connection to database exists when open repo

Postby RPG » Fri May 19, 2017 11:20 pm

Hello

As far I remember me it is only for a newstyle report. For an old style report it is more complicated. I will not study for the code.

Romke
LibreOffice 6.2.5.2 on openSUSE Leap 15
RPG
Volunteer
 
Posts: 2172
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands


Return to Forms

Who is online

Users browsing this forum: No registered users and 3 guests