Microsoft Access Development


Any application that is expecting to service more than 10 users at a time (including web users) should be built using the client-server architecture. Smaller applications can be run using an access desktop database.

When constructing a desktop database, it is best to keep the database tables in their own file. All other queries, forms etc, should be kept in a separate distributed file. The distributed file should have its tables linked to the table file which truly serves as the database. This makes compacting and backing up the main database file much easier. When possible, the database file should be stored on a central server with more power to push out the large transactional chunks of data. Linking in the tables does not use absolute paths so the distributed file will need to be re-linked to the desktop database file once the databases are deployed.

If the file is meant to be serve as the backend to a web page, a few other things must be taken into account. The actual database files with tables need to reside on the web server that is running the web service. The distributed files then need to map to the file on the database server.

The internet user account also needs to be given local permission to the desktop database. This is a huge security risk and should be considered carefully. Permission also needs to be given to the Internet User to access some ODBC registry keys. See the Administration section on changing this registry.

Development on any SQL or Oracle type database are done through the standard SSH or enterprise manager mechanisms. The Access database is then connected to the database through a system DSN. This DSN needs to be set up on any machine that will be accessing the database including web server.