Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Importing Access tables in Oracle

RE: Importing Access tables in Oracle

From: Christine Turner <christine.turner_at_ips-sendero.com>
Date: Wed, 19 Dec 2001 12:44:45 -0800
Message-ID: <F001.003E09F0.20011219123049@fatcity.com>

I other words the table names are case sensitive. But the issue you are running into is this:

You currently try and receive an ora-942

        select * from tablename;

When you should do:

        select * from "TableName";

MicroSoft products (whether access or sql server) will cause this. Good luck trying to find the exact case sensitivity and naming convention. The suggestion below seems to be the most efficient one.

Thanks,
C-

-----Original Message-----
Sent: Wednesday, December 19, 2001 12:51 PM To: Multiple recipients of list ORACLE-L

You are learning the hard way. NEVER, NEVER, NEVER create tables in Oracle by exporting from MS Access via ODBC. Access creates non-SQL compliant objects. To access these tables you will need to select "Field", "Field" from "Table Name"; using the exact spelling and case as displayed in Access. To get rid of them you will need to drop them in the same manner.

The correct way to do what you are doing is to create the objects in oracle with the names, columns, etc as they ought to be created. ie: correctly. Then ATTACH those tables into your Access database. Then write sql append queries that select from your Access tables and append into the Oracle tables. This is a little more cumbersome to do, but at least the structures will be correct.

On Wed, 2001-12-19 at 13:12, Bishwa_at_gmx.net wrote:

    Dear all,

    I have a lot of tables built in MS Access. Recently, I am trying to export

    the database from MS Access to Oracle 8i.From Access, I could export the     tables to Oracle through Oracle ODBC driver. The good thing is that this     process is relatively fast as Table struture is created automatically and I

    can actually see these tables and data in Toad. But if I try to execute any

    SQL, Oracle responds ORA-00942: table or view does not exist. I cannot     figure out what is happenning. I welcome any suggestion (including not     exporting through ODBC driver)

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com     --
    Author:
      INET: Bishwa_at_gmx.net

--
Rodd Holman
Enterprise Data Systems Engineer
LodgeNet Entertainment Corporation
rodney.holman_at_lodgenet.com

(605) 988-1373
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rodd Holman INET: rodney.holman_at_lodgenet.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christine Turner INET: christine.turner_at_ips-sendero.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Wed Dec 19 2001 - 14:44:45 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US