Access 2007-2016
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 
NEW Release: Access Developer Level 3   dismiss
 
 

< Previous: Access Expert 20

Next: Access Expert 22 >

Access Expert Level 21

Expert Microsoft Access Tutorial - 1 Hour, 54 Minutes
 
 
Access Expert 21 covers importing from and linking to other Access databases, setting up your database for a multi-user environment, splitting your database into front-end and back-end files, and record locking. Lots of good stuff in this course if you have any intention on sharing your database on a network. Topics include:
 
  - Import Access Database Objects
  - Linking to Other Access Databases
  - Splitting Your Database
  - Custom Front-End and Back-End DBs
  - Database Splitter Wizard
  - Shared Folder Permissions
  - Creating an Encrypted Front-End File
  - Preventing Design Changes
  -
Record Locking in a Shared Environment

Order Now

If you would like a preview of what's covered in this class,
click here to watch the first and last lessons of this course (free of charge), or scroll down for more information.




 

Access Expert Level 21
Description: Access Expert Level 21
Versions: Recorded with Access 2013. Also use with 2007 and 2010. Access 2003 users should get Access 308 for the lessons on importing, and Access 313 for the lessons on splitting your database.
Pre-Requisites: Access Expert Level 20 strongly recommended. This is NOT a class on Windows networking, so we will assume you have a Windows network already set up, and you know how to create user accounts and a shared folder at the Windows level. We will briefly go over creating shared folders, but we're not spending a lot of time on it.
Running Time: 1 Hour, 54 Minutes
Cost: $26.99


Today we will begin by learning how to import database objects from other Access databases. We'll import tables, queries, forms, reports, table relationships, menus, toolbars, import/export specs, and more. Then we'll learn how to Link to other Access databases to share data in real time. We'll learn how to create custom front-end forms for specific purposes.

import link access objects

 

Next we'll study multi-user database theory. It's always good to learn about how things work before starting to get your hands dirty, so we'll learn about sharing your database, why you want to split your database into front-end and back-end files, and the pros and cons of a multi-user database.

multi user database theory

 

Next we'll learn how to actually split our database. We'll learn about opening a database for exclusive use, the Database Splitter Wizard, the Linked Table Manager, renaming or moving a back-end file, setting up a shared folder, folder permissions, selecting users or groups, UNC path names, mapping a drive letter, and more.

splitting your database

 

Now that our database is split, we'll see how to move sensitive tables (such as credit history) to a different back-end file. This file can now be protected in a different network share for extra security. We will also create a separate custom front-end database file for different purposes. For example, your secretary might only need to see customer information. Your stock people might only need order information. Accounting needs order history and sales data. By splitting your database up you can create custom database files to fill specific roles, and not give people information they don't need access to. We'll learn how to create an encrypted front-end database file (ACCDE) so your end users can't make design changes.

 splitting database security

 

Finally, sharing a database on a network, in a multi-user environement can sometimes cause problems. We'll learn how to properly set up record-level locking so that two users can't edit a record at the same time. We'll learn about the Access record-lock file (LACCDB) and what it does. We'll see how to unlock a record that might be "stuck" if a user didn't shut down his database properly.

record locking

 

This is the 21st class in the Access Expert series. If you want to learn how to set up your database to be shared on a network by multiple users, this class is an absolute MUST. Of course, if you have any questions about whether or not this class is for you, please contact me.
 

 

Complete Outline - Access Expert Level 21

00. Intro (7:14)

01. Import Link Access Objects (14:45)
Import by Click-and-Drag
Import by External Data > Access
Browse to Database Folder
Import Objects Dialog
Import Options
Import Table Relationships
Import Menus and Toolbars
Import/Export Specs
Import Nav Pane Groups
Import All Images and Themes
Table Definition AND Data
Table Definition Only
Import Queries as Tables
Importing vs. Linking
Link to Customer Table
Custom Forms for Front End

02. Multi-User Database Theory (14:53)
Sharing a Database
Why Split Your Database
Front End vs. Back End Databases
Pros and Cons of Split Databases

03. Splitting Your Database 1 (20:38)
Compact & Repair First
Backup Your Data
Database Tools > Move Data > Access
Database Splitter Wizard
The database engine could not lock table
Already in use by another person
Notice Linked Tables
Set up Shared Folder
Linked Table Manager
Rename or Move Backend Database
Refresh Location of Backend Links
Setting up a Shared Folder
Folder Properties > Sharing
Assign Shared Folder Name
Setup Folder Permissions
Select Users or Group
UNC Path
Mapping a Drive Letter
04. Splitting Your Database 2 (21:15)
Storing Images on Server Share
Creating a 2nd Back End Database
Move a Secure Table to Different File
Creating a 2nd Front End Database
Custom Databases based on User Role
Distribute Encrypted Front End
Users Cannot Make Design Changes
File > Save As > Make ACCDE
Trusted Location - Shared Folder

05. Record Locking (28:42)
How Default Record Locking Works
Write Conflict Error Message
Save Record, Drop Changes
Copy to Clipboard
Form Properties > Record Locks
No Locks, All Records, Edited Record
What is an LACCDB file?
Database Client Settings
Page Level vs. Record Level Locking
Default Open Mode: Shared / Exclusive
File Open for Exclusive One Time Use
Default Record Locking
Open databases by using record-level locking
Changes take effect when first user opens db
Delete LACCDB file if record still locked

06. Review (6:15)

 


 
Keywords: Multi-User, Split Database, Record Locking, microsoft access tutorial, microsoft access tutorial, microsoft access training, access 2007, access 2010, access 2013, import access objects, external data, table definition, link to tables, sharing a database, front end, back end, move data, database splitter wizard, folder permissions, accde file, encrypted, record locks, laccdb file
 
 

Student Interaction: Microsoft Access Expert 21

Richard on 5/18/2014:  Microsoft Access Expert Level 21 is 1 hour, 54 minutes long. In this class focuses on importing from and linking to other Access databases, setting up your database for a multi-user environment, splitting your database into front-end and back-end files, and record locking. Lots of good stuff in this course if you have any intention on sharing your database on a network. Topics include: - Import Access Database Objects - Linking to Other Access Databases - Splitting Your Database - Custom Front-End and Back-End DBs - Database Splitter Wizard - Shared Folder Permissions - Creating an Encrypted Front-End File - Preventing Design Changes - Record Locking in a Shared Environment Click here for more information on Access Expert Level 21, including a course outline, sample videos, and more. This course was recorded using Access 2013, but is also valid for Access 2007 and 2010 users. This class follows Expert Level 20. The next class in the series is Expert Level 22.
Deon R on 5/19/2014: Hi Richard,

As usual the course was brilliant. Thank you for the excellent teaching and giving us your experience.

Looking forward to the Developer Series.

Kindest regards,

Deon

Richard Rost on 5/19/2014: So far, one student has asked if I will show how to distribute new front-end database updates using VBA code. If enough people are interested, I can make this into a mini-seminar. Post here or email me if you're interested.
Alex Hedley on 5/19/2014: I'd agree, this would make a cool mini seminar.
Deon R on 5/19/2014: Hi Richard,

Please count me in on this mini seminar.

Thanks for this and best regards,

Deon

James Gray on 5/21/2014: Rick,

I would like to see this as well. Thanks

Alice Morgan on 5/27/2014: Good Afternoon, Richard. I am also interested in Database updates using VBA. Hope it gets enough interest! :)

Regards,
Alice

Bonnie on 5/27/2014: Richard, I would be interested in such a mini-seminar, also comments and tips on further development once you have split your database.

Great classes!
Bonnie

Salvatori C on 7/5/2014: Hello Richard.
Is the speed of the network the basic restriction of why not have both front end and back end files on the server. Having the front end and or the encryption file on the server would be easier to manage with only a short cut to the files on the desktop.

Reply from Richard Rost:

Yes, it would make management easier, but you're also transmitting a LOT of extra information over the wire. Having your front-end local saves a lot of load time and overhead unless you have a REALLY fast network and/or a REALLY small database file.

simon baynton on 8/29/2014: Richard
Here is another vote for mini seminar on distribute new front-end database updates using VBA code.

Simon






Lynda on 10/30/2014: Got my vote also Richard
Brian Farley on 11/7/2014: So heres an odd thing. I split my database and it will only save it in a "Microsoft Access Signed Package" Still has a .accdb but a different icon. Also, I can NOT open the backend directly. Throws an error stating that there is no digital signature and to have the issuer send me another copy. Well I'm the sender so that's not going to work.

I worked around this just by coping the tables to a new db, deleted the tables in the working db and linked the tables.

So what happens to the joins? like the global ones. They had to be deleted when I deleted the tables.

I'm on a domain environment, so I'm thinking that's why I can't split them properly, unless you have another idea.

Office 2013, current and updated.

Keep up the great work!
Brian

Reply from Alex Hedley:

Hi Brian,
If you just click save does it prompt to save as another format?
If you click Save As does it give you other options?
Were you the original developer of the access db?
Could someone else already have signed the db before?

John Kosey on 12/9/2014: Hi Richard
Your lessons made it possible for me to put together a database for a prayer group. I have it on my home pc using Access 2013. Can the database be shared with four users using Office 365's Access? Can I upload it to Access Office 365?
Thanks
John

Reply from Alex Hedley:

Hi John,
Have you seen Rich's Blog Post on the matter?
It should help with a number of options you can try.

Hans Peeters on 1/8/2015: hello,
I'm interested in a network course :-)
Hans from Belgium

CHARLES FULGHAM on 2/4/2015: Mr. Rost, another vote for vba distribution front end updates. Thanks!

Reply from Alex Hedley:

Me too

William Lewis on 3/4/2015: I have the same problem as Brian. When I followed your steps it created the back end. But now when I open the back end file it shows a warning saying that it is missing a digital signature. The type of file matches yours so nothing is different except I cannot open.

Reply from Alex Hedley:

Is this on a domain environment too?

Nicholas S on 3/18/2015: Hi, If I add a new table to my front end how do I get it to the Backend and how do I then link it for the local computer and other users
kind regards
Nick

Reply from Alex Hedley:

You'd want it adding to the Backend then you'd need to redistribute the FE to your users.

Or you would have to use some fancy SQL that's covered in the SQL Seminar Series.

Scott Schumacher on 4/7/2015: I am also interested in small office network class. Please let me know if you decide to do one.
Michael on 4/25/2015: Hi Richard / Alex,

In lesson one (Import & Link Access Objects), you show clicking and dragging a table from one database into another. When I tried this, I wasn't able to simulate what you demonstrated - I think that I may not be able to have multiple Access databases open...can you please let me know where I may be going wrong?

Thanks,
M.

Reply from Alex Hedley:

Are you getting a cross symbol when you try and drag it?
Are you dropping into the Navigation pane, table section?
Is it accdb to accdb?

Can you copy paste the objects?
(Even copy paste a table from excel should paste in as a Table in Access,)

Michael on 5/16/2015: Hi Alex,

I am not able to open two Access databases simultaneously. When I try to open a second database, Access opens it but then when I try to switch back to the first one I can't find it. I am guessing Access closed it. Is there a settings adjustment that I need to make somewhere? (Other than my brain, of course...hahahaha.) Thanks in advance for your help.

M.

Reply from Alex Hedley:

So you can't open Access ready on the choose a db screen, and repeat.
Two aren't showing in the Taskbar?
If you Alt+Tab you can't switch between them.
Does the Task Manager not show two in the Applications tab?

Fernando Romero Galvan on 5/21/2015: Interested in a networking class! That'd be cool
vicki Hudson on 5/30/2015: At 4:22 you click on "Split Database" , It gives you a file name with the extension of .accdb. Mine is showing extension.accdc (Microsoft Access Signed Package (*.accdc). I don't have an option to change it to .accdb. How can I change extension? Not sure about the .accdc functionality.

Reply from Alex Hedley:

This looks to be an issue with Access 2013
Sources say you can just rename it .accdb and it will work.
Might be worth testing this to confirm

Vicki on 5/31/2015: I tried that and it automatically added the .accdc to the end and I had a file with .accdb.accdc. Unusable file. I tried checking for updates to access as well. Thanks for the reply

Reply from Alex Hedley:

Looks like you don't have file extensions showing.
Show them then you can actually change the file extension and not just the name

Displaying the File Extension in Windows Vista and Windows 7

1. Click the Start menu. ...
2. Type "folder options" (without the quotes). ...
3. A dialog box with the title "Folder Options" will appear. ...
4. Click to uncheck the box for "Hide extensions for known file types".
5. Click the "OK" button at the bottom of the dialog box.

vicki Hudson on 6/3/2015: The front end can not find the .accdb file that I renamed. time 9:26 in video. I get the error if I rename to .accdb. I think I will split manually :-). I just like things to be exactly like in the video but we were hown how to manually do the split
vicki Hudson on 6/3/2015: Duh...When I refreshed, it worked.

DilipKumar T on 2/26/2016: Sir!
My question is ..How to import and update data only to existing Table (Table is not Linked) which have same field type and Name?
If I have to give same customer table to my friend for update data and later If I want to update that data to my existing Customers Table which I have also updated the Data in the Customers Table.

Reply from Alex Hedley:

Expert 23 covers this

 

You may want to read these articles from the 599CD News:

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP