Access Utilities
Download
Report
Transcript Access Utilities
Access Utilities
P.O. Box 6142
Laguna Niguel, CA 92607
949-489-1472
http://www.d2associates.com
Multiple Cases
Built-in Access Utilities for
Developers, Users &
Administrators
Access Utilities
1
Access Utilities
Multiple Cases
Access Utilities
2
Access Utilities
P.O. Box 6142
Laguna Niguel, CA 92607
949-489-1472
http://www.d2associates.com
Multiple Cases
Back Up Database
Access Utilities
3
Launch Backup Utility
Multiple Cases
Access Utilities
4
Name the Backup File
Multiple Cases
Access Utilities
5
Directory Listing w/ New Backup
Multiple Cases
Access Utilities
6
Access Utilities
P.O. Box 6142
Laguna Niguel, CA 92607
949-489-1472
http://www.d2associates.com
Multiple Cases
Linked Table Manager
Access Utilities
7
Linked Tables
Powerful feature of Microsoft Access
Very widely used
Sets up “pointers” in the database
Can link to text, Excel, dBase, etc.
Another type of data source is ODBC
We will link to ODBC as one example
Then we will “relink” regular Access tables as
another example
Multiple Cases
Access Utilities
8
Linked ODBC Tables
ODBC = Open Database Connectivity
Microsoft supplies a generic middleman
driver with Windows
Vendors supply drivers for their specific
database or data source
This enables Access to link to data in almost
any kind of database including Oracle, MS
SQL Server, DB2, MySQL, etc.
Multiple Cases
Access Utilities
9
Launch Link Tables Dialogue
Multiple Cases
Access Utilities
10
Link Dialogue
Select
files of
type
ODBC
Multiple Cases
Access Utilities
11
Select Data Source
Multiple Cases
Access Utilities
12
Login to Data Source
Multiple Cases
Access Utilities
13
Select Tables to Link
Multiple Cases
Access Utilities
14
Access Database Window with Linked
Tables
Multiple Cases
Access Utilities
15
Admissions Database
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