Difference between revisions of "Microsoft Access Backend"

From database24
Jump to navigation Jump to search
(Created page with "== General == It is good practice to separate put queries, forms, reports and the business logic into a frontend database and all tables into a backend database if not a dedicate...")
 
 
(6 intermediate revisions by the same user not shown)
Line 1: Line 1:
 +
[[Category:Microsoft Access]]
 
== General ==
 
== General ==
−
It is good practice to separate put queries, forms, reports and the business logic into a frontend database and
+
It is good practice to separate a frontend from a backend,
−
all tables into a backend database if not a dedicated database server.
+
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 ==  
 
== Architecture ==  
Line 7: Line 9:
 
=== Server ===
 
=== Server ===
 
Every backend is a server no matter whether it is a Microsoft Jet or another type of database.
 
Every backend is a server no matter whether it is a Microsoft Jet or another type of database.
−
Each server has a unique name.
+
Each server has a unique name. Examples:
 +
 
 +
* ''Oracle''
 +
* ''MasterData''
 +
* ''CustomerCare''
  
 
=== Location ===
 
=== Location ===
−
Development does not happen on the same machine all the time.  
+
There are connection details which depend on the location where a frontend is opened.
−
The location is derived from the workstation name.
+
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 ===
 
=== Environment ===
−
There are usually three environments, namely
+
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''
  
−
* Development
 
−
* Testing / Pre-Production
 
−
* Production
 
  
 
=== ConnectionType ===
 
=== ConnectionType ===
−
Databases can be accessed through different types of connections like
+
Databases can be accessed through different types of connections depending on the context.
 +
Examples:
  
−
* Jet
+
* ''Jet''
−
* ODBC
+
* ''ODBC''
−
* OLEDB
+
* ''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 ==
 
== Model ==
 +
 +
=== Backend ===
 +
 +
==== Properties ====
 
The backend class has collections for each architectural aspect:
 
The backend class has collections for each architectural aspect:
−
<uml class style="scruffy" scale="75" direction="lr">
+
 
 +
<uml class style="plain" scale="75" direction="lr">
 
[Backend]1-*>[ConnectionTypes]
 
[Backend]1-*>[ConnectionTypes]
 
[Backend]1-*>[Environments]
 
[Backend]1-*>[Environments]
Line 35: Line 68:
 
[Backend]1-*>[Servers]
 
[Backend]1-*>[Servers]
 
</uml>
 
</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.
 +
 +
<syntaxhighlight lang="vb">
 +
updateBackend
 +
'or
 +
getBackend.update
 +
</syntaxhighlight>
 +
 +
 +
===== 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.
 +
 +
<syntaxhighlight lang="vb">
 +
relinkBackend
 +
'or
 +
getBackend.relink
 +
</syntaxhighlight>
 +
 +
 +
===== 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.
 +
 +
<syntaxhighlight lang="vb">
 +
amendBackend
 +
'or
 +
getBackend.amendConnections
 +
</syntaxhighlight>
 +
 +
 +
===== 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.
 +
 +
<syntaxhighlight lang="vb">
 +
linkBackendTable strTable:="USER", lngServerId:=BackendServer.Oracle
 +
'or
 +
getBackend.linkTable strTable:="USER", lngServerId:=BackendServer.Oracle
 +
</syntaxhighlight>
 +
 +
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.
 +
 +
<syntaxhighlight lang="vb">
 +
refreshOdbcTables
 +
'or
 +
getBackend.refreshOdbcTables
 +
</syntaxhighlight>

Latest revision as of 02:59, 5 August 2012

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
Retrieved from "http://wiki.database24.net/index.php?title=Microsoft_Access_Backend&oldid=395"