What is Microsoft Access

Microsoft access is a simple database that is available on the windows platform. This is part of their Microsoft Office Suite. Microsoft access files have an extension of ".mdb" and simply packaged as a single file.

Create an ODBC data source using Microsoft control panel

Control Panel --> Administrative Tools --> Data Sources (ODBC) --> System DSN --> Add

Choose Microsoft Access Driver (*.mdb), click on Finish, In Data source name put a name of your choice, click on Select ..., navigate to your mdb file and choose it, Click OK, Click OK, Click OK

To use a microsoft access database using java, you need to use the jdbc-odbc bridge. I don't know of any jdbc drivers that can be used. Atleast the free ones. So the first step is to create an ODBC data source for your mdb file. You may want to keep your mdb files in the "database" sub directory of aspire distribution. This way you can distribute it with the application.

Once you have the mdb file, use control panel to create a data source for this mdb file. Let us call this data source name "mdb-source".

Aspire Property file entries

Create the following entries in the master aspire.properties file.

Database.name = MyAccessDatabase
Database.MyAccessDatabase.jdbc_driver=sun.jdbc.odbc.JdbcOdbcDriver
Database.MyAccessDatabase.connection_string = jdbc:odbc:mdb-source
Database.MyAccessDatabase.userid = access_user
Database.MyAccessDatabase.password = access_password

Create an alias for the above database

Type in the following lines subsequently to create an alias for that database.

Database.alias.MyAccessDataBaseAlias=MyAccessDatabase

How to use the database in your queries

Here is an example

#
# Create report
#
request.createReport.classname=com.ai.db.DBRequestExecutor2
request.createReport.db=MyAccessDataBaseAlias
request.createReport.query_type=update
request.createReport.stmt=\
\
insert into reports (report_id \
	,report_short_name \
	,report_long_name \
	,report_description \
	,url \
	,owner_user_id \
	,created_by \
	,created_on \
	,last_updated_by \
	,last_updated_on) \
values({report_sequence_value} \
	,{report_short_name.quote} \
	,{report_long_name.quote} \
	,{report_description.quote} \
	,{url.quote} \
	,{profile_user.quote} \
	,{profile_user.quote} \
	,Now() \
	,{profile_user.quote} \
	,Now() \
)

Here is another Aspire page retrieval example

###################################
# reportsURL
###################################
reportsURL=aspire:\\reports\\reports-summary.html
reportsURL.formHandlerName=reportsURLFormHandler
request.reportsURLFormHandler.form_handler.class_request.className=com.ai.htmlgen.DBHashTableFormHandler
reportsURLFormHandler.loopNames=works

#
# Reading data from a file to satisfy a loopname called: "works"
# The loop name "works" is a user defined loop name in your html tags file
# Look at the commentary below to see how the same data could be read from a database
#
request.reportsURLFormHandler.works.class_request.className=com.ai.htmlgen.GenericTableHandler5
request.reportsURLFormHandler.works.query_request.className=com.ai.db.DBRequestExecutor2
request.reportsURLFormHandler.works.query_request.db=MyAccessDataBaseAlias
request.reportsURLFormHandler.works.query_request.stmt=\
	select report_short_name,url from reports