Apache OpenOffice Wiki - User contributions [en]

MSA-Base Faq

2006-10-02T23:15:10Z

Dale: /* Using a query */

Common questions and answers for those migrating from MS Access to OO.o Base.

This is a user community effort - add to the knowledge base if you can.

Please however try to keep the questions and answers general. If there is an answer regarding a feature specifc version of MSA then include the version number also.

'''Specifically, please only update the wiki if you have an answer for the question you are adding.''' ( ''If you may need to get back shortly with the answer don't let that stop you from getting the queston in first however - just come back before long'' )

For asking '''specific questions''' or for general help it would be appropriate to then ask on either the:
[http://www.oooforum.org/forum/viewforum.phtml?f=10 Base Forum]
or on
[http://dba.openoffice.org/servlets/ProjectMailingListList the Base users mailing list].

== Can I use Microsoft Access databases (.mdb) in Base? ==

On Windows: yes. You cannot open them directly via <code>File|Open</code>, but you can access the data therein. For this, you need the [http://msdn.microsoft.com/data/ref/mdac/downloads Microsoft Data Access Components], which are available from Microsoft for free. (In Windows XP, they're already included, so you need them for older Windows versions only.)

Then, go to <code>File|New|Database</code>, choose "Connect to an existing datbase", and select the "Microsoft Access" database type from the list. On the next page, you then select the .mdb file you want to access - that's it!

This will allow you to read and update the data in your Acess tables.

You cannot read / modify or use the Access forms, queries, or reports.

== Can I read my Base database from MS Access? ==
There is no way to use the table linking mechanisim in Access to connect to a Base database.

It is possible for a Base database to update data in the Access database. At this time this will require however that the Access database be opened and locked by the Base file.

Data could also be passed back to the Access database by creating a linked CSV file in the Base database. Then linking to this CSV file to a table in the Access database.

== Can I create an Access database (mdb file) with Base? ==

No. Base can not create a new Access database file; or new tables, queries, forms etc. within an existing mdb file.

== Can I create a Switchboard in Base? ==

Base does not currently have a switchboard style form wizard. It is possible to achieve a very similar effect however using either of two techinques.

=== Opening a default form via a macro ===

=== Using a stand alone form to launch the Base database ===
== Our Access databases uses "Workgroup Security" to control group/user permission to view/maintain data? ==

This question points out what is a fundamental difference between OOoBase and MSAccess . In the case of MSA (MS Access) there is one and only one database engine in use - although you may link tables in MSA to external datasource - MS Jet is still the only database engine actually used by MSA.

In Base (OOoBase) this is not the case at all. The Base database document is simply a front end ( client application ) to a variaty of database engines. In one of these forms it embeddeds a HSQLDB database into the ODB file itself, many people simply see this as the only Base database format. That is totally mistaken. In this instance of an embedded database there is no support for multiple users and of course no support for groups therefore. However, that is only one of the ways Base can be configured.

You are free to use Base as the front end to a HSQLDB database in server mode. HSQLDB is a very small footprint, low administraton database engine and in server mode has full support for users / groups. For larger numbers of users you could just as easily configure Base to work with MySQL, PostgrSQL, Firebird, Oracle or MS SqlServer.

There is another significant difference between MSA and Base regarding this then. In MSA you would deploy one MDB (or one of its variants ) file, then have each user in the workgroup see this file and open it.

In Base each user would have their own ODB file and each of these would be connected to the shared data server. In this way each user would have their own space for custom queries and reports - but it makes distribution of common forms, queries and reports more work.

== Is a Form in Base different from a Form in MSA? ==

Yes

There are very real differnces between how Forms are implemented in Access and Base.

=== Virtual forms within Base forms ===

== Where are the Delete / Upate queries? ==
Base does not currently support building delete / insert / update queries in the desinger.

=== equivilant actions ===
==== ''using the Query Designer'' ====
You can achieve the equivilant actions in the GUI using the designer and or SQL commands.

==== ''using a macro'' ====

== I want to filter my form based on the contents of a combobox? ==

== Converting my VBA modules to Base modules ==

=== What happened to me? ===

There is no exact duplicate to the ME psuedo variable in VBA.

In Access VBA 'me' has attributes of two seperate objects in OOoBasic:
TextDocument and DataForm

The TextDocument object would be used for controlling the window size and placement on the screen for instance.

The DataForm meanwhile supports result set functions, such as First, Filter, InsertRow.

In OOoBasic the psuedo variable thisComponent normaly would be the TextDocument, while thisComponent.DrawPage.Forms(0) meanwhile would reference the first DataForm owned by the TextDocument.

Perhaps a still closer equivilant is to use the object ownership hierarchy to get to the form. For example if we had a single table form with a button, then in the macro called by the 'onClick' or as OOBase calls it the 'When initializing' event we would do this: Create a macro in a library named onButtonClick( oEv as object )

sub onButtonClick( oEv as object )
' oEv is the event object passed automatially
' it has a property SOURCE which is the
' button that initiated this call

dim oForm as variant

oForm = oEv.Source.Model.Parent
' oForm is now the virtual form objct

dim oDocument as variant

oDocument = thisComponent
' oDocument is now the textDocument displayed in
' the window on screen

end sub

=== Where is findfirst? ===

Base does not appear to have a FindFirst function on the rowset like MS Access, what is the equivilant Base function?

MSAccess VBA example:

Say there is a VB combo box which lists all the prospects in the database and allows the user to select a particular prospect and then have the form automatically go to that person's data record.

Private Sub Find_Combo_AfterUpdate()
Dim strCriteria As String

strCriteria = "[Prospect_ID] =" & Me.Find_Combo

Me.recordsetclone.FindFirst (strCriteria)
If me.recordsetclone.NoMatch Then
MsgBox "No entry found"
Else
Form_F_Prospects.Bookmark = me.recordsetclone.Bookmark
End If

End Sub

For this type of simple single column search it is easy enough to create our own find first equivilant with OOoBasic:

Sub Find_Combo_AfterUpdate( OEv as Object)
' Oev is the event object automatically sent
' to sub procedure call
' Oev will have a property Source
' that is the control that triggered
' the call to this procedure

Dim oForm as variant ' The dataform obejct

Dim oComboBox as variant ' The combobox control

Dim oBkMark as variant

oForm = oEv.Source.Model.Parent

oComboBox = oForm.getByName( "Find_Combo" )

oBkMark = FindFirst( oForm.CreateResultSet(), "Prospect_ID", oComboBox.Text )
' oForm.CreateResultSet() is equivilant here to me.recordsetclone
if not IsEmpty( oBkMark ) then
oForm.moveToBookmark( oBkMark )
else
msgBox( oComboBox.Text & " not found" )
end if

end sub

function FindFirst( oRS as variant, ColName as string, SearchVal as string ) as variant
' oRS is a resultSet object
' ColName is the name of a column in the resultset
' SearchVal is
dim colIDX as integer
dim NotFound as variant

FindFirst = notFound
colIDX = oRS.FindColumn( ColName )
oRS.First
do
if oRS.getString( colIDX ) <> Searchval
oRS.Next
if oRS.isAfterLast then
exit do
endif
else
FindFirst = oRS.getBookMark
exit do
end if
while oRS.isAfterLast <> True

end function

=== How do I set the filter for a sub-form only? ===

=== Is there an equivilant to DoCmd RunSQL ===

Yes, in OOoBase you would use either a Statement or PreparedStatement object to achieve the same function. With OOoBase you must also decide between using execute and executeUpdate.

For example if you had the VBA line
DoCmd RunSQL( "SELECT * FROM tContacts" )

it would become in OOoBase

Dim oStatement as variant

oStatement = OForm.ActiveConnection.CreateStatement
' Where oForm is a DataForm object
' NOTE you could do exactly the same thing with
' a resultSet or rowSet obejct also
' oStatement = OResultSet.ActiveConnection.CreateStatement

oStatement.execute( "SELECT * FROM ""tContacts""" )

If on the other hand the VBA line where
DoCmd RunSQL( "DELETE FROM tContacts WHERE Status = 'INACTIVE'" )

then we would use the executeUpdate method on a statement

oStatement.executeUpdate( "DELETE FROM ""tContacts"" WHERE ""Status"" = 'INACTIVE'" )

The rule is -
*if your SQL statement will return records you use execute or executeQuery.
*if the SQL statement returns no records then you must use executeUpdate.

== If I use Lookup fields/columns in my Access tables what can I do in Base? ==
There are two parts to this answer, given that Base does not support Lookup column types in tables.

When a table is displayed in a custom form use of a bound listbox will give you the same functionality. An example of [[adding a listbox to a form]] and to a grid control on a form is at this page.

In a table view ( double clicking on the table name ) there is no equivilant to this.

==How do I use Domain Aggregate functions in Base?==

Domain Aggregate functions such as '''DCount''', '''DLookup''', '''DMax''' etc have no direct equivalent in Base. You will have to write or design queries to return these results using the appropriate SQL functions.

==How do I add Calculated Controls to my forms and reports?==

Controls in Base forms and reports cannot be used to perform calculations. There are two methods available to get a calculated result on a form or report.

===Using a query===
Modify the underlying data source of the form or report to perform the calculation for each record and return the result as a (calculated) field.

===Using a macro===
If you need your control to update as data is entered into a form, you will need to write a small macro to perform the calculation and assign it to an appropriate event on one of the data entry controls (eg. the Text modified event).

[[Category:Database]]
[[Category:Base Documentation]]

MSA-Base Faq

2006-10-02T23:13:21Z

Dale:

Common questions and answers for those migrating from MS Access to OO.o Base.

This is a user community effort - add to the knowledge base if you can.

Please however try to keep the questions and answers general. If there is an answer regarding a feature specifc version of MSA then include the version number also.

'''Specifically, please only update the wiki if you have an answer for the question you are adding.''' ( ''If you may need to get back shortly with the answer don't let that stop you from getting the queston in first however - just come back before long'' )

For asking '''specific questions''' or for general help it would be appropriate to then ask on either the:
[http://www.oooforum.org/forum/viewforum.phtml?f=10 Base Forum]
or on
[http://dba.openoffice.org/servlets/ProjectMailingListList the Base users mailing list].

== Can I use Microsoft Access databases (.mdb) in Base? ==

On Windows: yes. You cannot open them directly via <code>File|Open</code>, but you can access the data therein. For this, you need the [http://msdn.microsoft.com/data/ref/mdac/downloads Microsoft Data Access Components], which are available from Microsoft for free. (In Windows XP, they're already included, so you need them for older Windows versions only.)

Then, go to <code>File|New|Database</code>, choose "Connect to an existing datbase", and select the "Microsoft Access" database type from the list. On the next page, you then select the .mdb file you want to access - that's it!

This will allow you to read and update the data in your Acess tables.

You cannot read / modify or use the Access forms, queries, or reports.

== Can I read my Base database from MS Access? ==
There is no way to use the table linking mechanisim in Access to connect to a Base database.

It is possible for a Base database to update data in the Access database. At this time this will require however that the Access database be opened and locked by the Base file.

Data could also be passed back to the Access database by creating a linked CSV file in the Base database. Then linking to this CSV file to a table in the Access database.

== Can I create an Access database (mdb file) with Base? ==

No. Base can not create a new Access database file; or new tables, queries, forms etc. within an existing mdb file.

== Can I create a Switchboard in Base? ==

Base does not currently have a switchboard style form wizard. It is possible to achieve a very similar effect however using either of two techinques.

=== Opening a default form via a macro ===

=== Using a stand alone form to launch the Base database ===
== Our Access databases uses "Workgroup Security" to control group/user permission to view/maintain data? ==

This question points out what is a fundamental difference between OOoBase and MSAccess . In the case of MSA (MS Access) there is one and only one database engine in use - although you may link tables in MSA to external datasource - MS Jet is still the only database engine actually used by MSA.

In Base (OOoBase) this is not the case at all. The Base database document is simply a front end ( client application ) to a variaty of database engines. In one of these forms it embeddeds a HSQLDB database into the ODB file itself, many people simply see this as the only Base database format. That is totally mistaken. In this instance of an embedded database there is no support for multiple users and of course no support for groups therefore. However, that is only one of the ways Base can be configured.

You are free to use Base as the front end to a HSQLDB database in server mode. HSQLDB is a very small footprint, low administraton database engine and in server mode has full support for users / groups. For larger numbers of users you could just as easily configure Base to work with MySQL, PostgrSQL, Firebird, Oracle or MS SqlServer.

There is another significant difference between MSA and Base regarding this then. In MSA you would deploy one MDB (or one of its variants ) file, then have each user in the workgroup see this file and open it.

In Base each user would have their own ODB file and each of these would be connected to the shared data server. In this way each user would have their own space for custom queries and reports - but it makes distribution of common forms, queries and reports more work.

== Is a Form in Base different from a Form in MSA? ==

Yes

There are very real differnces between how Forms are implemented in Access and Base.

=== Virtual forms within Base forms ===

== Where are the Delete / Upate queries? ==
Base does not currently support building delete / insert / update queries in the desinger.

=== equivilant actions ===
==== ''using the Query Designer'' ====
You can achieve the equivilant actions in the GUI using the designer and or SQL commands.

==== ''using a macro'' ====

== I want to filter my form based on the contents of a combobox? ==

== Converting my VBA modules to Base modules ==

=== What happened to me? ===

There is no exact duplicate to the ME psuedo variable in VBA.

In Access VBA 'me' has attributes of two seperate objects in OOoBasic:
TextDocument and DataForm

The TextDocument object would be used for controlling the window size and placement on the screen for instance.

The DataForm meanwhile supports result set functions, such as First, Filter, InsertRow.

In OOoBasic the psuedo variable thisComponent normaly would be the TextDocument, while thisComponent.DrawPage.Forms(0) meanwhile would reference the first DataForm owned by the TextDocument.

Perhaps a still closer equivilant is to use the object ownership hierarchy to get to the form. For example if we had a single table form with a button, then in the macro called by the 'onClick' or as OOBase calls it the 'When initializing' event we would do this: Create a macro in a library named onButtonClick( oEv as object )

sub onButtonClick( oEv as object )
' oEv is the event object passed automatially
' it has a property SOURCE which is the
' button that initiated this call

dim oForm as variant

oForm = oEv.Source.Model.Parent
' oForm is now the virtual form objct

dim oDocument as variant

oDocument = thisComponent
' oDocument is now the textDocument displayed in
' the window on screen

end sub

=== Where is findfirst? ===

Base does not appear to have a FindFirst function on the rowset like MS Access, what is the equivilant Base function?

MSAccess VBA example:

Say there is a VB combo box which lists all the prospects in the database and allows the user to select a particular prospect and then have the form automatically go to that person's data record.

Private Sub Find_Combo_AfterUpdate()
Dim strCriteria As String

strCriteria = "[Prospect_ID] =" & Me.Find_Combo

Me.recordsetclone.FindFirst (strCriteria)
If me.recordsetclone.NoMatch Then
MsgBox "No entry found"
Else
Form_F_Prospects.Bookmark = me.recordsetclone.Bookmark
End If

End Sub

For this type of simple single column search it is easy enough to create our own find first equivilant with OOoBasic:

Sub Find_Combo_AfterUpdate( OEv as Object)
' Oev is the event object automatically sent
' to sub procedure call
' Oev will have a property Source
' that is the control that triggered
' the call to this procedure

Dim oForm as variant ' The dataform obejct

Dim oComboBox as variant ' The combobox control

Dim oBkMark as variant

oForm = oEv.Source.Model.Parent

oComboBox = oForm.getByName( "Find_Combo" )

oBkMark = FindFirst( oForm.CreateResultSet(), "Prospect_ID", oComboBox.Text )
' oForm.CreateResultSet() is equivilant here to me.recordsetclone
if not IsEmpty( oBkMark ) then
oForm.moveToBookmark( oBkMark )
else
msgBox( oComboBox.Text & " not found" )
end if

end sub

function FindFirst( oRS as variant, ColName as string, SearchVal as string ) as variant
' oRS is a resultSet object
' ColName is the name of a column in the resultset
' SearchVal is
dim colIDX as integer
dim NotFound as variant

FindFirst = notFound
colIDX = oRS.FindColumn( ColName )
oRS.First
do
if oRS.getString( colIDX ) <> Searchval
oRS.Next
if oRS.isAfterLast then
exit do
endif
else
FindFirst = oRS.getBookMark
exit do
end if
while oRS.isAfterLast <> True

end function

=== How do I set the filter for a sub-form only? ===

=== Is there an equivilant to DoCmd RunSQL ===

Yes, in OOoBase you would use either a Statement or PreparedStatement object to achieve the same function. With OOoBase you must also decide between using execute and executeUpdate.

For example if you had the VBA line
DoCmd RunSQL( "SELECT * FROM tContacts" )

it would become in OOoBase

Dim oStatement as variant

oStatement = OForm.ActiveConnection.CreateStatement
' Where oForm is a DataForm object
' NOTE you could do exactly the same thing with
' a resultSet or rowSet obejct also
' oStatement = OResultSet.ActiveConnection.CreateStatement

oStatement.execute( "SELECT * FROM ""tContacts""" )

If on the other hand the VBA line where
DoCmd RunSQL( "DELETE FROM tContacts WHERE Status = 'INACTIVE'" )

then we would use the executeUpdate method on a statement

oStatement.executeUpdate( "DELETE FROM ""tContacts"" WHERE ""Status"" = 'INACTIVE'" )

The rule is -
*if your SQL statement will return records you use execute or executeQuery.
*if the SQL statement returns no records then you must use executeUpdate.

== If I use Lookup fields/columns in my Access tables what can I do in Base? ==
There are two parts to this answer, given that Base does not support Lookup column types in tables.

When a table is displayed in a custom form use of a bound listbox will give you the same functionality. An example of [[adding a listbox to a form]] and to a grid control on a form is at this page.

In a table view ( double clicking on the table name ) there is no equivilant to this.

==How do I use Domain Aggregate functions in Base?==

Domain Aggregate functions such as '''DCount''', '''DLookup''', '''DMax''' etc have no direct equivalent in Base. You will have to write or design queries to return these results using the appropriate SQL functions.

==How do I add Calculated Controls to my forms and reports?==

Controls in Base forms and reports cannot be used to perform calculations. There are two methods available to get a calculated result on a form or report.

===Using a query===
Modify the underlying data source of the form or report to perform the calculation for each record return the result as a (calculated) field.

===Using a macro===
If you need your control to update as data is entered into a form, you will need to write a small macro to perform the calculation and assign it to an appropriate event on one of the data entry controls (eg. the Text modified event).

[[Category:Database]]
[[Category:Base Documentation]]

Talk:MSA-Base Faq

2006-10-02T12:08:46Z

Dale: How do I add Calculated Controls to my forms and reports?

== How do I import/convert my Access database to Base ==

We seem to be missing a couple of obvious FAQs. Probably because the page has just started and no one has got around to them yet.

How do I import/convert my Access database to Base?

As of ver 2.0 there is no direct method for this. Tables can be read from the mdb or converted to a more Base-friendly format (native Base tables, Calc spreadsheet, csv). <<Probably need some indication of method here. Or maybe reference to a separate (yet to be written) HowTo>>

Forms and reports cannot be imported and have to be re created from scratch.

There is no direct equivalent to Access macros. These have to be re created from scratch in a language supported by OOo (StarBasic, ...)

Code modules:
OOo and Base do not support VBA. The code used in you Access database will have to be rewritten in a language supported by OOo.

+ + + + +
Dale

== Lookup Columns ==

Edited to bring it up to it's own main topic and specifically state that it refers to table design.--[[User:Dale|Dale]] 14:05, 2 October 2006 (CEST)

== How do I use Domain Aggregate functions in Base? ==

Added entry. Pls review for correctness, and clarity. Add any further explanation that seems appropriate.--[[User:Dale|Dale]] 14:07, 2 October 2006 (CEST)

== How do I add Calculated Controls to my forms and reports? ==

Added entry. This is almost a direct copy of DrewJensen's contributions in oooforum.org. Pls review to make sure I haven't trashed Drew's good words.--[[User:Dale|Dale]] 14:08, 2 October 2006 (CEST)

Talk:MSA-Base Faq

2006-10-02T12:07:08Z

Dale: How do I use Domain Aggregate functions in Base?

Talk:MSA-Base Faq

2006-10-02T12:05:01Z

Dale: Lookup Columns

Talk:MSA-Base Faq

2006-10-02T12:03:00Z

Dale:

MSA-Base Faq

2006-10-02T11:59:08Z

Dale:

Common questions and answers for those migrating from MS Access to OO.o Base.

This is a user community effort - add to the knowledge base if you can.

Please however try to keep the questions and answers general. If there is an answer regarding a feature specifc version of MSA then include the version number also.

'''Specifically, please only update the wiki if you have an answer for the question you are adding.''' ( ''If you may need to get back shortly with the answer don't let that stop you from getting the queston in first however - just come back before long'' )

For asking '''specific questions''' or for general help it would be appropriate to then ask on either the:
[http://www.oooforum.org/forum/viewforum.phtml?f=10 Base Forum]
or on
[http://dba.openoffice.org/servlets/ProjectMailingListList the Base users mailing list].

== Can I use Microsoft Access databases (.mdb) in Base? ==

On Windows: yes. You cannot open them directly via <code>File|Open</code>, but you can access the data therein. For this, you need the [http://msdn.microsoft.com/data/ref/mdac/downloads Microsoft Data Access Components], which are available from Microsoft for free. (In Windows XP, they're already included, so you need them for older Windows versions only.)

Then, go to <code>File|New|Database</code>, choose "Connect to an existing datbase", and select the "Microsoft Access" database type from the list. On the next page, you then select the .mdb file you want to access - that's it!

This will allow you to read and update the data in your Acess tables.

You cannot read / modify or use the Access forms, queries, or reports.

== Can I read my Base database from MS Access? ==
There is no way to use the table linking mechanisim in Access to connect to a Base database.

It is possible for a Base database to update data in the Access database. At this time this will require however that the Access database be opened and locked by the Base file.

Data could also be passed back to the Access database by creating a linked CSV file in the Base database. Then linking to this CSV file to a table in the Access database.

== Can I create an Access database (mdb file) with Base? ==

No. Base can not create a new Access database file; or new tables, queries, forms etc. within an existing mdb file.

== Can I create a Switchboard in Base? ==

Base does not currently have a switchboard style form wizard. It is possible to achieve a very similar effect however using either of two techinques.

=== Opening a default form via a macro ===

=== Using a stand alone form to launch the Base database ===
== Our Access databases uses "Workgroup Security" to control group/user permission to view/maintain data? ==

This question points out what is a fundamental difference between OOoBase and MSAccess . In the case of MSA (MS Access) there is one and only one database engine in use - although you may link tables in MSA to external datasource - MS Jet is still the only database engine actually used by MSA.

In Base (OOoBase) this is not the case at all. The Base database document is simply a front end ( client application ) to a variaty of database engines. In one of these forms it embeddeds a HSQLDB database into the ODB file itself, many people simply see this as the only Base database format. That is totally mistaken. In this instance of an embedded database there is no support for multiple users and of course no support for groups therefore. However, that is only one of the ways Base can be configured.

You are free to use Base as the front end to a HSQLDB database in server mode. HSQLDB is a very small footprint, low administraton database engine and in server mode has full support for users / groups. For larger numbers of users you could just as easily configure Base to work with MySQL, PostgrSQL, Firebird, Oracle or MS SqlServer.

There is another significant difference between MSA and Base regarding this then. In MSA you would deploy one MDB (or one of its variants ) file, then have each user in the workgroup see this file and open it.

In Base each user would have their own ODB file and each of these would be connected to the shared data server. In this way each user would have their own space for custom queries and reports - but it makes distribution of common forms, queries and reports more work.

== Is a Form in Base different from a Form in MSA? ==

Yes

There are very real differnces between how Forms are implemented in Access and Base.

=== Virtual forms within Base forms ===

== Where are the Delete / Upate queries? ==
Base does not currently support building delete / insert / update queries in the desinger.

=== equivilant actions ===
==== ''using the Query Designer'' ====
You can achieve the equivilant actions in the GUI using the designer and or SQL commands.

==== ''using a macro'' ====

== I want to filter my form based on the contents of a combobox? ==

== Converting my VBA modules to Base modules ==

=== What happened to me? ===

There is no exact duplicate to the ME psuedo variable in VBA.

In Access VBA 'me' has attributes of two seperate objects in OOoBasic:
TextDocument and DataForm

The TextDocument object would be used for controlling the window size and placement on the screen for instance.

The DataForm meanwhile supports result set functions, such as First, Filter, InsertRow.

In OOoBasic the psuedo variable thisComponent normaly would be the TextDocument, while thisComponent.DrawPage.Forms(0) meanwhile would reference the first DataForm owned by the TextDocument.

Perhaps a still closer equivilant is to use the object ownership hierarchy to get to the form. For example if we had a single table form with a button, then in the macro called by the 'onClick' or as OOBase calls it the 'When initializing' event we would do this: Create a macro in a library named onButtonClick( oEv as object )

sub onButtonClick( oEv as object )
' oEv is the event object passed automatially
' it has a property SOURCE which is the
' button that initiated this call

dim oForm as variant

oForm = oEv.Source.Model.Parent
' oForm is now the virtual form objct

dim oDocument as variant

oDocument = thisComponent
' oDocument is now the textDocument displayed in
' the window on screen

end sub

=== Where is findfirst? ===

Base does not appear to have a FindFirst function on the rowset like MS Access, what is the equivilant Base function?

MSAccess VBA example:

Say there is a VB combo box which lists all the prospects in the database and allows the user to select a particular prospect and then have the form automatically go to that person's data record.

Private Sub Find_Combo_AfterUpdate()
Dim strCriteria As String

strCriteria = "[Prospect_ID] =" & Me.Find_Combo

Me.recordsetclone.FindFirst (strCriteria)
If me.recordsetclone.NoMatch Then
MsgBox "No entry found"
Else
Form_F_Prospects.Bookmark = me.recordsetclone.Bookmark
End If

End Sub

For this type of simple single column search it is easy enough to create our own find first equivilant with OOoBasic:

Sub Find_Combo_AfterUpdate( OEv as Object)
' Oev is the event object automatically sent
' to sub procedure call
' Oev will have a property Source
' that is the control that triggered
' the call to this procedure

Dim oForm as variant ' The dataform obejct

Dim oComboBox as variant ' The combobox control

Dim oBkMark as variant

oForm = oEv.Source.Model.Parent

oComboBox = oForm.getByName( "Find_Combo" )

oBkMark = FindFirst( oForm.CreateResultSet(), "Prospect_ID", oComboBox.Text )
' oForm.CreateResultSet() is equivilant here to me.recordsetclone
if not IsEmpty( oBkMark ) then
oForm.moveToBookmark( oBkMark )
else
msgBox( oComboBox.Text & " not found" )
end if

end sub

function FindFirst( oRS as variant, ColName as string, SearchVal as string ) as variant
' oRS is a resultSet object
' ColName is the name of a column in the resultset
' SearchVal is
dim colIDX as integer
dim NotFound as variant

FindFirst = notFound
colIDX = oRS.FindColumn( ColName )
oRS.First
do
if oRS.getString( colIDX ) <> Searchval
oRS.Next
if oRS.isAfterLast then
exit do
endif
else
FindFirst = oRS.getBookMark
exit do
end if
while oRS.isAfterLast <> True

end function

=== How do I set the filter for a sub-form only? ===

=== Is there an equivilant to DoCmd RunSQL ===

Yes, in OOoBase you would use either a Statement or PreparedStatement object to achieve the same function. With OOoBase you must also decide between using execute and executeUpdate.

For example if you had the VBA line
DoCmd RunSQL( "SELECT * FROM tContacts" )

it would become in OOoBase

Dim oStatement as variant

oStatement = OForm.ActiveConnection.CreateStatement
' Where oForm is a DataForm object
' NOTE you could do exactly the same thing with
' a resultSet or rowSet obejct also
' oStatement = OResultSet.ActiveConnection.CreateStatement

oStatement.execute( "SELECT * FROM ""tContacts""" )

If on the other hand the VBA line where
DoCmd RunSQL( "DELETE FROM tContacts WHERE Status = 'INACTIVE'" )

then we would use the executeUpdate method on a statement

oStatement.executeUpdate( "DELETE FROM ""tContacts"" WHERE ""Status"" = 'INACTIVE'" )

The rule is -
*if your SQL statement will return records you use execute or executeQuery.
*if the SQL statement returns no records then you must use executeUpdate.

== If I use Lookup fields/columns in my Access tables what can I do in Base? ==
There are two parts to this answer, given that Base does not support Lookup column types in tables.

When a table is displayed in a custom form use of a bound listbox will give you the same functionality. An example of [[adding a listbox to a form]] and to a grid control on a form is at this page.

In a table view ( double clicking on the table name ) there is no equivilant to this.

==How do I use Domain Aggregate functions in Base?==

Domain Aggregate functions such as '''DCount''', '''DLookup''', '''DMax''' etc have no direct equivalent in Base. You will have to write or design queries to return these results using the appropriate SQL functions.

==How do I add Calculated Controls to my forms and reports?==

Controls in Base forms and reports cannot be used to perform calculations. There are two methods available to get a calculated result on a form or report.

Using a query
Modify the underlying data source of the form or report to perform the calculation for each record return the result as a (calculated) field.

Using a macro
If you need your control to update as data is entered into a form, you will need to write a small macro to perform the calculation and assign it to an appropriate event on one of the data entry controls (eg. the Text modified event).

[[Category:Database]]
[[Category:Base Documentation]]

MSA-Base Faq

2006-10-02T11:54:49Z

Dale: /* If I use DLookup columns in MS Access what can I do in Base? */

Common questions and answers for those migrating from MS Access to OO.o Base.

This is a user community effort - add to the knowledge base if you can.

Please however try to keep the questions and answers general. If there is an answer regarding a feature specifc version of MSA then include the version number also.

'''Specifically, please only update the wiki if you have an answer for the question you are adding.''' ( ''If you may need to get back shortly with the answer don't let that stop you from getting the queston in first however - just come back before long'' )

For asking '''specific questions''' or for general help it would be appropriate to then ask on either the:
[http://www.oooforum.org/forum/viewforum.phtml?f=10 Base Forum]
or on
[http://dba.openoffice.org/servlets/ProjectMailingListList the Base users mailing list].

== Can I use Microsoft Access databases (.mdb) in Base? ==

On Windows: yes. You cannot open them directly via <code>File|Open</code>, but you can access the data therein. For this, you need the [http://msdn.microsoft.com/data/ref/mdac/downloads Microsoft Data Access Components], which are available from Microsoft for free. (In Windows XP, they're already included, so you need them for older Windows versions only.)

Then, go to <code>File|New|Database</code>, choose "Connect to an existing datbase", and select the "Microsoft Access" database type from the list. On the next page, you then select the .mdb file you want to access - that's it!

This will allow you to read and update the data in your Acess tables.

You cannot read / modify or use the Access forms, queries, or reports.

== Can I read my Base database from MS Access? ==
There is no way to use the table linking mechanisim in Access to connect to a Base database.

It is possible for a Base database to update data in the Access database. At this time this will require however that the Access database be opened and locked by the Base file.

Data could also be passed back to the Access database by creating a linked CSV file in the Base database. Then linking to this CSV file to a table in the Access database.

== Can I create an Access database (mdb file) with Base? ==

No. Base can not create a new Access database file; or new tables, queries, forms etc. within an existing mdb file.

== Can I create a Switchboard in Base? ==

Base does not currently have a switchboard style form wizard. It is possible to achieve a very similar effect however using either of two techinques.

=== Opening a default form via a macro ===

=== Using a stand alone form to launch the Base database ===
== Our Access databases uses "Workgroup Security" to control group/user permission to view/maintain data? ==

This question points out what is a fundamental difference between OOoBase and MSAccess . In the case of MSA (MS Access) there is one and only one database engine in use - although you may link tables in MSA to external datasource - MS Jet is still the only database engine actually used by MSA.

In Base (OOoBase) this is not the case at all. The Base database document is simply a front end ( client application ) to a variaty of database engines. In one of these forms it embeddeds a HSQLDB database into the ODB file itself, many people simply see this as the only Base database format. That is totally mistaken. In this instance of an embedded database there is no support for multiple users and of course no support for groups therefore. However, that is only one of the ways Base can be configured.

You are free to use Base as the front end to a HSQLDB database in server mode. HSQLDB is a very small footprint, low administraton database engine and in server mode has full support for users / groups. For larger numbers of users you could just as easily configure Base to work with MySQL, PostgrSQL, Firebird, Oracle or MS SqlServer.

There is another significant difference between MSA and Base regarding this then. In MSA you would deploy one MDB (or one of its variants ) file, then have each user in the workgroup see this file and open it.

In Base each user would have their own ODB file and each of these would be connected to the shared data server. In this way each user would have their own space for custom queries and reports - but it makes distribution of common forms, queries and reports more work.

== Is a Form in Base different from a Form in MSA? ==

Yes

There are very real differnces between how Forms are implemented in Access and Base.

=== Virtual forms within Base forms ===

== Where are the Delete / Upate queries? ==
Base does not currently support building delete / insert / update queries in the desinger.

=== equivilant actions ===
==== ''using the Query Designer'' ====
You can achieve the equivilant actions in the GUI using the designer and or SQL commands.

==== ''using a macro'' ====

== I want to filter my form based on the contents of a combobox? ==

== Converting my VBA modules to Base modules ==

=== What happened to me? ===

There is no exact duplicate to the ME psuedo variable in VBA.

In Access VBA 'me' has attributes of two seperate objects in OOoBasic:
TextDocument and DataForm

The TextDocument object would be used for controlling the window size and placement on the screen for instance.

The DataForm meanwhile supports result set functions, such as First, Filter, InsertRow.

In OOoBasic the psuedo variable thisComponent normaly would be the TextDocument, while thisComponent.DrawPage.Forms(0) meanwhile would reference the first DataForm owned by the TextDocument.

Perhaps a still closer equivilant is to use the object ownership hierarchy to get to the form. For example if we had a single table form with a button, then in the macro called by the 'onClick' or as OOBase calls it the 'When initializing' event we would do this: Create a macro in a library named onButtonClick( oEv as object )

sub onButtonClick( oEv as object )
' oEv is the event object passed automatially
' it has a property SOURCE which is the
' button that initiated this call

dim oForm as variant

oForm = oEv.Source.Model.Parent
' oForm is now the virtual form objct

dim oDocument as variant

oDocument = thisComponent
' oDocument is now the textDocument displayed in
' the window on screen

end sub

=== Where is findfirst? ===

Base does not appear to have a FindFirst function on the rowset like MS Access, what is the equivilant Base function?

MSAccess VBA example:

Say there is a VB combo box which lists all the prospects in the database and allows the user to select a particular prospect and then have the form automatically go to that person's data record.

Private Sub Find_Combo_AfterUpdate()
Dim strCriteria As String

strCriteria = "[Prospect_ID] =" & Me.Find_Combo

Me.recordsetclone.FindFirst (strCriteria)
If me.recordsetclone.NoMatch Then
MsgBox "No entry found"
Else
Form_F_Prospects.Bookmark = me.recordsetclone.Bookmark
End If

End Sub

For this type of simple single column search it is easy enough to create our own find first equivilant with OOoBasic:

Sub Find_Combo_AfterUpdate( OEv as Object)
' Oev is the event object automatically sent
' to sub procedure call
' Oev will have a property Source
' that is the control that triggered
' the call to this procedure

Dim oForm as variant ' The dataform obejct

Dim oComboBox as variant ' The combobox control

Dim oBkMark as variant

oForm = oEv.Source.Model.Parent

oComboBox = oForm.getByName( "Find_Combo" )

oBkMark = FindFirst( oForm.CreateResultSet(), "Prospect_ID", oComboBox.Text )
' oForm.CreateResultSet() is equivilant here to me.recordsetclone
if not IsEmpty( oBkMark ) then
oForm.moveToBookmark( oBkMark )
else
msgBox( oComboBox.Text & " not found" )
end if

end sub

function FindFirst( oRS as variant, ColName as string, SearchVal as string ) as variant
' oRS is a resultSet object
' ColName is the name of a column in the resultset
' SearchVal is
dim colIDX as integer
dim NotFound as variant

FindFirst = notFound
colIDX = oRS.FindColumn( ColName )
oRS.First
do
if oRS.getString( colIDX ) <> Searchval
oRS.Next
if oRS.isAfterLast then
exit do
endif
else
FindFirst = oRS.getBookMark
exit do
end if
while oRS.isAfterLast <> True

end function

=== How do I set the filter for a sub-form only? ===

=== Is there an equivilant to DoCmd RunSQL ===

Yes, in OOoBase you would use either a Statement or PreparedStatement object to achieve the same function. With OOoBase you must also decide between using execute and executeUpdate.

For example if you had the VBA line
DoCmd RunSQL( "SELECT * FROM tContacts" )

it would become in OOoBase

Dim oStatement as variant

oStatement = OForm.ActiveConnection.CreateStatement
' Where oForm is a DataForm object
' NOTE you could do exactly the same thing with
' a resultSet or rowSet obejct also
' oStatement = OResultSet.ActiveConnection.CreateStatement

oStatement.execute( "SELECT * FROM ""tContacts""" )

If on the other hand the VBA line where
DoCmd RunSQL( "DELETE FROM tContacts WHERE Status = 'INACTIVE'" )

then we would use the executeUpdate method on a statement

oStatement.executeUpdate( "DELETE FROM ""tContacts"" WHERE ""Status"" = 'INACTIVE'" )

The rule is -
*if your SQL statement will return records you use execute or executeQuery.
*if the SQL statement returns no records then you must use executeUpdate.

== If I use Lookup fields/columns in my Access tables what can I do in Base? ==
There are two parts to this answer, given that Base does not support Lookup column types in tables.

When a table is displayed in a custom form use of a bound listbox will give you the same functionality. An example of [[adding a listbox to a form]] and to a grid control on a form is at this page.

In a table view ( double clicking on the table name ) there is no equivilant to this.

[[Category:Database]]
[[Category:Base Documentation]]

Talk:MSA-Base Faq

2006-10-02T00:43:51Z

Dale: /* Can Base read my Access database */

I edited vLookup to Dlookup. AFAIK Access does not have a vLookup function. If vLookup was intended and correct, pls change it back.
Dale.

== How do I import/convert my Access database to Base ==

We seem to be missing a couple of obvious FAQs. Probably because the page has just started and no one has got around to them yet.

How do I import/convert my Access database to Base?

As of ver 2.0 there is no direct method for this. Tables can be read from the mdb or converted to a more Base-friendly format (native Base tables, Calc spreadsheet, csv). <<Probably need some indication of method here. Or maybe reference to a separate (yet to be written) HowTo>>

Forms and reports cannot be imported and have to be re created from scratch.

There is no direct equivalent to Access macros. These have to be re created from scratch in a language supported by OOo (StarBasic, ...)

Code modules:
OOo and Base do not support VBA. The code used in you Access database will have to be rewritten in a language supported by OOo.

+ + + + +
Dale

Talk:MSA-Base Faq

2006-09-29T03:10:21Z

Dale: /* Is a Form in Base different from a Form in MSA? */

I edited vLookup to Dlookup. AFAIK Access does not have a vLookup function. If vLookup was intended and correct, pls change it back.
Dale.

== Can Base read my Access database ==

We seem to be missing a couple of obvious FAQs. Probably because the page has just started and they no one has got around to them yet.

Can Base read/write/open my Access database?
As of version 2.0:
Tables - yes. Need some instruction (sorry - I'm completely ignorant of how to do this)
Queries - no (was there something about table views for Access queries?)
Forms - no.
Reports - no.
Macros - no.
Modules - no.

+ + + + +
Dale.

Later: this is already addressed on the FAQ(Base) page. I suppose the question now is where does it best belong?

quote

Can I use Microsoft Access databases (.mdb) in Base?

On Windows: yes. You cannot open them directly via <code>File|Open</code>, but you can access the data therein. For this, you need the [http://msdn.microsoft.com/data/ref/mdac/downloads Microsoft Data Access Components], which are available from Microsoft for free. (In Windows XP, they're already included, so you need them for older Windows versions only.)

Then, go to <code>File|New|Database</code>, choose "Connect to an existing datbase", and select the "Microsoft Access" database type from the list. On the next page, you then select the .mdb file you want to access - that's it!

== How do I import/convert my Access database to Base ==

We seem to be missing a couple of obvious FAQs. Probably because the page has just started and no one has got around to them yet.

How do I import/convert my Access database to Base?

As of ver 2.0 there is no direct method for this. Tables can be read from the mdb or converted to a more Base-friendly format (native Base tables, Calc spreadsheet, csv). <<Probably need some indication of method here. Or maybe reference to a separate (yet to be written) HowTo>>

Forms and reports cannot be imported and have to be re created from scratch.

There is no direct equivalent to Access macros. These have to be re created from scratch in a language supported by OOo (StarBasic, ...)

Code modules:
OOo and Base do not support VBA. The code used in you Access database will have to be rewritten in a language supported by OOo.

+ + + + +
Dale

MSA-Base Faq

2006-09-28T01:52:46Z

Dale: /* Can I create an Access database (mdb file) with Base? */

Talk:MSA-Base Faq

2006-09-28T01:48:57Z

Dale:

Talk:MSA-Base Faq

2006-09-28T01:41:34Z

Dale:

I edited vLookup to Dlookup. AFAIK Access does not have a vLookup function. If vLookup was intended and correct, pls change it back.
Dale.

== Can Base read my Access database ==

We seem to be missing a couple of obvious FAQs. Probably because the page has just started and they no one has got around to them yet.

Can Base read/write/open my Access database?
As of version 2.0:
Tables - yes. Need some instruction (sorry - I'm completely ignorant of how to do this)
Queries - no (was there something about table views for Access queries?)
Forms - no.
Reports - no.
Macros - no.
Modules - no.

+ + + + +
Dale.

How do I import/convert my Access database to Base

We seem to be missing a couple of obvious FAQs. Probably because the page has just started and no one has got around to them yet.

How do I import/convert my Access database to Base?

As of ver 2.0 there is no direct method for this. Tables can be read from the mdb or converted to a more Base-friendly format (native Base tables, Calc spreadsheet, csv). <<Probably need some indication of method here. Or maybe reference to a separate (yet to be written) HowTo>>

Forms and reports cannot be imported and have to be re created from scratch.

There is no direct equivalent to Access macros. These have to be re created from scratch in a language supported by OOo (StarBasic, ...)

Code modules:
OOo and Base do not support VBA. The code used in you Access database will have to be rewritten in a language supported by OOo.

+ + + + +
Dale

Later: this is already addressed on the FAQ(Base) page. I suppose the question now is where does it best belong?

quote

=== Can I use Microsoft Access databases (.mdb) in Base? ===

On Windows: yes. You cannot open them directly via <code>File|Open</code>, but you can access the data therein. For this, you need the [http://msdn.microsoft.com/data/ref/mdac/downloads Microsoft Data Access Components], which are available from Microsoft for free. (In Windows XP, they're already included, so you need them for older Windows versions only.)

Then, go to <code>File|New|Database</code>, choose "Connect to an existing datbase", and select the "Microsoft Access" database type from the list. On the next page, you then select the .mdb file you want to access - that's it!

== Is a Form in Base different from a Form in MSA? ==

quote

Is a Form in Base different from a Form in MSA?
No.

There are very real differnces between how Forms are implemented in Access and Base.

/quote

I might be missing something, but shouldn't this be "Yes. There are ..."

Talk:MSA-Base Faq

2006-09-28T01:39:33Z

Dale: Is a Form in Base different from a Form in MSA?

I edited vLookup to Dlookup. AFAIK Access does not have a vLookup function. If vLookup was intended and correct, pls change it back.
Dale.

== Can Base read my Access database ==

We seem to be missing a couple of obvious FAQs. Probably because the page has just started and they no one has got around to them yet.

Can Base read/write/open my Access database?
As of version 2.0:
Tables - yes. Need some instruction (sorry - I'm completely ignorant of how to do this)
Queries - no (was there something about table views for Access queries?)
Forms - no.
Reports - no.
Macros - no.
Modules - no.

+ + + + +
Dale.

== How do I import/convert my Access database to Base ==

We seem to be missing a couple of obvious FAQs. Probably because the page has just started and no one has got around to them yet.

How do I import/convert my Access database to Base?

As of ver 2.0 there is no direct method for this. Tables can be read from the mdb or converted to a more Base-friendly format (native Base tables, Calc spreadsheet, csv). <<Probably need some indication of method here. Or maybe reference to a separate (yet to be written) HowTo>>

Forms and reports cannot be imported and have to be re created from scratch.

There is no direct equivalent to Access macros. These have to be re created from scratch in a language supported by OOo (StarBasic, ...)

Code modules:
OOo and Base do not support VBA. The code used in you Access database will have to be rewritten in a language supported by OOo.

+ + + + +
Dale

Later: this is already addressed on the FAQ(Base) page. I suppose the question now is where does it best belong?

quote

=== Can I use Microsoft Access databases (.mdb) in Base? ===

On Windows: yes. You cannot open them directly via <code>File|Open</code>, but you can access the data therein. For this, you need the [http://msdn.microsoft.com/data/ref/mdac/downloads Microsoft Data Access Components], which are available from Microsoft for free. (In Windows XP, they're already included, so you need them for older Windows versions only.)

Then, go to <code>File|New|Database</code>, choose "Connect to an existing datbase", and select the "Microsoft Access" database type from the list. On the next page, you then select the .mdb file you want to access - that's it!

== Is a Form in Base different from a Form in MSA? ==

quote

Is a Form in Base different from a Form in MSA?
No.

There are very real differnces between how Forms are implemented in Access and Base.

/quote

I might be missing something, but shouldn't this be "Yes. There are ..."

MSA-Base Faq

2006-09-28T01:36:50Z

Dale:

Talk:MSA-Base Faq

2006-09-28T01:31:39Z

Dale: /* How do I import/convert my Access database to Base */

I edited vLookup to Dlookup. AFAIK Access does not have a vLookup function. If vLookup was intended and correct, pls change it back.
Dale.

== Can Base read my Access database ==

We seem to be missing a couple of obvious FAQs. Probably because the page has just started and they no one has got around to them yet.

Can Base read/write/open my Access database?
As of version 2.0:
Tables - yes. Need some instruction (sorry - I'm completely ignorant of how to do this)
Queries - no (was there something about table views for Access queries?)
Forms - no.
Reports - no.
Macros - no.
Modules - no.

+ + + + +
Dale.

== How do I import/convert my Access database to Base ==

We seem to be missing a couple of obvious FAQs. Probably because the page has just started and no one has got around to them yet.

How do I import/convert my Access database to Base?

As of ver 2.0 there is no direct method for this. Tables can be read from the mdb or converted to a more Base-friendly format (native Base tables, Calc spreadsheet, csv). <<Probably need some indication of method here. Or maybe reference to a separate (yet to be written) HowTo>>

Forms and reports cannot be imported and have to be re created from scratch.

There is no direct equivalent to Access macros. These have to be re created from scratch in a language supported by OOo (StarBasic, ...)

Code modules:
OOo and Base do not support VBA. The code used in you Access database will have to be rewritten in a language supported by OOo.

+ + + + +
Dale

Later: this is already addressed on the FAQ(Base) page. I suppose the question now is where does it best belong?

quote

=== Can I use Microsoft Access databases (.mdb) in Base? ===

On Windows: yes. You cannot open them directly via <code>File|Open</code>, but you can access the data therein. For this, you need the [http://msdn.microsoft.com/data/ref/mdac/downloads Microsoft Data Access Components], which are available from Microsoft for free. (In Windows XP, they're already included, so you need them for older Windows versions only.)

Then, go to <code>File|New|Database</code>, choose "Connect to an existing datbase", and select the "Microsoft Access" database type from the list. On the next page, you then select the .mdb file you want to access - that's it!

Talk:MSA-Base Faq

2006-09-28T00:33:09Z

Dale: /* How do I import/convert my Access database to Base */

Talk:MSA-Base Faq

2006-09-28T00:32:16Z

Dale: How do I import/convert my Access database to Base

Talk:MSA-Base Faq

2006-09-28T00:18:36Z

Dale: Can Base read my Access database

Talk:MSA-Base Faq

2006-09-27T23:35:25Z

Dale:

I edited vLookup to Dlookup. AFAIK Access does not have a vLookup function. If vLookup was intended and correct, pls change it back.
Dale.

Talk:MSA-Base Faq

2006-09-27T23:34:59Z

Dale:

I edited vLookup to Dlookup. AFAIK Access does not have a vLookup function. If vLookup was intended and correct, pls change it back.

MSA-Base Faq

2006-09-27T23:33:06Z

Dale: /* If I use DLookup columns in MS Access what can I do in Base? */