Access Utilities - D. H. D`Urso and Associates
Download
Report
Transcript Access Utilities - D. H. D`Urso and Associates
Access Database
Administration
Introduction
P.O. Box 6142
Laguna Niguel, CA 92607
949-489-1472
http://www.d2associates.com
Multiple Cases
Access Utilities
1
Orange Coast Database Associates
Loose association of local University instructors and
IT Professionals.
Started in 1996
Training
Open Enrollment (public classes in San Juan
Capistrano using standardized material)
On-site (custom classes for groups)
Private desk side training for individuals
Consulting & Programming (incl. offshore)
Multiple Cases
Access Utilities
2
ADB201 Access Database
Administration
Introduction (s)
Facilities
Course Packet
Student Questionnaire
Curriculum overview
Syllabus
List of Useful Books
PowerPoint handouts for all sessions
Laminated Reference Guide to Access
Evaluation form in back
Training certificate
Multiple Cases
Access Utilities
3
Multiple Cases
Access Utilities
4
MS Access Introductory (100
Level) Curriculum
AIN100T
AIN100
AIA101
AIN102
Queries
Multiple Cases
AIN104
Reports
& Forms
Access Utilities
AMP110
Macros
5
ADB201 – Access Database
Administration
Quick pace for experienced windows users
Assumes good prior knowledge of Access
Oriented towards those charged with
administering the database
Multiple Cases
Access Utilities
6
ADB201 – Access Database
Administration
Course Format:
2 Sessions
Lecture
Demo
Student “hands-on” - by the end of the
class the student will have worked
through all the Access Utilities and
secured a database
Exercises are cumulative – later examples
build on objects created earlier
Multiple Cases
Access Utilities
7
ADB201 – Access Database
Administration
Course Schedule (3 hour sessions):
Session 1
ODBC
Access Utilities
Session 2
Multiple Cases
Security
Access Utilities
8
Rocky2 DSN now available
Note: the rocky2 DSN
is available for all
applications that can
use ODBC, not just
Access
Multiple Cases
Access Utilities
9
Link Tables
(Use created DSN; still in second scenario)
We will now go through an abbreviated series
of slides showing how the new DSN can
used.
Multiple Cases
Access Utilities
10
Launch Link Tables Dialogue
(Use created DSN; still in second scenario)
Multiple Cases
Access Utilities
11
Select New Rocky2 Data Source
Multiple Cases
Access Utilities
12
Select Tables
Multiple Cases
Access Utilities
13
New Bookstore Database with Linked
Tables via rocky2 DSN
Multiple Cases
Access Utilities
14
Launch Link Tables Dialogue
(Existing DSN third scenario)
Multiple Cases
Access Utilities
15
Admissions Database (for Relink example)
Multiple Cases
Access Utilities
16
Launch Linked Table Manager
Multiple Cases
Access Utilities
17
Linked Tables & Locations
Multiple Cases
Access Utilities
18
Select Table to Relink
Multiple Cases
Access Utilities
19
Locate Database or File (for Text,
Excel ,Etc.)
Multiple Cases
Access Utilities
20
Table Relinked Successfully
Multiple Cases
Access Utilities
21
Access Utilities
Database Splitter
P.O. Box 6142
Laguna Niguel, CA 92607
949-489-1472
http://www.d2associates.com
Multiple Cases
Access Utilities
22
Database Splitter
Splits an Access database into two
components
A new “back end” database with “_be”
appended to the original database name. This
database has the tables
The original database with the tables replaced
by table links to the tables in the new back end
database
Multiple Cases
Access Utilities
23
Why Split a Database?
Pro
Can improve performance
Allows front end to be changed or overlaid
without disturbing the back end data
Con
May complicate maintenance, esp. by endusers
Multiple Cases
Access Utilities
24
Admissions Database
Multiple Cases
Access Utilities
25
Launch Database Splitter
Multiple Cases
Access Utilities
26
Start the Split
Multiple Cases
Access Utilities
27
Create Back End Database
Multiple Cases
Access Utilities
28
Original Database (Front End)
Multiple Cases
Access Utilities
29
Linked Table Manager Connections
Multiple Cases
Access Utilities
30
New Back End Database
Multiple Cases
Access Utilities
31
Access Utilities
P.O. Box 6142
Laguna Niguel, CA 92607
949-489-1472
http://www.d2associates.com
Multiple Cases
Switchboard Manager –
Covered in Access
Introductory Courses
Access Utilities
32
Access Utilities
Upsizing Wizard
P.O. Box 6142
Laguna Niguel, CA 92607
949-489-1472
http://www.d2associates.com
Multiple Cases
Access Utilities
33
Upsizing an Access Database
Upsizes to MS SQL Server only
Several approaches
Create tables in SQL Server only
Create tables in SQL Server and add table
links to existing Access database (we will use
this approach)
Create a “project” file (.adp). Deprecated in
Access 2007.
Multiple Cases
Access Utilities
34
Factory 2000 Database
Multiple Cases
Access Utilities
35
Launch Upsizing Wizard
Multiple Cases
Access Utilities
36
Select Database Type
Multiple Cases
Access Utilities
37
Select Server and Database Name
Multiple Cases
Access Utilities
38
Select Tables
Multiple Cases
Access Utilities
39
Select Attributes
Multiple Cases
Access Utilities
40
Specify Application Approach
Multiple Cases
Access Utilities
41
Upsize!
Multiple Cases
Access Utilities
42
Print Upsizing Report
Multiple Cases
Access Utilities
43
DB Window with Linked Tables
Multiple Cases
Access Utilities
44
Using Linked Tables
Linked tables can be manipulated as if they
were regular tables, subject to certain
restrictions.
Can be used in queries, reports, forms
Data can be modified
But… table structure cannot be altered via
Access
Multiple Cases
Access Utilities
45
Linked Table Manager Connections
Multiple Cases
Access Utilities
46
SQL Server Database
Multiple Cases
Access Utilities
47