Converting from Oracle to Access

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