Difference between revisions of "MSA-Base Faq"

From Apache OpenOffice Wiki
Jump to: navigation, search
 
(96 intermediate revisions by 3 users not shown)
Line 1: Line 1:
Common questions and answers for those migrating from [[Microsoft Access]] (MSA) to OpenOffice.org [[Base]].
+
Common questions and answers for those migrating from [[Microsoft Access]] (MSA) to OpenOffice [[Base]].
  
 
This is a user community effort - add to the knowledge base if you can.
 
This is a user community effort - add to the knowledge base if you can.
Line 8: Line 8:
  
 
For asking '''specific questions''' or for general help it would be appropriate to then ask on either the:
 
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]  
+
[https://www.oooforum.org/forum/viewforum.phtml?f=10 Base Forum]  
 
or on   
 
or on   
[http://dba.openoffice.org/servlets/ProjectMailingListList the Base users mailing list].
+
[https://dba.openoffice.org/servlets/ProjectMailingListList the Base users mailing list].
  
 
== Can I use Microsoft Access databases (.mdb) in Base? ==
 
== 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 an Open Document Base file (*.odb), which holds the connection information. For details see [[Microsoft Access]] or [[Connecting to Microsoft Access]].
+
On Windows: yes. You cannot open them directly via {{menu|File|Open}}, but you can access the data therein. For this, you need an Open Document Base file (*.odb), which holds the connection information. For details see [[Microsoft Access]] or [[Connecting to Microsoft Access]].
  
 
You can modify the data in the tables and you can add and remove records.
 
You can modify the data in the tables and you can add and remove records.
Line 24: Line 24:
  
 
== Can I read my Base database from MS Access (MSA)? ==
 
== Can I read my Base database from MS Access (MSA)? ==
That depends. There's no way to use the table linking mechanism in MSA to connect to an "embedded database" stored within a Base .odb file. It is possible to [http://hsqldb.org/doc/2.0/guide/openoffice-app.html#ooa_db_files_convert extract] the (native HSQLDB) database files manually ([http://www.oooforum.org/forum/viewtopic.phtml?p=386995#386995 step-by-step]) from a Base .odb file, for indirect access through the "[http://en.wikipedia.org/wiki/Java_Database_Connectivity JDBC]" driver/engine bundled with OpenOffice.org (hsqldb.jar). This can be a [http://user.services.openoffice.org/en/forum/viewtopic.php?p=162653#p162653 preferred configuration with Base] to ensure data [http://en.wikipedia.org/wiki/ACID#Durability durability]. However, MS Access doesn't offer JDBC linking, so a [http://stackoverflow.com/a/5403672 third-party ODBC-to-JDBC] "bridge" or "gateway" ([http://uda.openlinksw.com/images/odbcjdbcst.gif  diagram]) would be required to connect to the extracted database files. Another approach involves further database migration to an [http://en.wikipedia.org/wiki/RDBMS RDBMS] offering "[http://www.utteraccess.com/wiki/index.php/Beginners_Guide_To_ODBC ODBC]" connectivity. Simply [http://hsqldb.org/doc/2.0/guide/deployment-chapt.html#dec_upgrade_database upgrading Base to HSQLDB 2.x] is not the immediate solution for MSA linking because the necessary "ODBC" option is [http://hsqldb.org/web/hsqlDevelopment.html still under development]. But there's plenty of ODBC-compatible options for Base/MSA, including [http://en.wikipedia.org/wiki/SQL_Server_Express SQL Server Express], [http://www.h2database.com/html/advanced.html#odbc_driver H2] ([http://www.oooforum.org/forum/viewtopic.phtml?t=97522 tutorial]), [http://sqlite.awardspace.info/syntax/sqliteodbc.htm SQLite], [http://www.postgresql.org PostgreSQL], [http://www.mysql.com/downloads/mysql/ MySQL], [http://mariadb.org/ MariaDB], [http://www.firebirdsql.org/ Firebird], MS SQL Server, Oracle, etc.  
+
That depends. There's no way to to link to tables stored within a Base .odb file. But there's two types of .odb files generated by Base; those with and those without an "embedded database." So if your Base .odb file is simply linked to an external, MSA-compatible, data source, particularly those offering an "[http://www.utteraccess.com/wiki/index.php/Beginners_Guide_To_ODBC ODBC]" connection option, then MSA will ''''<u>L</u>ink to the data source by creating a linked table'''.' And you get full read/write access with the "ODBC" option. Changes made in MSA will be reflected in the data source, and therefore in Base, and vice versa.
  
It is possible to use a Base .odb file to update data in an Access database (.mdb) on a Windows machine. This requires the Access database be opened and locked by the Base file.
+
Technically, it is possible to [https://hsqldb.org/doc/2.0/guide/openoffice-app.html#ooa_db_files_convert extract] an "embedded database" from a Base .odb file through a [http://www.oooforum.org/forum/viewtopic.phtml?p=386995#386995 manual extraction process]. This is necessary for instance to upgrade from the built-in HSQLDB 1.8 engine to the latest [http://hsqldb.org/doc/2.0/guide/deployment-chapt.html#dec_upgrade_database HSQLDB 2.x] engine in order to take advantage of newer HSQLDB features which [https://hsqldb.org/web/hsqlDevelopment.html may soon] include an "ODBC" option as necessary for MSA linking. But for now HSQLDB only provides Java-based "[https://en.wikipedia.org/wiki/Java_Database_Connectivity JDBC]" linking, which is noticeably absent from MSA's link options. So a [http://stackoverflow.com/a/5403672 third-party ODBC-to-JDBC] "bridge" or "gateway" ([http://uda.openlinksw.com/images/odbcjdbcst.gif diagram]) is currently required to link MSA to a database generated by the HSQLDB engine as bundled with OpenOffice.org.  
  
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.
+
As an alternative to "bridging" it should be possible to [http://www.oooforum.org/forum/viewtopic.phtml?p=460854#460854 migrate your HSQL database] to one that offers "ODBC" connectivity. Most options are compatible with both MSA and Base including: [http://en.wikipedia.org/wiki/SQL_Server_Express SQL Server Express], [http://www.h2database.com/html/advanced.html#odbc_driver H2] ([http://www.oooforum.org/forum/viewtopic.phtml?t=97522 tutorial]), [http://sqlite.awardspace.info/syntax/sqliteodbc.htm SQLite], [http://www.postgresql.org PostgreSQL], [http://www.mysql.com/downloads/mysql/ MySQL], [http://mariadb.org/ MariaDB], [http://www.firebirdsql.org/ Firebird], MS SQL Server, Oracle, etc.
 +
 
 +
It is possible to use a Base .odb file to update data in a MSA database (.mdb), but only from a Windows machine. This requires the MSA database be opened and locked by the Base file.
 +
 
 +
Data could also be passed to a MSA database by linking both MSA and Base to the same CSV file(s).
  
 
== Can I create an Access database (mdb file) with Base? ==
 
== Can I create an Access database (mdb file) with Base? ==
Line 40: Line 44:
 
=== Opening a default form via a macro ===
 
=== Opening a default form via a macro ===
  
=== Using a stand alone form to launch the Base database ===
+
=== Use a stand alone form to launch the Base database ===
see [http://user.services.openoffice.org/en/forum/viewtopic.php?f=83&t=40493 Tutorial: Standalone Forms / Switchboard]
+
see [https://user.services.openoffice.org/en/forum/viewtopic.php?f=83&t=40493 Tutorial: Standalone Forms / Switchboard]
  
 
=== Base Switchboard extension ===
 
=== Base Switchboard extension ===
Development started to bring switchboard feature into OOo, see the Roberto Benitez's [http://extensions.services.openoffice.org/project/SwitchBoard Base Switchboard extension].
+
Development started to bring switchboard feature into Apache OO, see the Roberto Benitez's [https://extensions.openoffice.org/project/SwitchBoard Base Switchboard extension].
  
 
== Our MS Access (MSA) databases uses "Workgroup Security" to control group/user permission to view/maintain data. Is similar access control and security possible with Base? ==
 
== Our MS Access (MSA) databases uses "Workgroup Security" to control group/user permission to view/maintain data. Is similar access control and security possible with Base? ==
  
This question highlights some fundamental differences between Base and MS Access (MSA). In the case of MSA, the integral Jet/ACE engine is involved in all data access -- including linked data-sources. This presents a problem for MSA developers because you must choose between "workgroup" (access control) and "security" (encryption) functions -- which are normally combined for effective "[http://en.wikipedia.org/wiki/Database#Database_security database security]." Specifically, the Jet-MDB database format (MSA 2003 and earlier) is [http://www.everythingaccess.com/tutorials.asp?ID=Jet-MDB-security---under-the-hood not truly "secure"] but it does offer "workgroup" (access control) functions including individual user-login and user/group permissions. On the other hand, the ACE-ACCDB file format (MSA 2007 and later) is relatively "secure" (encryption) but forgoes the "workgroup" function in favor of a single login. Perhaps throwing-in-the-towel (or in promotion of MS SQL Server), Microsoft has [http://office.microsoft.com/en-us/access-help/set-or-change-access-2003-user-level-security-in-access-2010-HA010341994.aspx depreciated "Workgroup Security" in MSA 2010], but continues to support the legacy function of the Jet engine for MDB files created with MSA 2003 or earlier. So in reality, MSA's "Workgroup Security" has been a bit of an [http://en.wikipedia.org/wiki/Oxymoron oxymoron].  
+
This question highlights some fundamental differences between Base and MS Access (MSA). MSA's Jet/ACE engine is integral so even linked data-sources are accessed through Jet/ACE. This has been a problem for "[http://en.wikipedia.org/wiki/Database#Database_security database security]" because the all-in-one MDB/ACCDB file supports either "workgroup" (access control) or "security" (encryption) but not both. Specifically, the Jet-MDB database format (MSA 2003 and earlier) is [http://www.everythingaccess.com/tutorials.asp?ID=Jet-MDB-security---under-the-hood not truly "secure"] but it does offer "workgroup" (access control) functions including individual user-login and user/group permissions. This could be combined with security features of the host file system (NTFS/EFS) to formulate crude database security. On the other hand, the ACE-ACCDB file format (MSA 2007) is relatively "secure" (encryption) but forgoes the "workgroup" function in favor of a single login. So MSA's "Workgroup Security" has been little more than an [http://en.wikipedia.org/wiki/Oxymoron oxymoron]. Perhaps so much so, that Microsoft wisely decided to [http://office.microsoft.com/en-us/access-help/set-or-change-access-2003-user-level-security-in-access-2010-HA010341994.aspx depreciate "Workgroup Security" in MSA 2010] -- but continues to support the legacy function of the Jet engine for MDB files created with MSA 2003 or earlier.  
 +
 
 +
So database security has now formally shifted away from built-in Jet/ACE engine, such that MSA relies primarily on the security provided by linked data-source engines. Perhaps along those lines, MSA 2010 offers to '[http://office.microsoft.com/en-us/access-help/ways-to-share-an-access-database-HA010342110.aspx#BM4 split the database]' into front-end and back-end files. This can be accomplished manually by eliminating all Tables in a copy of the MDB/ACCDB file. But either way you'll need to skip the Jet/ACE back-end, using only the MDB/ACCDB front-end to link to a full-featured database engine supporting robust database security.  
  
That's not the case with Base. Although the Base 'user authentication' feature could use [http://www.oooforum.org/forum/viewtopic.phtml?p=466086#466086 some improvements], Base is simply a front-end (client application) to a variety of database engines. The engines, themselves, provide various levels of database security from ''none'' in dBase or SQLite; to role based access control ([http://en.wikipedia.org/wiki/Rbac RBAC]) ''alone'' in the built-in HSQLDB engine; to ''both'' RBAC and database encryption in more advanced engines.  
+
This is now very similar to Base. In Base, all multi-user databases are "split." The Base 'user authentication' feature could use [http://www.oooforum.org/forum/viewtopic.phtml?p=466086#466086 some improvements], but Base is purely a front-end (client application) to a variety of database engines. The engines, themselves, provide various levels of database security: from ''none'' with dBase files or SQLite; to role based access control ([http://en.wikipedia.org/wiki/Rbac RBAC]) using the built-in HSQLDB engine; to ''both'' RBAC and database encryption with more advanced engines.  
  
Base's bundled HSQLDB engine is similar to MSA's Jet/ACE engine in terms of multi-user function. You are free to run this built-in HSQLDB engine in [http://user.services.openoffice.org/en/forum/viewtopic.php?p=162653#p162653 server mode] in support of multiple users. The provided 'role based access control' is on par with the Jet-MDB "workgroup" function. Security is likewise [http://stackoverflow.com/a/8833306 limited]. Otherwise, the primary role of this engine is in support of the "embedded database" configuration which 'embeds' the database data inside the Base ODB container file, similar to an MSA file. But unlike MSA, this Base "embedded database" configuration is single-user and the file, itself, is easily corrupted so it's not recommended beyond database-prototyping and examples. So with Base, a production database should ''always'' be connected externally using an ODB file generated by the "Connect to an existing database" wizard -- whether single-user or multi-user. In this robust configuration, no back-end database data is stored within the Base ODB file. Only front-end components such as Base Queries, Forms, Reports and Macros are stored within the ODB file. This is similar to the 'split database' function in MSA 2010.
+
For comprehensive database security with Base, including encryption, consider HSQLDB 2.x, H2, Apache Derby, PostgreSQL 9.1 or later, Oracle with the Advanced Security option, and of course MS SQL Server 2008 or later with TDE. If you're supporting a very large number of users, and/or need concurrent MSA front-end connectivity (requiring ODBC), then select among the latter options: PostgreSQL, Oracle, or MS SQL Server.
  
For comprehensive database security with Base, including encryption, consider HSQLDB 2.x, H2, Apache Derby, PostgreSQL 9.1 or later, Oracle with the Advanced Security option, and of course MS SQL Server 2008 or later with TDE. If you're supporting a very large number of users select the latter: PostgreSQL, Oracle, or MS SQL Server.
+
The Base-bundled HSQLDB engine is similar to MSA's Jet/ACE engine in terms of multi-user function. HSQLDB must be run in [http://user.services.openoffice.org/en/forum/viewtopic.php?p=162653#p162653 server mode] in support of multiple users, as with alternative engines. This engine supports 'role based access control' similar to the Jet-MDB "workgroup" function. Security is likewise [http://stackoverflow.com/a/8833306 limited]. Otherwise, the primary role of the built-in HSQLDB engine is in support of the "embedded database" configuration which 'embeds' the database data inside the Base ODB container file, similar to MDB/ACCDB. This all-in-one ODB file is often referred to as a "Base database." But unlike MSA files, this "embedded database" ODB file is configured for single-user access. It's also prone to corruption, so these all-in-one ODB files are not recommended beyond database-prototyping and examples. A production database should ''always'' be deployed in a 'split' configuration using an ODB file generated by the "Connect to an existing database" wizard. In a split-configuration, no back-end database data is stored within the Base ODB file. Only front-end components such as Base Queries, Forms, Reports and Macros are stored therein. This is very similar to the 'split database' function in MSA 2010.
  
There are some considerations when deploying Base or MSA in multi-user environments. In MSA, you generally provide shared access to a single MDB/ACCDB file. In MSA 2010, you can split the ACCDB file to separate front-end and back-end functions, similar to the requisite 'existing database' configuration with Base. This split configuration allows you to share the front-end files (MDB/ACCDB/ODB) file for common access to front-end Queries, Forms, Reports and Macros, in addition to multi-user access to the back-end database. These split functions also allow the distribution of individual copies of the front-end (MDB/ACCDB/ODB) in support of personalized Queries, Forms, etc. while maintaining common access to the database(s).
+
There are some considerations when deploying a split database in multi-user environments. This split configuration allows you to share the front-end files (MDB/ACCDB/ODB) file for common access to front-end Queries, Forms, Reports and Macros, while maintaining multi-user access to the back-end database. You can also distribute personal copies of the front-end (MDB/ACCDB/ODB) to allow user-specific Queries, Forms, etc. -- while each maintains access to the central database(s). Personal copies of the front-end will provide the most robust/stable user environment/experience, although front-end updates become more difficult to distribute.
  
 
== Is a Form in Base different from a Form in MSA? ==
 
== Is a Form in Base different from a Form in MSA? ==
Line 93: Line 99:
  
 
Perhaps a still closer equivalent 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 )
 
Perhaps a still closer equivalent 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 )
 
+
<syntaxhighlight lang="oobas">
 
     sub onButtonClick( oEv as object )
 
     sub onButtonClick( oEv as object )
 
       ' oEv is the event object passed automatically
 
       ' oEv is the event object passed automatically
Line 111: Line 117:
 
      
 
      
 
     end sub
 
     end sub
 +
</syntaxhighlight>
  
 
=== Where is findfirst? ===
 
=== Where is findfirst? ===
Line 119: Line 126:
  
 
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.
 
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.
 
+
<syntaxhighlight lang="vb">
 
   Private Sub Find_Combo_AfterUpdate()
 
   Private Sub Find_Combo_AfterUpdate()
 
   Dim strCriteria As String
 
   Dim strCriteria As String
Line 133: Line 140:
 
    
 
    
 
   End Sub
 
   End Sub
 
+
</syntaxhighlight>
 
For this type of simple single column search it is easy enough to create our own find first equivalent with OOoBasic:
 
For this type of simple single column search it is easy enough to create our own find first equivalent with OOoBasic:
 
+
<syntaxhighlight lang="oobas">
 
  Sub Find_Combo_AfterUpdate( OEv as Object)
 
  Sub Find_Combo_AfterUpdate( OEv as Object)
 
     ' Oev is the event object automatically sent
 
     ' Oev is the event object automatically sent
Line 186: Line 193:
 
    
 
    
 
  end function
 
  end function
 +
</syntaxhighlight>
  
 
=== How do I set the filter for a sub-form only? ===
 
=== How do I set the filter for a sub-form only? ===
Line 194: Line 202:
  
 
For example if you had the VBA line
 
For example if you had the VBA line
 +
<syntaxhighlight lang="vb">
 
   DoCmd RunSQL( "SELECT * FROM tContacts" )
 
   DoCmd RunSQL( "SELECT * FROM tContacts" )
 +
</syntaxhighlight>
  
 
it would become in OOoBase
 
it would become in OOoBase
 
+
<syntaxhighlight lang="oobas">
 
   Dim oStatement as variant
 
   Dim oStatement as variant
 
    
 
    
Line 207: Line 217:
 
    
 
    
 
   oStatement.execute( "SELECT * FROM ""tContacts"""  )
 
   oStatement.execute( "SELECT * FROM ""tContacts"""  )
 
+
</syntaxhighlight>
 
If on the other hand the VBA line where
 
If on the other hand the VBA line where
 +
<syntaxhighlight lang="vb">
 
   DoCmd RunSQL( "DELETE FROM tContacts WHERE Status = 'INACTIVE'" )
 
   DoCmd RunSQL( "DELETE FROM tContacts WHERE Status = 'INACTIVE'" )
 +
</syntaxhighlight>
  
 
then we would use the executeUpdate method on a statement
 
then we would use the executeUpdate method on a statement
 
+
<syntaxhighlight lang="oobas">
 
   oStatement.executeUpdate( "DELETE FROM ""tContacts"" WHERE ""Status"" = 'INACTIVE'"  )
 
   oStatement.executeUpdate( "DELETE FROM ""tContacts"" WHERE ""Status"" = 'INACTIVE'"  )
 
+
</syntaxhighlight>
 
The rule is -  
 
The rule is -  
 
*if your SQL statement will return records you use execute or executeQuery.
 
*if your SQL statement will return records you use execute or executeQuery.
Line 248: Line 260:
 
[[Category:Base Documentation]]
 
[[Category:Base Documentation]]
 
[[Category:User FAQ]]
 
[[Category:User FAQ]]
 +
[http://www.christians-louboutin.org/ christian louboutin]

Latest revision as of 14:17, 14 May 2021

Common questions and answers for those migrating from Microsoft Access (MSA) to OpenOffice 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 specific 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 question 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: Base Forum or on the Base users mailing list.

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

On Windows: yes. You cannot open them directly via File → Open, but you can access the data therein. For this, you need an Open Document Base file (*.odb), which holds the connection information. For details see Microsoft Access or Connecting to Microsoft Access.

You can modify the data in the tables and you can add and remove records. You can create new tables, but you cannot alter the structure of existing tables.

Queries in MS Access are shown as views in the table container. You can use the query results, but you have no access to the underlying definitions and you cannot alter them.

You cannot read MS Access forms or reports.

Can I read my Base database from MS Access (MSA)?

That depends. There's no way to to link to tables stored within a Base .odb file. But there's two types of .odb files generated by Base; those with and those without an "embedded database." So if your Base .odb file is simply linked to an external, MSA-compatible, data source, particularly those offering an "ODBC" connection option, then MSA will 'Link to the data source by creating a linked table.' And you get full read/write access with the "ODBC" option. Changes made in MSA will be reflected in the data source, and therefore in Base, and vice versa.

Technically, it is possible to extract an "embedded database" from a Base .odb file through a manual extraction process. This is necessary for instance to upgrade from the built-in HSQLDB 1.8 engine to the latest HSQLDB 2.x engine in order to take advantage of newer HSQLDB features which may soon include an "ODBC" option as necessary for MSA linking. But for now HSQLDB only provides Java-based "JDBC" linking, which is noticeably absent from MSA's link options. So a third-party ODBC-to-JDBC "bridge" or "gateway" (diagram) is currently required to link MSA to a database generated by the HSQLDB engine as bundled with OpenOffice.org.

As an alternative to "bridging" it should be possible to migrate your HSQL database to one that offers "ODBC" connectivity. Most options are compatible with both MSA and Base including: SQL Server Express, H2 (tutorial), SQLite, PostgreSQL, MySQL, MariaDB, Firebird, MS SQL Server, Oracle, etc.

It is possible to use a Base .odb file to update data in a MSA database (.mdb), but only from a Windows machine. This requires the MSA database be opened and locked by the Base file.

Data could also be passed to a MSA database by linking both MSA and Base to the same CSV file(s).

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

No. Base cannot create a new Access database file; or new tables; or modify existing table structures/properties. Base queries, forms and reports can be added to the Base .odb file, but not to the Access .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 one of several techniques.

Opening a default form via a macro

Use a stand alone form to launch the Base database

see Tutorial: Standalone Forms / Switchboard

Base Switchboard extension

Development started to bring switchboard feature into Apache OO, see the Roberto Benitez's Base Switchboard extension.

Our MS Access (MSA) databases uses "Workgroup Security" to control group/user permission to view/maintain data. Is similar access control and security possible with Base?

This question highlights some fundamental differences between Base and MS Access (MSA). MSA's Jet/ACE engine is integral so even linked data-sources are accessed through Jet/ACE. This has been a problem for "database security" because the all-in-one MDB/ACCDB file supports either "workgroup" (access control) or "security" (encryption) but not both. Specifically, the Jet-MDB database format (MSA 2003 and earlier) is not truly "secure" but it does offer "workgroup" (access control) functions including individual user-login and user/group permissions. This could be combined with security features of the host file system (NTFS/EFS) to formulate crude database security. On the other hand, the ACE-ACCDB file format (MSA 2007) is relatively "secure" (encryption) but forgoes the "workgroup" function in favor of a single login. So MSA's "Workgroup Security" has been little more than an oxymoron. Perhaps so much so, that Microsoft wisely decided to depreciate "Workgroup Security" in MSA 2010 -- but continues to support the legacy function of the Jet engine for MDB files created with MSA 2003 or earlier.

So database security has now formally shifted away from built-in Jet/ACE engine, such that MSA relies primarily on the security provided by linked data-source engines. Perhaps along those lines, MSA 2010 offers to 'split the database' into front-end and back-end files. This can be accomplished manually by eliminating all Tables in a copy of the MDB/ACCDB file. But either way you'll need to skip the Jet/ACE back-end, using only the MDB/ACCDB front-end to link to a full-featured database engine supporting robust database security.

This is now very similar to Base. In Base, all multi-user databases are "split." The Base 'user authentication' feature could use some improvements, but Base is purely a front-end (client application) to a variety of database engines. The engines, themselves, provide various levels of database security: from none with dBase files or SQLite; to role based access control (RBAC) using the built-in HSQLDB engine; to both RBAC and database encryption with more advanced engines.

For comprehensive database security with Base, including encryption, consider HSQLDB 2.x, H2, Apache Derby, PostgreSQL 9.1 or later, Oracle with the Advanced Security option, and of course MS SQL Server 2008 or later with TDE. If you're supporting a very large number of users, and/or need concurrent MSA front-end connectivity (requiring ODBC), then select among the latter options: PostgreSQL, Oracle, or MS SQL Server.

The Base-bundled HSQLDB engine is similar to MSA's Jet/ACE engine in terms of multi-user function. HSQLDB must be run in server mode in support of multiple users, as with alternative engines. This engine supports 'role based access control' similar to the Jet-MDB "workgroup" function. Security is likewise limited. Otherwise, the primary role of the built-in HSQLDB engine is in support of the "embedded database" configuration which 'embeds' the database data inside the Base ODB container file, similar to MDB/ACCDB. This all-in-one ODB file is often referred to as a "Base database." But unlike MSA files, this "embedded database" ODB file is configured for single-user access. It's also prone to corruption, so these all-in-one ODB files are not recommended beyond database-prototyping and examples. A production database should always be deployed in a 'split' configuration using an ODB file generated by the "Connect to an existing database" wizard. In a split-configuration, no back-end database data is stored within the Base ODB file. Only front-end components such as Base Queries, Forms, Reports and Macros are stored therein. This is very similar to the 'split database' function in MSA 2010.

There are some considerations when deploying a split database in multi-user environments. This split configuration allows you to share the front-end files (MDB/ACCDB/ODB) file for common access to front-end Queries, Forms, Reports and Macros, while maintaining multi-user access to the back-end database. You can also distribute personal copies of the front-end (MDB/ACCDB/ODB) to allow user-specific Queries, Forms, etc. -- while each maintains access to the central database(s). Personal copies of the front-end will provide the most robust/stable user environment/experience, although front-end updates become more difficult to distribute.

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

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

Virtual forms within Base forms

Virtual forms in Base are called "subforms". You can manage them opening the form in Edit mode and then clicking on "Form Navigator", in "Form Design" toolbar.

Where are the Delete / Update queries?

Base does not currently support building delete / insert / update queries in the designer.

Equivalent actions

using the Query Designer

You can achieve the equivalent 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 pseudo variable in VBA.

In Access VBA 'me' has attributes of two separate 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 pseudo variable thisComponent normally would be the TextDocument, while thisComponent.DrawPage.Forms(0) meanwhile would reference the first DataForm owned by the TextDocument.

Perhaps a still closer equivalent 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 automatically
      ' 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 equivalent 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 equivalent 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
    loop while oRS.isAfterLast <> True
  
 end function

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

Is there an equivalent 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 equivalent 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 (e.g. the Text modified event).

Autor: MSA-Base Faq Drew 21:01, 11 September 2007 (CEST) '
Please do not change the logical content of this site without acknowledge of the author or the OOo QA Project Lead/Co-Leads. christian louboutin

Personal tools