Microsoft Access Backend

General

It is good practice to separate a frontend from a backend, ie to put queries, forms, reports and the business logic into a frontend database and all tables into a backend database no matter what kind of database it is.

Architecture

Server

Every backend is a server no matter whether it is a Microsoft Jet or another type of database. Each server has a unique name.

Location

Development does not happen on the same machine all the time. The location is derived from the workstation name.

Environment

There are usually three environments, namely

• Development
• Testing / Pre-Production
• Production

ConnectionType

Databases can be accessed through different types of connections like

• Jet
• ODBC
• OLEDB

Model

Backend

Properties

The backend class has collections for each architectural aspect:

<uml class style="plain" scale="75" direction="lr"> [Backend]1-*>[ConnectionTypes] [Backend]1-*>[Environments] [Backend]1-*>[Locations] [Backend]1-*>[Servers] </uml>

It also knows all known connections as well as its tables and queries:

<uml class style="plain" scale="75" direction="lr"> [Backend]1-*>[Queries] [Backend]1-*>[Tables] [Backend]1-*>[Connections] </uml>

Methods

update

The backend object does not automatically reflect changes of the backend database.

The update method updates all objects and checks whether the existing linked objects are identifyable.

updateBackend
'or
getBackend.update

relink

Relinking all backend database objects to reflect the current location and environment can become a tedious task, if many objects are linked.

The relink method checks for each linked object which connection is currently present, tries to adjust the connection towards the current location and environment and reconnects the object accordingly.

relinkBackend
'or
getBackend.relink

amendConnections

Backend objects can be accessed through three different connection types. If a connection is provided manually or has been automatically retrieved from a linked object, naturally the other two connection types are missing.

The amendConnections method scans all connections, looks for missing connections regarding the connection type and creates the missing connections.

amendBackend
'or
getBackend.amendConnections

linkTable

The developer should not have to worry about connection information like connection strings, user credentials and other details.

The linkTable method links a backend object into the current frontend.

linkBackendTable strTable:="USER", lngServerId:=BackendServer.Oracle
'or
getBackend.linkTable strTable:="USER", lngServerId:=BackendServer.Oracle

The ServerId is realized as Enumeration in order to make backend servers easier accessible.

refreshOdbcTables

Tables which are linked via ODBC unfortunately do not automatically reflect the changes on the source object.

The refreshOdbcTables method refreshes the ODBC connection and makes the changes visible.

refreshOdbcTables
'or
getBackend.refreshOdbcTables