Applications should not use temporary tables, unless there is no practical alternative. There is almost ALWAYS an alternative. How about using more complex queries or temporary ADO recordsets? For more information see the Thin Client Page

Table associations should be maintained with auto-number fields as ABSOLUTE numeric keys.  It is important to understand the difference between the LOGICAL key and the ABSOLUTE key.  Tables are the containers and the ABSOLUTE keys are the cog teeth that control their interaction with other tables, irrespective of data.  LOGICAL keys can be a compound of fields; these can be large, and vulnerable to foreign characters. The ABSOLUTE key should be the Primary Key and the LOGICAL key should have a unique index.

Naming

All field names within a schema should be unique. Each table could have a three-character mnemonic, which is used in each fieldname, separated by underscores. This ensures that no field-name is ambiguous in a query and allows easy source recognition. As a table will only have the attributes for one entity, there is no need to mention the entity in the field name.

EG : Table TBL_PRD_PRODUCT is a Product table

Fields that hold a single auto-number Primary Key should have a suffix of “_PK” 

Fields that hold Foreign Keys should have a suffix of “_FK” 

Mixed-case names should be avoided, in case the application is ported to a less tolerant Enterprise such as some versions of Oracle.

Keep field widths and types as small as possible. Be careful of large defaults that can apply, such as 255-character text fields and Double Precision floating point numbers. If there are no decimals to be stored, then use Bytes, Integers, Long Integers etc.

For each field enter the description as you build them. These will appear in the status bar text of form fields based on the table (or queries based on the table), and can be used by database documenters.

For each field in MS Access back-ends, enter the caption. Captions will be displayed in the header of queries, and will form the labels for forms and reports built after the caption has been added.

If practical, set a Jet database to "Compact on Close" if it contains data that is regularly changed.

MS Access Field tags :

Even though application objects such as forms may maintain the integrity of field data, the back-end data integrity should be secured where possible. The following table field properties must be maintained :

Where back-end security is implemented, table attachments should not be able to be examined by a User to determine the UID and Password.

Field data should not need to be entered by hand. The application should support all required data maintenance, including lookup tables.

Linked Tables

Linked tables should be avoided where :

If you do use linked tables, then attach them dynamically, and detach them before the application closes.

Public Function CheckBackendDatabase(pstr_Mode As String) As Boolean
On Error GoTo CheckBackendDatabase_Error

Dim wk As DAO.Workspace
Dim DB_App As DAO.Database, DB_Data As DAO.Database
Dim var_Rtn As Variant
Dim str_Filename As String, str_TableName As String
Dim int_i As Integer

CheckBackendDatabase = False
DoCmd.Hourglass True
Set DB_App = CurrentDb()

If pstr_Mode = "Change" Then GoTo CheckBackendDatabaseInPlace

'--- If counting records of an attached table causes an error then you need to reattach
On Error GoTo CheckBackendDatabaseInPlace
var_Rtn = DCount("*", "tlkp_Person", "True")
On Error GoTo CheckBackendDatabase_Error
CheckBackendDatabase = True

CheckBackendDatabaseExit:
CheckBackendDatabase = True
DoCmd.Hourglass False
Exit Function


CheckBackendDatabaseInPlace:
'--- If you have got here then the ODBC connection is not working
MsgBox "Sorry, but your computer cannot see the backend database. Please phone the Help Desk", vbCritical, "Fatal Error"
GoTo CheckBackendDatabaseExit

'--- If you have got here then you need to rettach the database
On Error GoTo CheckBackendDatabase_Error

<Call your Common Mode Dialogue box here>

'--- Check if file finder was successful
DoCmd.Hourglass False
If Len(<MyReturnedFilePathName>) > 0 Then
  DoCmd.Hourglass True
  str_Filename = <MyReturnedFilePathName>
  Set wk = DBEngine(0)
  Set DB_Data = wk.OpenDatabase(str_Filename)
  DB_App.TableDefs.Refresh
  DoCmd.SetWarnings False
  '--- Go through tables of target database, detaching then reattaching
  For int_i = 0 To DB_Data.TableDefs.Count - 1
    str_TableName = DB_Data.TableDefs(int_i).Name
    If Left(str_TableName, 4) <> "MSys" Then  '--- Ignore system tables
      var_Rtn = SysCmd(SYSCMD_SETSTATUS, "Attaching to " & str_TableName)
      DoEvents
      var_Rtn = slib_DetachTable(str_TableName) '--- Detach this App from table
      If Not slib_AttachTable("", str_Filename, str_TableName, str_TableName) Then
        DoCmd.SetWarnings True
        DoCmd.Hourglass False
        MsgBox "There was a problem attaching application to table '" & str_TableName & "' in database " & str_Filename, 16, "Fatal Error"
        GoTo CheckBackendDatabaseExit
      End If
    End If
  Next int_i

  DB_App.TableDefs.Refresh
  DoCmd.SetWarnings True
  CheckBackendDatabase = True
  var_Rtn = SysCmd(SYSCMD_CLEARSTATUS)
End If

GoTo CheckBackendDatabaseExit

CheckBackendDatabase_Error:
MsgBox Cstr(err) & " - " & error, vbCritical,"Application Error"

Resume CheckBackendDatabaseExit
End Function

Function slib_AttachTable(pstr_Type As String, pstr_DNS As Variant, pstr_Source As Variant, pstr_Alias As Variant) As Boolean
On Error GoTo slib_AttachTable_Error

slib_AttachTable = False

DoCmd.Hourglass True
Dim tbl_TempDef As TableDef
Dim var_Rtn As Variant
Dim DB_App As Database

Set DB_App = CurrentDb()
Set tbl_TempDef = DB_App.CreateTableDef(CStr(pstr_Alias))
tbl_TempDef.Connect = pstr_Type & ";DATABASE=" & CStr(pstr_DNS)
tbl_TempDef.SourceTableName = CStr(pstr_Source)
DB_App.TableDefs.Append tbl_TempDef ' Attach table.

slib_AttachTable = True

slib_AttachTable_Exit:
DoCmd.Hourglass False
Exit Function

slib_AttachTable_Error:
MsgBox Cstr(err) & " - " & error, vbCritical,"Application Error"Resume slib_AttachTable_Exit
End Function

Function slib_DetachTable(pstr_Alias As String) As Boolean
slib_DetachTable = False
On Error GoTo slib_DetachTable_Error

Dim DB_App As DAO.Database
Set DB_App = CurrentDb()

DB_App.TableDefs.Delete pstr_Alias
DoCmd.SetWarnings True
slib_DetachTable = True

slib_DetachTable_Exit:
Exit Function

slib_DetachTable_Error:
Resume slib_DetachTable_Exit
End Function

Normalisation

Tables should be normalised to at least third-normal form (3NF)

Care must be taken not to “over-normalise” the data structure to the detriment of performance.

Although Boyce/Codd Normal form, 4NF or 5NF  will rarely be a requirement, it is accepted that these may be in place through the implementation of 3NF.

Entity Relationship Diagram

A simple Entity Relationship diagram should be generated early in the project, and maintained through the project until it is included in the Technical Documentation.

The Customer Representative should be made aware of the implications of the structure by using a spoken fact-modelling dialogue.

Indexes

Indexing should be implemented sensitively for preferred performance.

All Primary Keys should have a unique index.

All Foreign Keys should be indexed

Fields that are commonly used in search criteria should be indexed.

Tables should not be “over-indexed” where data changes are frequent.

Care should be taken not to accidentally duplicate indexes.

Schema

In Jet databases, the integral Rushmore Technology should be taken advantage of.

Table Relationships should be set. It should not be possible to breach the laws of Referential Integrity.

Parent-Child relationships will usually be set to :

For simplicity, Relationships need not be set to lookup tables, where appropriate. However, the actual Relationships should reflect the detail of the Entity Relationship Diagram.