Converting from Oracle to Access
More detail about ODBC and the issues of attaching Oracle tables from
Access and exporting Access tables to Oracle are covered in
Microsoft Tools Front-Ending Oracle
and
Interfacing Access with Oracle
How to export Oracle tables to Access
- Install an Oracle ODBC driver
- Create a datasource based on a machine with an Oracle database
- Select Get External data from the File menu
- Choose Import rather than Link
- Choose ODBC as the file type and select the datasource
The
biggest problems are (going from Oracle to Access):
Names
names- these go over to upper case names in Access which
may not look very friendly. One can rename them at this stage since names
are case insensitive.
Fails to export
If your machine falls over because the data is to big to import, you
could use the following script
to create a comma delimitered file of the
data in Oracle and then ftp it over
document
I hope you gave the tablename on the start command
otherwise you get an 'unknown command' error message
#
undefine quote
REM This procedure writes a comma delimited file
REM where tablename is the name of the table you want to output.
REM
accept delimiter char prompt 'Delimiter?'
accept nullvalue char prompt 'Null value?'
accept quote char prompt 'Type " for text to be surrounded by quotes '
set pause off
set echo off
set termout off
set newpage 1
set verify off
set feedback off
set pagesize 0
set linesize 75
set tab off
set scan
spool /tmp/stack.tmp
select decode (rownum,1,'select ','||''&delimiter''||')
|| decode (data_type,'NUMBER','decode(','''&"e''||')
|| column_name
||
decode(data_type,'NUMBER',',NULL,''&nullvalue'','||column_name
||')','||''&"e''')
from user_tab_columns
where table_name=upper('&1');
select 'templabel from &1' from dict where rownum=1;
prompt /
spool off
set linesize 132
set tab on
set trimout on
set termout on
set scan off
spool temp.txt
start /tmp/stack.tmp
spool OFF
set heading on
set linesize 80
set scan on
set pause on
set pagesize 23
set feedback on
host mv temp.dat "&1".txt
data types
- text fields in Access (max length 255) but varchar2 (max
length 2000) in Oracle
so may want to use memo fields
- may want to use booleans rather than text fields
- You may want to set up automatic counters
- ORACLE longraw datatype
only holds 65Kb so the picture will come out very fuzzy, convert to
OLE
Relationships and Constraints
set up the relationships,
add data constraints and indexes
data content
case of text ignored in Access queries but significant in Oracle
queries
Queries
Taking queries from Oracle to Access may or may not be straightforward
depending on how standard the SQL is - again I have documented
differences between Access SQL and Oracle SQL
in Comparison of
Oracle
and Microsoft Access SQL.
-
Copy the SQL from Oracle into the clipboard
- Create a new query in Access
- View the design in SQL
- paste the Oracle query into the Access query and see if it runs
- if it does you can then view the QBE grid and see what it looks like