How and Why to Split Your Microsoft Access Database - Linked Table, Front End, Back End, Benefits

In this video, we'll talk about what splitting your database means, the benefits and drawbacks, and I'll show you how to do it.

Junior from Baton Rouge LA (a Gold Member) asks: I've heard a lot about the benefits of splitting my Access database. Can you tell me what this is all about?

LINKS:
Access Updater: https://599cd.com/XPBW6
Split Database Seminar: https://599cd.com/XU6JU
Security Seminar: https://599cd.com/XMLTB
Split & ACCDE Files: https://599cd.com/XJ9V4
Search Form:    • Microsoft Access Search Form - MS Acc...  
Compact & Repair: https://599cd.com/1331

BONUS FOR CHANNEL MEMBERS:
Silver Members and up get access to an EXTENDED CUT of this video which covers additional examples. That video will show you how to move or rename your backend file, use the linked table manager, create multiple back end files, and manually create links to tables.

MEMBERS ONLY VIDEO:
   • How and Why to Split Your Microsoft A...  

BECOME A MEMBER:
   / @599cd  

ADDITIONAL RESOURCES:
FREE Access Level 1: https://599cd.com/Free1
FREE Customer Template Database: https://599cd.com/XCDT
$1 Access Level 2: https://599cd.com/1Dollar
Your Questions Answered: https://599cd.com/TechHelp
Learning Connection 50% OFF: https://599cd.com/Connection
Survey: https://599cd.com/survey
Richard's Blog: https://599cd.com/blog
Access Forum: https://599cd.com/AccessForum
Twitter:   / learningaccess  
https://en.wikipedia.org/wiki/Microso...
https://products.office.com/en-us/access
https://microsoft.com/en-us/microsoft...
Email Me: [email protected]

OTHER ARTICLES:
https://www.techrepublic.com/blog/10-...
https://support.microsoft.com/en-us/o...
https://www.yourofficeanywhere.co.uk/...
http://www.databasedev.co.uk/split_a_...

KEYWORDS
microsoft access benefits of splitting database
Splitting your Access database
reasons to split an Access database
What does splitting an Access database do?
Can an Access database be used by multiple users?
How and why you should split an Access database
Benefits of Splitting Your Access Database
create a front end for an access database

OVERVIEW:
Splitting your database is dividing it into two or more files. One file, called the Front End, contains your forms, reports, queries, macros, and modules. The other, called the Back End, contains just your tables. Splitting your database makes it:

More Efficient, Faster
Easier to Maintain, Update
More Secure
Reduces Corruption
Easier to Scale Up

BENEFITS OF A SPLIT DATABASE
Faster & More Efficient
Only data transferred over the network
Users have local copy of forms, reports, etc.
Makes the database operate faster
Data is “more available”
Temporary data can be processed locally
Easier to Update & Maintain
Design changes to Front End without interrupting users
Do not have to import data after changes
Multiple developers can work on their Front Ends
More Secure
Separate Back End files for secure data
Sensitive info can be stored locally
Users can’t get at database design or code (with encryption)
Other Benefits
Go beyond the 2 GB Access file size limitation
Reduces corruption – usually kept to Front End file
Easier to scale up to SQL Server or Cloud Platform

DRAWBACKS OF SPLITTING
Distributing Front End updates can be challenging
Lose referential integrity on linked tables

STEPS TO SPLITTING YOUR DATABASE
1. Backup your database!
2. Create a shared database folder (//Server/Sharename or Z:\Database)
3. Split the Database
4. Database Tools - Move Data - Access Database
5. Compact Both
6. Encrypt (make ACCDE file) for the Front End
7. Distribue your Front End (ZIP and email, shared drive, AccessUpdater)

TIPS FOR SPLIT DATABASES
Try not to open ALL records (use a search form)
Create data entry forms
Avoid aggregate queries and functions like DLOOKUP
Use TOP X queries to limit records returned

QUESTIONS:
Please feel free to post your questions or comments below. Thanks.