470 likes | 532 Views
Access Database Administration. Introduction. P.O. Box 6142 Laguna Niguel, CA 92607 949-489-1472 http://www.d2associates.com. Orange Coast Database Associates. Loose association of local University instructors and IT Professionals. Started in 1996 Training
E N D
Access Database Administration Introduction P.O. Box 6142 Laguna Niguel, CA 92607 949-489-1472 http://www.d2associates.com Access Utilities
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) Access Utilities
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 Access Utilities
MS Access Introductory (100 Level) Curriculum AIN100 AIN100T AIA101 AIN102 Queries AIN104 Reports & Forms AMP110 Macros Access Utilities
ADB201 – Access Database Administration • Quick pace for experienced windows users • Assumes good prior knowledge of Access • Oriented towards those charged with administering the database Access Utilities
ADB201 – Access Database Administration • 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 CourseFormat: Access Utilities
ADB201 – Access Database Administration • Session 1 • ODBC • Access Utilities • Session 2 • Security Course Schedule (3 hour sessions): Access Utilities
Rocky2 DSN now available Note: the rocky2 DSN is available for all applications that can use ODBC, not just Access Access Utilities
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. Access Utilities
Launch Link Tables Dialogue(Use created DSN; still in second scenario) Access Utilities
Select New Rocky2 Data Source Access Utilities
Select Tables Access Utilities
New Bookstore Database with Linked Tables via rocky2 DSN Access Utilities
Launch Link Tables Dialogue(Existing DSN third scenario) Access Utilities
Admissions Database (for Relink example) Access Utilities
Launch Linked Table Manager Access Utilities
Linked Tables & Locations Access Utilities
Select Table to Relink Access Utilities
Locate Database or File (for Text, Excel ,Etc.) Access Utilities
Table Relinked Successfully Access Utilities
Access Utilities Database Splitter P.O. Box 6142 Laguna Niguel, CA 92607 949-489-1472 http://www.d2associates.com Access Utilities
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 Access Utilities
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 end-users Access Utilities
Admissions Database Access Utilities
Launch Database Splitter Access Utilities
Start the Split Access Utilities
Create Back End Database Access Utilities
Original Database (Front End) Access Utilities
Linked Table Manager Connections Access Utilities
New Back End Database Access Utilities
Access Utilities Switchboard Manager – Covered in Access Introductory Courses P.O. Box 6142 Laguna Niguel, CA 92607 949-489-1472 http://www.d2associates.com Access Utilities
Access Utilities Upsizing Wizard P.O. Box 6142 Laguna Niguel, CA 92607 949-489-1472 http://www.d2associates.com Access Utilities
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. Access Utilities
Factory 2000 Database Access Utilities
Launch Upsizing Wizard Access Utilities
Select Database Type Access Utilities
Select Server and Database Name Access Utilities
Select Tables Access Utilities
Select Attributes Access Utilities
Specify Application Approach Access Utilities
Upsize! Access Utilities
Print Upsizing Report Access Utilities
DB Window with Linked Tables Access Utilities
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 Access Utilities
Linked Table Manager Connections Access Utilities
SQL Server Database Access Utilities