Access Database Design & Development for Systems You Already Run

Written by the Excel Access Expert team. Microsoft Access and Excel developers since 2011. Last updated October 7, 2026.

What is Access database design and development?

What a weak Access design costs every week

Why Access databases slow down and break: symptoms and causes

Redesign, optimization or repair: which do you need?

How we have remodeled live Access systems

Access database modernization: updating a legacy system

• Convert legacy .mdb files to .accdb

• Replace table-level lookup fields with proper relationships and combo boxes on forms

• Make VBA safe for 64-bit Office, including API declarations that need PtrSafe

• Fix missing or outdated references and replace deprecated code patterns

• Convert any Access Data Project (.adp), because Access 2013 and later do not support them

• Replace linked Excel and text files with real tables where the data belongs in the database

• Review multivalue and attachment fields, which complicate a later move to SQL Server

• Produce an ERD and a change log so the next person can extend the database safely

What is included in Access design and development

• Architecture review of your current .accdb or .mdb

• Table normalization and relationship redesign

• Index and query performance improvements

• Form and report redesign for clarity and speed

• Split database and multi-user hardening when needed

• Data cleanup rules and validation on entry

• Documentation: ERD notes and a change log for your team

• Optional VBA cleanup for broken or brittle code

• A staging plan so go-live does not surprise staff

• Guidance on when SQL Server upsizing is the next step

How we change a live database without breaking it

• We work on a copy. Your live database stays untouched until sign-off.

• Data moves into the new structure side by side with the old one.

• We reconcile totals against the old system before cutover.

• Your power users test real scenarios before go-live.

• Rollout is staged, with notes on what changed for each screen.

• You get the ERD, a change log and backup guidance at handover.

Try it: a quick structure audit in VBA

Option Explicit

' Quick structure audit for the current database.
' Run from the Immediate window (Ctrl+G):  AuditTables
' Reports local tables with no primary key, and
' ID-style fields that have no index.
Public Sub AuditTables()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field

    Set db = CurrentDb
    For Each tdf In db.TableDefs
        If IsUserTable(tdf) Then
            If Not HasPrimaryKey(tdf) Then
                Debug.Print tdf.Name & ": no primary key"
            End If
            For Each fld In tdf.Fields
                If IsIdName(fld.Name) Then
                    If Not IsIndexed(tdf, fld.Name) Then
                        Debug.Print tdf.Name & "." & fld.Name & ": ID field with no index"
                    End If
                End If
            Next fld
        End If
    Next tdf
    Debug.Print "Audit finished."
End Sub

Private Function IsUserTable(ByVal tdf As DAO.TableDef) As Boolean
    IsUserTable = (Left$(tdf.Name, 4) <> "MSys") _
              And (Left$(tdf.Name, 4) <> "USys") _
              And (Left$(tdf.Name, 1) <> "~") _
              And (Len(tdf.Connect) = 0)
End Function

Private Function IsIdName(ByVal fieldName As String) As Boolean
    IsIdName = (UCase$(fieldName) = "ID") _
            Or (Right$(fieldName, 2) = "ID") _
            Or (LCase$(Right$(fieldName, 3)) = "_id")
End Function

Private Function HasPrimaryKey(ByVal tdf As DAO.TableDef) As Boolean
    Dim idx As DAO.Index
    For Each idx In tdf.Indexes
        If idx.Primary Then
            HasPrimaryKey = True
            Exit Function
        End If
    Next idx
End Function

Private Function IsIndexed(ByVal tdf As DAO.TableDef, ByVal fieldName As String) As Boolean
    Dim idx As DAO.Index
    For Each idx In tdf.Indexes
        If idx.Fields.Count > 0 Then
            If idx.Fields(0).Name = fieldName Then
                IsIndexed = True
                Exit Function
            End If
        End If
    Next idx
End Function

How much does Access database redesign cost?

• Number of tables, forms and reports

• How many structural problems the review finds

• Amount and condition of the VBA code

• Volume of data to migrate into the new structure

• How the rollout needs to be staged for your team

Who this service is for, and who it is not for