Originally Answered: Is there a viable alternative to Microsoft Access? Micosoft Access falls in the middle between an end user software and a developer tool. It is an area not many companies wanted to invest in. Also, in recent years, all new products were developed for the cloud. Searching for a free desktop alternative to Microsoft Office? Look no further than LibreOffice and OpenOffice. These two open-source productivity suites are both great choices if you're looking to.
The Apache OpenOffice productivity suite was previously known as Oracle® Open Office and OpenOffice.org. LibreOffice was initially derived from the OpenOffice.org source code.
This article describes how to use OpenOffice / LibreOffice / OpenOffice.org applications (Base databases and Calc spreadsheets) to access ODBC databases for which an ODBC driver is available. You can access databases such as Microsoft SQL Server, Microsoft Access, Oracle®, Salesforce.com, DB2, Derby, InterBase and Sybase ASE from Base and Calc even when the database is on a different machine to the one where these applications are being run.
Apache OpenOffice, LibreOffice, OpenOffice.org and Linux
Easysoft ODBC drivers have been tested with:
- OpenOffice 3.0+ and LibreOffice 3.0+ on Ubuntu (Precise Pangolin, which includes unixODBC 2.2.14).
- OpenOffice.org 2.0+ on RedHat 9.0 (which includes unixODBC 2.2.3).
- OpenOffice.org 1.0+ on RedHat 8.0 (which includes unixODBC 2.2.2), Ubuntu (Edgy Eft, Feisty Fawn, Gutsy Gibbon and Hardy Heron) and Debian.
Easysoft ODBC drivers should work with any recent Linux distribution.
Example of Connecting to an ODBC Data Source from Apache OpenOffice, LibreOffice and OpenOffice.org
When testing Easysoft ODBC drivers with Apache OpenOffice and LibreOffice we used both the unixODBC Driver Manager included with the operating system and the Easysoft ODBC driver distribution. As both Apache OpenOffice and LibreOffice are built with threads, make sure that you specify the thread-safe OOB client driver ('OOB_r'), if you are using the OOB.
To use an Easysoft ODBC driver with OpenOffice.org, ensure that the unixODBC Driver Manager is installed and that the Easysoft ODBC driver is installed as an ODBC driver under unixODBC. If unixODBC is not installed already, install the version that comes with the Easysoft ODBC driver distribution. OpenOffice.org is built with threads, so if you build unixODBC yourself, use --enable-threads=yes on the configure line. If you are using the OOB, make sure that you specify the thread-safe OOB client driver ('OOB_r') when creating data sources for use with OpenOffice.org. For earlier OOB releases, the thread-safe client is contained in distributions that contain '-mt' in their file name.
Before trying to use ODBC in OpenOffice, create an ODBC data source and test it with unixODBC's isql utility.
If you have the GUI unixODBC programs (you need QT for these), you can use ODBCConfig to create Easysoft ODBC driver data sources. Otherwise, you will have to edit the unixODBC odbc.ini file to add data sources. For information about adding an OOB data source, see DSN_definition.txt and example_odbc.ini in the install_dir/easysoft/oob/doc directory or the OOB documentation. For information about adding a data source for a different Easysoft ODBC driver, see the documentation for your Easysoft ODBC driver.
After you have created your data source and tested it with isql, you can use it from OpenOffice.org applications such as Base, Calc and Writer.
Connecting to an ODBC datasource from Apache OpenOffice 3, LibreOffice 3 and OpenOffice.org 2.0
- Choose File > New > Database.
- Click Connect to an existing database.
- Click ODBC in the list, and then click Next.
- Click Browse, double-click your data source, and then click Next.
If your data source is not listed, check the odbc.ini file.
Note When testing with Apache OpenOffice, browsing for data sources failed with the error 'Could not load the program library libodbc.so or it is corrupted. The ODBC data source selection is not available'. To work around this, we entered the name of our Easysoft ODBC data source in the space provided in the dialog box. You can enter the name of a SYSTEM or USER data source (defined for the user who is running Base). To find out the name of the data source look at the relevant .ini file. (Run
odbcinst -jin a terminal window to find out the .ini file location.) - If your database requires a database user name, type it in the User name box. If this user needs to supply a password click the Password required check box.
- Click Finish.
- Save the database when prompted.
The database opens in a new Base window. From here you can access your data.
- In the left pane of the database window, click the Tables icon to see a hierarchy of tables. Type the database password if prompted, and then click OK.
- To retrieve the data in a table, in the Tables pane, double-click a table.
- Click the Queries icon to create a query.
Use any of the methods listed in the Tasks pane to create a query.
Connecting to an ODBC datasource from OpenOffice.org 1.0
- Choose Tools > Data Sources.
A dialogue containing General, ODBC, Tables, Queries and Links tabs is displayed.
- Click New Data Source, and then in the Name box, type a name to use for this data source in OpenOffice.org.
- In the Database type list, click ODBC.
- The Data source URL box now contains 'sdbc:odbc:'. Click the ellipses (..) next to this box to display the ODBC data sources that unixODBC knows about. Double-click one.
If your data source is not in this list, check the odbc.ini file.
- Click the ODBC tab. If your database requires a database user name, type it in the User name box. If this user needs to supply a password, click the Password required check box.
- Click the Tables tab to see a hierarchy of tables. Type the database password if prompted, and then click OK.
- Click the Queries tab to create a query.
Save the data source when prompted.
At the time of testing (using OpenOffice.org 1.0.1), queries created in Design View or executed in any mode other than 'Run SQL command directly' will not work in databases that use multipart names (catalog.schema.table). See instance 10577 at http://www.openoffice.org/issues/show_bug.cgi?id=10577.
Until 10577 is fixed, you must switch Design View off and click Run SQL command directly to get any data back from databases that use multipart names. In this case, the data will be read-only.
RedHat 8.0 Specific Notes
RedHat 8.0 comes with unixODBC 2.2.2 which is installed with a --prefix of /usr. As a result, odbcinst is in /usr/bin/odbcinst and libodbc.so is in /usr/lib. OOB distributions before 1.1.0.4 do not expect to find unixODBC in these places and will ask if you have unixODBC installed and where it is.
The unixODBC that comes with RedHat appears to have been built with --sysconfdir=/etc, so the odbc.ini and odbcinst.ini files should be in /etc.
When you install OOB (pre 1.1.0.4) on RedHat 8, it may fail to locate the existing unixODBC, in which case you have two alternatives:
- Accept the fact the OOB installation does not find the existing unixODBC and answer Yes to install the unixODBC that comes with OOB. All the OOB installation needs is odbcinst and libodbcinst.so to install the OOB driver into unixODBC. Once OOB is installed under unixODBC you can delete /usr/local/easysoft/unixODBC.
- When asked if you already have unixODBC installed, say Yes and enter /usr as the path to unixODBC. This will use your existing odbcinst to install OOB (no net difference to 1). When asked if you want to install OOB as an ODBC driver under unixODBC, answer Yes.
Example of Using Calc with ODBC Data Sources
After you have registered your ODBC data source, the external ODBC data is then available to OpenOffice applications. This example shows how to connect Calc to ODBC databases such as Mssql, Oracle®, InterBase and Sybase. Acrobat pro dc size. Calc is the OpenOffice spreadsheet program.
- In Calc, choose View > Data Sources or press the F4 key.
The Data Sources pane opens at the top of the Calc window.
- In the left Data Sources pane, double-click your ODBC data source.
- Do one of the following:
- To make all the data in a table available to your Calc spreadsheet, double click Tables.
- To make the results of a query available to your Calc spreadsheet, double click Queries.
- Double-click the table or query that will retrieve the data you want to copy into Calc.
- To filter the data in Calc before copying it, do one of the following:
- Click and then define the filter criteria.
- Click in a field that contains the value you want to filter records with, and then choose Data > Filter > Auto Filter.
For example, to display the details of AdventureWorks customers who are from a particular territory, click in the TerritoryID and then click . Calc now only displays records from the territory that you chose.
To remove the Calc filter, click .
- To copy the data to Calc, drag the data from the right Data Sources pane to your spreadsheet:
- To import all records from the result set, drag the top left to an empty spreadsheet cell.
- To import a row, in the row that you want, drag the to an empty spreadsheet cell.
To select multiple adjacent rows, hold down the Shift key and click the rows that you want. To select multiple non-adjacent rows, hold down the CTRL key.
Auto clicker keybind. To keep the spreadsheet data current with the data in the underlying database table, Choose Data > Refresh Range
Creating DataPilot Tables from ODBC Data
A DataPilot is an interactive table that enables you display data in more than one way. You can manipulate the rows and columns in a DataPilot table to view or summarise the data in different ways for the purposes of analysis. DataPilots also allow you to apply spreadsheet functions to the data.
This example shows how to use an external ODBC data source as the source for a DataPilot table.
- Import the ODBC data that you want to analyse with the DataPilot table.
- In Calc, select the data you want to base the DataPilot table on.
Include the column headings.
- Choose Data > DataPilot > Start.
- In the Select Source dialog box, select Current Selection and click OK.
The DataPilot dialog box is displayed. The dialog box shows you a diagram of the DataPilot that you are creating. The table column headings are displayed as dialog box buttons. To create the DataPilot, you drag these buttons to the dialog box's layout areas: Column, Row and Data.
- Drag the required fields to one of the three areas.
If you drag a column button to the Column or Row area, it becomes the first cell in a row or column of the DataPilot. If you drag a column button to the Data area, it becomes the data in the DataPilot.
This example DataPilot shown in the screen shot uses the Northwind Orders table as its source data. In the example, ShipCountry is the column, EmployeeID the row, and OrderDate the column.
- Double-click the column button in the Data Fields area.
The Data Field dialog box is displayed.
- Use the dialog box to select the Calc function that you want to use to manipulate the data in the Data Area. The example DataPilot uses the Count function to count the number of orders each Northwind sales person took in each country.
- Click OK to exit the DataPilot configuration dialog boxes.
The DataPilot is displayed below the source data in the Calc spreadsheet. The example DataPilot makes it easy to see how many orders each employee took, broken down by country.
- To change how the DataPilot displays the information, you can drag the columns to new positions. For example, the following DataPilot was produced by dragging the ShipCountry column below the EmployeeID column.
If the source data changes, choose Data > DataPilot > Refresh to update the DataPilot. If you no longer need the DataPilot, choose Data > DataPilot > Delete.
Using a Base Query as DataPilot Source Data
Calc lets you use a database query created in Base as the source data for a DataPilot. You can use the query to selectively retrieve the data you want before importing it into Calc.
This section shows how to use a Base query and a Calc DataPilot to create a sales report from external ODBC data that shows how well sales representatives are doing and which products are selling the most.
To follow the examples in this section, create a SQL Server ODBC driver or OOB data source that connects to a SQL Server instance that serves the Northwind database. Alternatively, create an OOB data source that points to an Microsoft Access ODBC data source for the Northwind database.
Creating a Base Query from ODBC Data
To find out how much each sales representative has sold, create a query that retrieves the:
- Name of each sales representative.
- Name of each product.
- Amount sold.
- Dates of the orders.
- Create an OpenOffice database document for an ODBC data source that connects to the Northwind database.
- In the Database pane, click Queries.
- In the Tasks Pane, click Create Query in Design View.
The Query Design window prompts you to add a table or query.
- Click Employees and click Add to add the Employees table to the Query Design window.
- Repeat the previous step to add the Products, Orders, Order Subtotals and Order Details table.
- Click Close.
- In the lower pane of the Query Design window, choose:
- Employees.LastName in the first field.
- Products.ProductName in the second field.
- Orders.OrderDate in the third field.
- Order Subtotals.Subtotal in the fourth field.
- Choose Insert > New Relation.
The Join Properties dialog box is displayed.
- In the Tables involved area, choose Employees and Orders.
- In the Fields involved area, choose EmployeeID in both fields.
- Leave the join type set to the default value Inner join, and then click OK.
- Repeat the previous three steps to join the OrderID fields in the Orders and Order Subtotals tables.
- Choose File > Save and save the query as Sales Results.
- Choose File > Exit to close the Query Design window and return to the database document.
- In the Queries pane, double-click Sales Results to run the query.
Creating a DataPilot from a Base Query
Now that you have the raw data you need, you can use Calc to summarise the information. The following example shows how to compare the amount of products sold by each sales representative.
- In a Calc spreadsheet, choose Data > DataPilot > Start.
The Select Source dialog box is displayed.
- Choose Data source registered in OpenOffice.org and click OK.
The Select Data Source dialog box is displayed.
- Set the dialog box options to the following values, and then click OK.
Examples of Using Base Queries and Forms with ODBC Data Sources
To follow the examples in this section, create a SQL Server ODBC driver or OOB data source that connects to a SQL Server instance that serves the Northwind database. Alternatively, create an OOB data source that points to an Microsoft Access ODBC data source for the Northwind database.
Using Calculated Fields in a Base Query
Calculated fields derive their values from data that exists elsewhere in the database. The example query in this section uses a calculated field to work out the total value of each order in the Northwind Orders table. The calculated field does this by multiplying the Quantity column value by the UnitPrice column value. The calculation produces a value in the query result set that is derived from but not stored in the database table.
Before trying to use ODBC in OpenOffice, create an ODBC data source and test it with unixODBC's isql utility.
If you have the GUI unixODBC programs (you need QT for these), you can use ODBCConfig to create Easysoft ODBC driver data sources. Otherwise, you will have to edit the unixODBC odbc.ini file to add data sources. For information about adding an OOB data source, see DSN_definition.txt and example_odbc.ini in the install_dir/easysoft/oob/doc directory or the OOB documentation. For information about adding a data source for a different Easysoft ODBC driver, see the documentation for your Easysoft ODBC driver.
After you have created your data source and tested it with isql, you can use it from OpenOffice.org applications such as Base, Calc and Writer.
Connecting to an ODBC datasource from Apache OpenOffice 3, LibreOffice 3 and OpenOffice.org 2.0
- Choose File > New > Database.
- Click Connect to an existing database.
- Click ODBC in the list, and then click Next.
- Click Browse, double-click your data source, and then click Next.
If your data source is not listed, check the odbc.ini file.
Note When testing with Apache OpenOffice, browsing for data sources failed with the error 'Could not load the program library libodbc.so or it is corrupted. The ODBC data source selection is not available'. To work around this, we entered the name of our Easysoft ODBC data source in the space provided in the dialog box. You can enter the name of a SYSTEM or USER data source (defined for the user who is running Base). To find out the name of the data source look at the relevant .ini file. (Run
odbcinst -jin a terminal window to find out the .ini file location.) - If your database requires a database user name, type it in the User name box. If this user needs to supply a password click the Password required check box.
- Click Finish.
- Save the database when prompted.
The database opens in a new Base window. From here you can access your data.
- In the left pane of the database window, click the Tables icon to see a hierarchy of tables. Type the database password if prompted, and then click OK.
- To retrieve the data in a table, in the Tables pane, double-click a table.
- Click the Queries icon to create a query.
Use any of the methods listed in the Tasks pane to create a query.
Connecting to an ODBC datasource from OpenOffice.org 1.0
- Choose Tools > Data Sources.
A dialogue containing General, ODBC, Tables, Queries and Links tabs is displayed.
- Click New Data Source, and then in the Name box, type a name to use for this data source in OpenOffice.org.
- In the Database type list, click ODBC.
- The Data source URL box now contains 'sdbc:odbc:'. Click the ellipses (..) next to this box to display the ODBC data sources that unixODBC knows about. Double-click one.
If your data source is not in this list, check the odbc.ini file.
- Click the ODBC tab. If your database requires a database user name, type it in the User name box. If this user needs to supply a password, click the Password required check box.
- Click the Tables tab to see a hierarchy of tables. Type the database password if prompted, and then click OK.
- Click the Queries tab to create a query.
Save the data source when prompted.
At the time of testing (using OpenOffice.org 1.0.1), queries created in Design View or executed in any mode other than 'Run SQL command directly' will not work in databases that use multipart names (catalog.schema.table). See instance 10577 at http://www.openoffice.org/issues/show_bug.cgi?id=10577.
Until 10577 is fixed, you must switch Design View off and click Run SQL command directly to get any data back from databases that use multipart names. In this case, the data will be read-only.
RedHat 8.0 Specific Notes
RedHat 8.0 comes with unixODBC 2.2.2 which is installed with a --prefix of /usr. As a result, odbcinst is in /usr/bin/odbcinst and libodbc.so is in /usr/lib. OOB distributions before 1.1.0.4 do not expect to find unixODBC in these places and will ask if you have unixODBC installed and where it is.
The unixODBC that comes with RedHat appears to have been built with --sysconfdir=/etc, so the odbc.ini and odbcinst.ini files should be in /etc.
When you install OOB (pre 1.1.0.4) on RedHat 8, it may fail to locate the existing unixODBC, in which case you have two alternatives:
- Accept the fact the OOB installation does not find the existing unixODBC and answer Yes to install the unixODBC that comes with OOB. All the OOB installation needs is odbcinst and libodbcinst.so to install the OOB driver into unixODBC. Once OOB is installed under unixODBC you can delete /usr/local/easysoft/unixODBC.
- When asked if you already have unixODBC installed, say Yes and enter /usr as the path to unixODBC. This will use your existing odbcinst to install OOB (no net difference to 1). When asked if you want to install OOB as an ODBC driver under unixODBC, answer Yes.
Example of Using Calc with ODBC Data Sources
After you have registered your ODBC data source, the external ODBC data is then available to OpenOffice applications. This example shows how to connect Calc to ODBC databases such as Mssql, Oracle®, InterBase and Sybase. Acrobat pro dc size. Calc is the OpenOffice spreadsheet program.
- In Calc, choose View > Data Sources or press the F4 key.
The Data Sources pane opens at the top of the Calc window.
- In the left Data Sources pane, double-click your ODBC data source.
- Do one of the following:
- To make all the data in a table available to your Calc spreadsheet, double click Tables.
- To make the results of a query available to your Calc spreadsheet, double click Queries.
- Double-click the table or query that will retrieve the data you want to copy into Calc.
- To filter the data in Calc before copying it, do one of the following:
- Click and then define the filter criteria.
- Click in a field that contains the value you want to filter records with, and then choose Data > Filter > Auto Filter.
For example, to display the details of AdventureWorks customers who are from a particular territory, click in the TerritoryID and then click . Calc now only displays records from the territory that you chose.
To remove the Calc filter, click .
- To copy the data to Calc, drag the data from the right Data Sources pane to your spreadsheet:
- To import all records from the result set, drag the top left to an empty spreadsheet cell.
- To import a row, in the row that you want, drag the to an empty spreadsheet cell.
To select multiple adjacent rows, hold down the Shift key and click the rows that you want. To select multiple non-adjacent rows, hold down the CTRL key.
Auto clicker keybind. To keep the spreadsheet data current with the data in the underlying database table, Choose Data > Refresh Range
Creating DataPilot Tables from ODBC Data
A DataPilot is an interactive table that enables you display data in more than one way. You can manipulate the rows and columns in a DataPilot table to view or summarise the data in different ways for the purposes of analysis. DataPilots also allow you to apply spreadsheet functions to the data.
This example shows how to use an external ODBC data source as the source for a DataPilot table.
- Import the ODBC data that you want to analyse with the DataPilot table.
- In Calc, select the data you want to base the DataPilot table on.
Include the column headings.
- Choose Data > DataPilot > Start.
- In the Select Source dialog box, select Current Selection and click OK.
The DataPilot dialog box is displayed. The dialog box shows you a diagram of the DataPilot that you are creating. The table column headings are displayed as dialog box buttons. To create the DataPilot, you drag these buttons to the dialog box's layout areas: Column, Row and Data.
- Drag the required fields to one of the three areas.
If you drag a column button to the Column or Row area, it becomes the first cell in a row or column of the DataPilot. If you drag a column button to the Data area, it becomes the data in the DataPilot.
This example DataPilot shown in the screen shot uses the Northwind Orders table as its source data. In the example, ShipCountry is the column, EmployeeID the row, and OrderDate the column.
- Double-click the column button in the Data Fields area.
The Data Field dialog box is displayed.
- Use the dialog box to select the Calc function that you want to use to manipulate the data in the Data Area. The example DataPilot uses the Count function to count the number of orders each Northwind sales person took in each country.
- Click OK to exit the DataPilot configuration dialog boxes.
The DataPilot is displayed below the source data in the Calc spreadsheet. The example DataPilot makes it easy to see how many orders each employee took, broken down by country.
- To change how the DataPilot displays the information, you can drag the columns to new positions. For example, the following DataPilot was produced by dragging the ShipCountry column below the EmployeeID column.
If the source data changes, choose Data > DataPilot > Refresh to update the DataPilot. If you no longer need the DataPilot, choose Data > DataPilot > Delete.
Using a Base Query as DataPilot Source Data
Calc lets you use a database query created in Base as the source data for a DataPilot. You can use the query to selectively retrieve the data you want before importing it into Calc.
This section shows how to use a Base query and a Calc DataPilot to create a sales report from external ODBC data that shows how well sales representatives are doing and which products are selling the most.
To follow the examples in this section, create a SQL Server ODBC driver or OOB data source that connects to a SQL Server instance that serves the Northwind database. Alternatively, create an OOB data source that points to an Microsoft Access ODBC data source for the Northwind database.
Creating a Base Query from ODBC Data
To find out how much each sales representative has sold, create a query that retrieves the:
- Name of each sales representative.
- Name of each product.
- Amount sold.
- Dates of the orders.
- Create an OpenOffice database document for an ODBC data source that connects to the Northwind database.
- In the Database pane, click Queries.
- In the Tasks Pane, click Create Query in Design View.
The Query Design window prompts you to add a table or query.
- Click Employees and click Add to add the Employees table to the Query Design window.
- Repeat the previous step to add the Products, Orders, Order Subtotals and Order Details table.
- Click Close.
- In the lower pane of the Query Design window, choose:
- Employees.LastName in the first field.
- Products.ProductName in the second field.
- Orders.OrderDate in the third field.
- Order Subtotals.Subtotal in the fourth field.
- Choose Insert > New Relation.
The Join Properties dialog box is displayed.
- In the Tables involved area, choose Employees and Orders.
- In the Fields involved area, choose EmployeeID in both fields.
- Leave the join type set to the default value Inner join, and then click OK.
- Repeat the previous three steps to join the OrderID fields in the Orders and Order Subtotals tables.
- Choose File > Save and save the query as Sales Results.
- Choose File > Exit to close the Query Design window and return to the database document.
- In the Queries pane, double-click Sales Results to run the query.
Creating a DataPilot from a Base Query
Now that you have the raw data you need, you can use Calc to summarise the information. The following example shows how to compare the amount of products sold by each sales representative.
- In a Calc spreadsheet, choose Data > DataPilot > Start.
The Select Source dialog box is displayed.
- Choose Data source registered in OpenOffice.org and click OK.
The Select Data Source dialog box is displayed.
- Set the dialog box options to the following values, and then click OK.
Examples of Using Base Queries and Forms with ODBC Data Sources
To follow the examples in this section, create a SQL Server ODBC driver or OOB data source that connects to a SQL Server instance that serves the Northwind database. Alternatively, create an OOB data source that points to an Microsoft Access ODBC data source for the Northwind database.
Using Calculated Fields in a Base Query
Calculated fields derive their values from data that exists elsewhere in the database. The example query in this section uses a calculated field to work out the total value of each order in the Northwind Orders table. The calculated field does this by multiplying the Quantity column value by the UnitPrice column value. The calculation produces a value in the query result set that is derived from but not stored in the database table.
- Create an OpenOffice database document for an ODBC data source that connects to the Northwind database.
- In the Database pane, click Queries.
- In the Tasks pane, click Create Query in Design View.
- In the Tables list, double-click Employees, Orders and Order Details, and then click Close.
- In the upper pane of the Query Design window, double-click:
- LastName and FirstName in the Employees table.
- OrderDate in the Orders table.
- In the lower pane of the Query Design window, choose Order Details in the first available Table cell.
- In the first available Field cell, type this formula:
The formula calculates the total value of each order.
- In the Alias cell below this formula, type 'Total Order Value'.
The Alias cell lets you change the default column heading that displays when you retrieve the query data in Base and other OpenOffice applications. By default, OpenOffice displays the formula value ('Order Details'.'Quantity' * 'UnitPrice') as the heading for this column.
- Choose File > Save and save the query as Sales Performance.
Filtering Records with a Criterion Field
Criterion fields let you place restrictions on a query to help you find specific records that you want to work with. For example, instead of retrieving all orders received, you can create a criterion field that retrieves orders received in a particular month.
This section shows how to use a criterion field with an external ODBC data source to filter Northwind orders by month.
- Open the OpenOffice document where you saved the Sales Performance query.
- In the Database pane, click Queries.
- In the Queries Pane, click the Sales Results query.
- Choose Edit > Edit.
The query opens in Design View.
- In the lower pane of the Query Design window, in the OrderDate column, click in the Criterion field.
- Type the following filter to retrieve records for a particular month:
OpenOffice automatically delimits each date with hash (#) characters, which the Criterion field syntax requires.
- Choose Edit > Run Query to retrieve the query results or click .
The filtered query results display in the upper pane. You many need to change the OrderDate column format. To do this, right-click the OrderDate column heading and choose Column Format. In the format code box, type:
- Choose File > Save As and save the query as Monthly Sales Performance.
Changing the Query Sort Order
Base lets you sort result set data on a particular column and choose whether the query results display in ascending or descending order.
This example query shows the total value of the orders taken by each Northwind sales rep. To rank the employees, the query sorts the records by order amount and displays the records in descending order. The larger the order amount, the earlier the employee displays in the query results.
- Open the Monthly Sales Performance query in Design View.
- In the FirstName column, choose Group in the Function field.
- Repeat the previous step for the LastName column.
- In the OrderDate column, click to clear the Visible field.
- In the Total Order Value column, choose Sum in the Function field.
- Click to run the query.
The total order amount taken by each sales representative displays in the results pane.
You can rank the sales representatives by sorting the order totals in descending order.
- In the Total Order Value column, choose descending in the Sort field and re-run the query.
Creating a Data Entry Form for an ODBC Database
This section shows how to create a form that lets OpenOffice users create and display ODBC database records. The example form is based around the Northwind Products table.
Microsoft Access Alternative For Mac
- Open an OpenOffice database document for an ODBC data source that connects to the Northwind database.
- In the Database pane, click Forms.
- In the Tasks pane, click Use Wizard to Create Form.
The Form Wizard is displayed.
- In the Tables or queries list, choose the Northwind Products table.
- Select all columns apart from ProductID, and then click to add the remaining table fields to the form. Click Next.
- Click Next to move to the Arrange controls page.
- In the Arrangement of main form section, click Columnar - Label Left. Click Next to move to the Apply styles page.
- Choose the form layout format you want, and then click Next.
- In the Name of the form box, type 'Products'.
- Click Modify the form, and then click Finish.
The form opens in Form Design View.
The available values for the SupplierID and CategoryID fields are defined in separate tables. To enable users to choose from a predefined list of supplier IDs and category IDs when entering data, change the form controls for these fields to list boxes that retrieve the available IDs.
- Hold down the CTRL key, and click the SupplierID text box to select the control.
- Right-click the control, and then choose Replace With > List Box from the shortcut menu.
- Double-click the control to display the List Box properties dialog box. In the General tab, set the Drop down property to Yes.
- In the Data tab, set the Type of list contents property to 'SQL'. In the List content box, type:
- Repeat the previous four steps for the CategoryID text box. Change the SQL for the list box's source data to:
- To reduce the chance of data entry errors, make sure that the form field format is appropriate for the field's contents. For example, to prevent negative numbers from being entered in the UnitsInStock field, in the General Properties for this field, type '0' in the Value minimum box.
You can also ensure that a form field value falls within a specific range. For example, if the unit price for a product is never above 1000, you can prevent prices above this value from being entered. To do this in the example form, hold down the CTRL key, select, and then double-click the UnitPrice box. In the Value maximum box, type '1000'. Note that although this method prevents values that are outside the range from being entered, OpenOffice does not prompt for a valid value. Instead, OpenOffice sets the value to the maximum permitted by the range. In the example form, if a value of 1100 is entered in the UnitPrice field, OpenOffice sets the value to 1000, which is valid but not necessarily correct.
- You can configure the form to automatically enter field values when user's add new records.
For example, to specify that a new Northwind product will be automatically reordered when stock levels fall to 10, set the ReorderLevel field's default value to 10. To do this, hold down the CTRL key and select the ReorderLevel text box, and then double-click the control. In the Default value box, type 10. OpenOffice will automatically set this field's value to 10 when users add a new record.
- To exit Form Design View and use the form to display and add records, click
The form opens in a Writer document and displays the first record from the Northwind Products table.
Use the to move through the records. To add a new record click .
To return to Form Design View, click .
Example of Using Writer with ODBC Data Sources
Writer is the OpenOffice word processing application. Writer can use external ODBC data either to display in a document or as a mail merge source.
When testing Writer with external data, we found that the program hung when importing data by using drag and drop. However there is an alternative method:
- In Writer, choose View > Data Sources or press the F4 key.
- In the left Data Sources pane, double-click your ODBC data source.
- Do one of the following:
- To make all the data in a table available to your Writer document, double click Tables.
- To make the results of a query available to your Writer document, double click Queries.
- Double-click the table or query that will retrieve the data you want to copy into Writer.
- In the right Data Sources pane, select the data that you want to copy into Writer.
- Click the button.
The Insert Database Columns dialog box is displayed.
- The Insert Database Columns lets you choose whether the external data is inserted as a table, a mail merge field or text. Use the Table area to choose which columns are inserted.
If you have chosen to import the data as a Writer table, do not use this dialog box to apply a table format. When testing Writer, we found that applying a table format by using this method changed the values of all text columns to '0'. As a workaround, after you have inserted the external data:
- Insert another empty table into your Writer document.
- Apply the format you want to this table.
- Use the style paint brush to apply the empty table's format to the external data table.
- Click OK to exit the Insert Database Columns dialog box.
The external ODBC data is inserted into the Writer document.
This example shows Northwind data stored in a SQL Server database in a Writer table.
Appendix A: Resources
Alternatives To Microsoft Office Free
- Easysoft ODBC drivers:
- ODBC-JDBC Gateway — providing ODBC access to JDBC data sources.
- ODBC-ODBC Bridge — providing ODBC access to any remote ODBC data source. For example, connect OpenOffice on Linux to MS Access.
- Linux/UNIX ODBC — everything you need to know about ODBC on Linux and UNIX platforms.
- 64-bit ODBC — everything you need to know about ODBC on 64-bit Linux, UNIX and Windows platforms.
