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. Examples:

Oracle
MasterData
CustomerCare

Location

There are connection details which depend on the location where a frontend is opened. By specifying connection details for a specific location it is possible to adjust connections accordingly. The location is derived from the workstation name, but other conditions could be coded into the backend easily. Examples:

Office
Mobile
Home

Environment

Frontends are usually developed, tested and then put into production. The purpose of an environment is to provide different connection details depending on the purpose. Examples:

Development
Testing
Production

ConnectionType

Databases can be accessed through different types of connections depending on the context. Examples:

Jet
ODBC
OLEDB

Connection Code

The combination of Server, Location, Environment and Connection Type specifies a unique Connection. The four codes separated by spaces define the Connection Code. Examples:

Oracle Work Production ODBC
MasterData Home Development Jet

This implies that spaces must not be used in any of the codes for Server, Location, Environment or Connection Type.

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