Transcript Connecting to CAP Server & Converting MS Access Database from

How to Connect to the CAP
Server
&
Convert MS Access Databases
from
RDB to ORACLE
(CAP server environment)
Marshall Silberstein (with additions by Dawn Syverson)
Reporting and Data Services
[email protected]
12/5/07
1
Important Notices
• It is VERY IMPORTANT to make a copy of your
MS Access database for backup purposes
before making any changes. You may need to
revert back if you run into problems.
• All examples assume MS Access 2003
12/5/07
2
Connecting to the CAP Server
To access the CAP Server to run
adhoc queries, use START/All
Programs/Accessories but could
also be found under
START/Programs/CAP/CAP Server
Contact the IT Desk at
[email protected] if you have
trouble locating this link.
12/5/07
3
Connecting & Enabling Shared
Resources
• Identify where your MS Access database
resides; is it a local C or D drives,
campus network H or S drive, etc?
• Access the CAP Computer using
Start/All Programs/CAP/CAP Server
link (see previous slide)
12/5/07
4
Login to theCAP
Server using your
Network id and
password (the
same ones you use
for logging into
your computer).
12/5/07
5
The ‘My Computer’ window may
open and display all of your
drives, including the CAP
server, your shared network
and local PC.
Your drives are now remotely
connected.
You may close the window.
12/5/07
6
You may now open
MS Access from
within the CAP
server environment.
12/5/07
7
How to Convert Your MS Access dB
from RDB to ORACLE
Method 1 – using Linked Table
Manager (a quick way to
convert databases)
12/5/07
8
Method 1 - Linked Table Manager option
•
•
12/5/07
Open your MS Access database (from within CAP environment)
Select Tools, Database Utilities, Linked Table Manager
9
Select all the appropriate tables, then select ‘Always prompt for new location’ box. Then select ‘OK’.
Note the DSN name.
When we complete
the conversion
process the DSN
name will be
different! It will
read the name of
your new ORACLE
database!
12/5/07
This is
important! It
tells the
machine to look
for the new
Data Source
10
The ‘Select Data Source’ window will display. Select the ‘Machine Data Source’ tab.
Select
‘Machine
Data
Source’
Tab
12/5/07
11
Scroll down to the name of the new ORACLE dB data source.
Highlight it, then Click ‘OK’
raredata is
the data
source. See
the next
page about
database
names.
12/5/07
After
highlighting
raredb,
Click ‘OK’
12
You will now be prompted to enter your ORACLE User Name and Password.
NOTE: This login/password combination is the one you would have received after having
completed the Operational Data Security Form sent out by IT Desk. If you do not have a
username for this system contact the IT Desk at [email protected] to obtain one.
Service
name (i.e.
database
name)
User
Name
ORACLE
Password
12/5/07
Click
“OK”
13
After Clicking ‘OK’, the tables will begin to refresh their links (note the small status bar on
bottom left). This may take a few minutes and is dependent upon on the number of tables
in your MS Access database.
When the Refresh
is complete, note
how the tables
have linked to the
ORACLE database.
In our example it
reads DSN=raredb
Status
bar
Click “OK”. You
have successfully
converted your
RDB tables to
ORACLE.
12/5/07
14
ERROR MESSAGES
•
You may see several error messages when using the Linked Table Manager.
Examples
•
“There are several tables with that name. Please specify owner in the format ’owner.table’.
•
“The Microsoft Jet database engine could not find the object ‘XX_XXXX_XXX’. Make sure the
object exists and that you spell its name and path name correctly.”
•
If so, it means the Link Table Manager could not determine ownership or location of the table
and will not convert it. Write down the table names. You will need to uncheck the tables it
erred on (ST_ADM_STAT, ST_APP, and ST_GRADE_HIST in the example) and move on.
•
You will want to use the manual method for linking these tables, Method #2.
12/5/07
15
You may now close the ‘Linked Table Manager’ window
You should be transported back to your database, Table View
12/5/07
16
Now open a table and test the conversion. Run an existing query or two.
The query should run smoothly and give you results. If so, you are all done. If not, you may need
to rebuild the query manually. See Method #2. Congratulations upon successfully converting
your tables and queries from RDB to the ORACLE database!
Sample of
successful
query
12/5/07
17
How to Convert Your MS Access dB
from RDB to ORACLE
Method 2 – Manually
converting each table, line-byline. A very thorough option,
where tables are renamed,
linked and tested.
12/5/07
18
Method 2 - Manual conversion option
Start with your current MS Access database (which is linked to the RDB database)
Right Click mouse in white space, select ‘Link Tables…’
12/5/07
19
Select drop-down ‘Files of Type’, the ‘ODBC Databases () ‘
12/5/07
20
Select the Systen DSNtab, then select the ORACLE server name (raredata), click ‘OK’
12/5/07
21
Enter your ORACLE User Name and ORACLE Password, click ‘Ok’
12/5/07
22
You will now see a list of database System Tables that are unfamiliar. DO NOT USE THEM.
12/5/07
23
•
•
•
12/5/07
Scroll down (or hit the ‘I’ key) to find tables beginning with ‘ISRS.TABLENAME’. This is
the new naming convention.
Find the ISRS. tables you want to add because they are going to replace the older RDB
tables. Click ‘OK’ when done selecting and highlighting the tables.
NOTE: while most tables are found in the isrs. Schema, information considered ‘private’
data and requiring High user rights can be found in the ods. Schema and some systemwide validation tables are now located in the isrsval. Schema – if you can’t find the table
you are looking for within the isrs schema try one of the others. A list of tables and
their schemas can be found at
http://its.mnscu.edu/reportanddataservices/operationaldata/index.html in the document
titled operational data tables and views
24
DO NOT SELECT a Unique Record Identifier when prompted, because it will present problems
with your data. Click ‘Cancel’
DO NOT SELECT a
Unique Record
Identifier when
prompted, because it
will present problems
with your data.
Click ‘Cancel’
12/5/07
25
You will now see two sets of tables within your database. They will be in alphabetical order. The
next step is to rename tables because we are going to keep one set and eventually delete
the other. Then your existing queries will pull from the new machine source tables and
you will not have to rebuild queries. This will save you a lot of time!
Two sets of tables are visible in alpha order…
Old familiar (from RDB database)
New starting with “ISRS_ “ (from ORACLE)
RDB
Notice how they are named!
ORACLE
Tables from RDB
CT_COU
CU_COU
FA_PKG_AWD_CNTRL
SPEC_CMMT, etc.
RDB
12/5/07
New tables from ORACLE
ISRS_CT_COU
ISRS_CU_COU
ISRS_FA_PKG_AWD_CNTRL
ISRS_FA_PKG_BUDG_CNTRL
ISRS_SPEC_CMMT
ISRS_ST_APP
ISRS_ST_COU
ISRS_ST_TERM_MGMT
26
Rename the old RDB tables. The suggestion is to place the letter “z” in front of them. This
disables them. See the FAQ below.
Steps to Rename…
Right click the old table
name
Select ‘Rename’ from
the popup box
12/5/07
27
FAQ … Inquiring minds ask…
Question
Why the letter “z”?
Answer
Tables appear in alphabetical order and by entering a “z”
character; you are forcing all of the old tables to sort to the
bottom of the list. This will help you visually and make it much
easier when the time comes to delete the old RDB tables!
12/5/07
28
The old table name will be active and blue in color. Type in a lower case “z” in the front of the
name. Caution not to delete the name. Just enter a “z” in the front of them. Move your
cursor to the next old table name and complete your entry of “z’s”.
This image
illustrates how
the name field
looks when
‘active’.
12/5/07
29
This image shows how all
the old tables have
automatically relocated to
the bottom of the list
because they have been
renamed with the letter
“z”.
The “z” acts as an alpha
sorter and visual helper.
12/5/07
30
•
Guess what? NOW is time to rename all of your new ORACLE tables to be the same as your
original tables. (Yes, you are in the home stretch now! Can you feel it?). These may be the
same names as those in existing queries, only they will pull from the ORACLE database and
not the RDB database. However, there may be instances where a query may need to be
rebuilt because of a new table name.
This shows you how to rename a
new ORACLE table. Right click
on the table name, select
‘Rename’ from the popup menu,
and highlight the part you want
to delete, then ‘Delete’ it. You
will be left with a familiar
reading table name.
In this example we are going to
change ISRS_CT_COU to be
CT_COU.
12/5/07
31
This image shows
you what it looks
like after you
deleted the
leading “ISRS_”
Continue
deleting the
“ISRS_” from
the remaining
new tables.
12/5/07
32
Newly
renamed
Oracle
Tables
And in this image, we see how all
the new tables have been
renamed. The old tables are at
the bottom of the list, starting
with the letter “z”.
Old
RDB
Tables
12/5/07
33
•
AT THIS POINT, it is time to open some tables. If satisfied, run existing queries. Test
to see if they connect and run from ORACLE.
•
If you are not satisfied with the performance of your queries, return to table view and
make sure all of your tables are connected to the ORACLE database. You may also need
to build new queries if you cannot assure the data source.
•
Last Step: kick back and relax. Congratulations! You have successfully converted
your MS Access database from RDB to ORACLE.
Questions or Comments?
Contact Marshall
Silberstein
Reporting and Data
Services (RADS)
[email protected]
12/5/07
34
FAQ’s - Inquiring minds want to know…
Question:
Answer:
WHAT HAPPENS IF I RUN INTO A PROBLEM USING METHOD #1 LINKED TABLE MANAGER or
I do not get the desired results I am looking for?
Then you will need to use Method #2 – an excellent and thorough method, where you visit, fix and
manually link tables.
Question:
Answer:
What happens if I do not find my table within the ORACLE ‘ISRS_’ schema?
Search carefully within the ‘ISRS_’ and ‘ISRSVAL_’ schemas. If the table is not present you will
need to determine if the missing table is local to your campus or not. Local tables are those
created on campus to meet local needs. They need to be rebuilt and cannot be automatically
converted. If the missing table is an ISRS Table or View, contact Marshall Silberstein at
[email protected].
Question:
Answer:
What are ‘ISRS_’ , ‘ISRSVAL_’ and ‘ODS_’ schemas?
These are new naming conventions used in the CAP Oracle environment.
ISRS_ are campus data tables and data views
ISRSVAL_ are MnSCU system defined tables and views
ODS_ are added value tables and views
Question:
Answer:
How do I know if a table is local or linked by ODBC?
Table View will provide visual clues...
1. If it is a globe and arrow
it means the table is linked by ODBC. However until you look
at Link Table Manager you may not know what it is linked to. It could be linked to MnSCU RDB,
MnSCU ORACLE, MS SQL server, etc.
2. If it is a small rectangle
12/5/07
it means that it is a locally built table residing on your Access dB.
35