Appendix: ODBC Database Servers

Microsoft Access, and thus Open Accounting, support the use of a relational database server via the Open Data Base Connection (ODBC) protocol. Many popular databases, including MySql, Oracle®, and Sybase®, include ODBC drivers for use in multiple operating system environments.

One benefit of using a database server is speed. In a database with about one thousand Transactions, reports are five to ten times faster running on MySql via ODBC then in Jet on a laptop hard drive.

Although setting up ODBC is outside the scope of Open Accounting, we do provide two SQL files for assisting with this practice. One is called OpenAccounting.ddl.sql. This script creates a database called OpenAccounting and then defines the required tables.

The other script is called OpenAccounting.dml.sql. This script populates these tables with the minimum amount of data necessary to run the system.

It generally is no problem to start an Open Accounting operation using ODBC. But if one starts out using the Jet database engine in Access, it is somewhat tricky to then later upgrade to ODBC.

All is not lost, however. Access includes a data export feature. And a local table may have the same name as an ODBC table. So to transfer data, perform the following steps:

Create Tables on Server: Use the OpenAccounting.ddl.sql script to create the database and tables on the server.
Install ODBC Drivers: Install the required driver and configure a machine data source that connects to this new database.
Export Data: Link the ODBC tables to the openAccountingTables##-##.mdb file. Then use the Export command on the File menu to copy data to the ODBC tables.
Link Application: Delete the existing table links in the openAccounting##-##.mdb file and then use Link Tables under Get External Data in the File menu to connect with the ODBC machine source.

Oh, and as a reminder: all of the tables hosted on the database server have single word names: Accounts, Contacts, Transactions, and so on. Local tables have multiple word names: CurrentSession is an example. Do not delete local tables when performing this porting operation.