Connecting Nebulae to a Microsoft Access Database via ODBC

This technote assumes the Nebulae server is running on a Windows machine, which will also host the Access database. We recommend using Windows 2000 or XP for better performance on production servers.

The first task is to create the ODBC datasource that will point to the Access .mdb file. You can use an existing database or create a new .mdb, even if Access is not installed. Follow these steps to create the ODBC source:

A) Open the CONTROL PANELS -> ADMINISTRATION TOOLS-> DATA SOURCES (ODBC) applet.
B) Select the SYSTEM DNS tab to create a data source that is valid for all users on the current machine.
C) Click ADD.
D) Select MICROSOFT ACCESS DRIVER (*.mdb) from the dropdown list, and click FINISH.
E) Enter a name for the datasource in the field provided (for example NebulaeSQL). You can click SELECT to choose an existing *.mdb file, or click CREATE to let the system create an empty database file for you.
F) Select the database file and hit OK.
G) The ODBC data source will be displayed in the Data sources list, and is ready to be used.

Now we need to configure Nebulae to use this ODBC data source as an SQL gateway. In the Nebulae.cfg file you need to find and edit the SQL related directives to the following values:

EnableSQLDatabase = 1

SQLBackend = ODBCaccess

SQLDatabaseDriver = sun.jdbc.odbc.JdbcOdbcDriver

SQLDatabaseURL = jdbc:odbc:NebulaeSQL

SQLDatabaseUsername =

SQLDatabasePassword =

Notice that the SQLDatabaseURL directive contains the name of the ODBC datasource, and must match the name you chose on step e above. The sun.jdbc.odbc.JdbcOdbcDriver is Sun's generic ODBC bridge, and is included by default in all Java Virtual machines, so no additional drivers are required.

The same technique can be used to connect Nebulae to virtually any database engine that provides an ODBC driver (FoxPro, DBF, MS SQL Server, etc.), just by changing step d when you are setting up the system data source name.

If you are using a database engine other than Microsoft Access please remember to set:

SQLBackend = default

The ODBCAccess value configures Nebulae to handle Access non-standard SQL implementation, and is not necessary for other database engines like Microsoft's SQL server.