Splitting a Database: How and Why

1
Splitting a Database How and Why

• Roger Smeaton

2
CONTENTS

• Splitting a database - what is it?
• Why do it?
• How is it done?

3
One-file architecture

• Access database
• Tables
• Relationships
• Forms
• Queries
• Reports
• Macros
• Modules (VBA code)

Only one database file, has it all!
4
Two-file architecture

• Front-end (application) the user interface
• Back-end the data

5
FormsQueriesReportsMacrosModules (VBA code)
Tables Relationships
Front-end
Back-end
6
Front-end
Back-end

• Each user gets their own copy

• One shared copy, on the server

7
Why split a database ?

• Separates data (back-end) from application
  (front-end).
• Developer can make changes to the
• application without disturbing the
  data or
• the users.

8
Why split a database ?

• 2. Able to create temporary tables in the
  front-end. Stores intermediate results, run-time
  parameters, etc.

9
Why split a database ?

• 3. Performance. Each user has a copy of the user
  interface (in the front-end), so network traffic
  is reduced.

10
Do I have to split?

• Not necessary to split if
• front-end is stable (no changes) and
• single user and
• network overhead is not a problem

11
When to split

• After you have designed your tables,
  relationships, forms, etc. but before you release
  the software to the users.

12
How to split a database

• OPTION 1 use the Database Splitter wizard
• Open the file. From the Tools menu, Database
  Utilities ? Database Splitter and follow the
  directions.
• OPTION 2 do it manually details omitted

13
After you split

• Compact the front-end file
• How? From the Tools menu, select Database
  Utilities ? Compact and Repair Database

14
DEMO

• Access database Northwind.mdb

15
Ready to deploy the database?

• Copy the back-end to a shared network device.
• Re-link the tables in the front-end. (Why? To
  point to the actual data tables in their new
  location)
• Copy the front-end to each users PC.
• Not mandatory but strongly recommended

16
Re-linking

• Once you have linked to the tables in the
  back-end, you can use the Linked Table Manager to
  re-link.
• How? From the Tools menu, select Database
  Utilities ? Linked Table Manager