Re: MSAccess97(NT4SP3)-->ODBC-->Oracle7(Unix) Problem

Hi Jim.

Ready for this?

When you export stuff from Access, make sure that the table and column names are in UPPERCASE. Otherwise, Oracle won't be able to read them unless you put the table and/or column names in quotes (single, I think). I suppose quotes are still an option, but it's impractical in the long run. I'd put all the Access table and field names in uppercase and re-export. And remember, this won't make a difference to Access, because it ignores case.

Convenient, eh?

Note that Oracle stores tables and columns created with its own tools (such as SQL*Plus) in uppercase.

Annoying, huh?

I pulled out much hair over this, until a colleague pointed it out. Best of luck!

-John Hopkins
Programmer/Analyst
Bone Marrow Transplant Unit
Shands Hospital
Gainesville, FL

-----Original Message-----
From: Jim Garrison <jhg_at_austx.tandem.com> Newsgroups:
comp.databases.oracle.misc,comp.databases.oracle.server,comp.databases.oracl e.tools
Date: Tuesday, November 04, 1997 1:14 PM Subject: MSAccess97(NT4SP3)-->ODBC-->Oracle7(Unix) Problem

>I have just installed the current version of Personal
>Oracle on my NT workstation, and successfully set up
>an ODBC connection to an Oracle7 database running
>on Unix (Solaris). The connection works, and I can
>link from Access to tables on the Oracle DB with no
>problem.
>
>Then I "exported" an Access table to Oracle
>(File:SaveAs/Export:External Database:ODBC). The
>export was successful, and I was then able to link
>from Access to the newly created Oracle table, and
>am able to view and manipulate the Oracle rows from
>within Access.
>
>However, on the Oracle side all is not well. When
>examining the Oracle database with SQLPLUS on the
>Unix system, the following behavior is observed:
>
>1) The table "exists" in that it is defined in the
> system catalog tables (USER_TABLES) and its
> columns are defined, with the proper attributes
> (in USER_TAB_COLUMNS).
>
>2) Any attempt to manipulate the table with DML
> (SELECT ...) or DDL (DROP TABLE ...) statements
> in SQLPLUS result in:
>
> ORA-00942: table or view does not exist
>
>3) I get exactly the same results when manipulating
> the table using the "Personal Oracle7 Navigator"
> tool on the NT Workstation. I.e., the table seems
> to exist in that it appears in the browser display,
> but I cannot access its rows and cannot delete it.
> The error code is identical (ORA-00942).
>
>In summary:
>
> 1) A table is exported from Access to Oracle7
> via ODBC.
> 2) The newly created Oracle table is then
> linked to from Access, and behaves normally.
> 3) The newly created Oracle table appears in
> Oracle's system catalog, but any attempt to
> refer to it results in ORA-00942.
>
>Suggestions?
>
>System Config:
> AST Pentium 90
> 72MB memory
> >400MB free disk space
> Windows NT4 SP3
> MS Office97 PRO with Service Release 1
>
>Personal Oracle:
> Personal Oracle 7.2.2.4.0
> Required Support Files 7.2.2.4.1C
> SQL*Net Client 2.2.2.1.0A
> SQL*Net Server 2.2.2.1.0A
> SQL*Plus 3.2.2.0.1
> Oracle TCP/IP Adapter 2.2.2.1.0
> Oracle7 ODBC Driver 1.13.5.0.4
> Oracle Call Interface 7.2.2.3.1
>
>Unix Oracle Version: 7.3.3.0.0
>
>--
>James Garrison mailto:jhg_at_austx.tandem.com
>Tandem Computers, Inc
>14231 Tandem Blvd, Rm 1038 Phone: (512) 432-8455
>Austin, TX 78728-6699 Fax: (512) 432-8247
 Jim Garrison wrote in message <345F65F9.541E_at_austx.tandem.com>...
>I have just installed the current version of Personal
>Oracle on my NT workstation, and successfully set up
>an ODBC connection to an Oracle7 database running
>on Unix (Solaris). The connection works, and I can
>link from Access to tables on the Oracle DB with no
>problem.
>
>Then I "exported" an Access table to Oracle
>(File:SaveAs/Export:External Database:ODBC). The
>export was successful, and I was then able to link
>from Access to the newly created Oracle table, and
>am able to view and manipulate the Oracle rows from
>within Access.
>
>However, on the Oracle side all is not well. When
>examining the Oracle database with SQLPLUS on the
>Unix system, the following behavior is observed:
>
>1) The table "exists" in that it is defined in the
> system catalog tables (USER_TABLES) and its
> columns are defined, with the proper attributes
> (in USER_TAB_COLUMNS).
>
>2) Any attempt to manipulate the table with DML
> (SELECT ...) or DDL (DROP TABLE ...) statements
> in SQLPLUS result in:
>
> ORA-00942: table or view does not exist
>
>3) I get exactly the same results when manipulating
> the table using the "Personal Oracle7 Navigator"
> tool on the NT Workstation. I.e., the table seems
> to exist in that it appears in the browser display,
> but I cannot access its rows and cannot delete it.
> The error code is identical (ORA-00942).
>
>In summary:
>
> 1) A table is exported from Access to Oracle7
> via ODBC.
> 2) The newly created Oracle table is then
> linked to from Access, and behaves normally.
> 3) The newly created Oracle table appears in
> Oracle's system catalog, but any attempt to
> refer to it results in ORA-00942.
>
>Suggestions?
>
>System Config:
> AST Pentium 90
> 72MB memory
> >400MB free disk space
> Windows NT4 SP3
> MS Office97 PRO with Service Release 1
>
>Personal Oracle:
> Personal Oracle 7.2.2.4.0
> Required Support Files 7.2.2.4.1C
> SQL*Net Client 2.2.2.1.0A
> SQL*Net Server 2.2.2.1.0A
> SQL*Plus 3.2.2.0.1
> Oracle TCP/IP Adapter 2.2.2.1.0
> Oracle7 ODBC Driver 1.13.5.0.4
> Oracle Call Interface 7.2.2.3.1
>
>Unix Oracle Version: 7.3.3.0.0
>
>--
>James Garrison mailto:jhg_at_austx.tandem.com
>Tandem Computers, Inc
>14231 Tandem Blvd, Rm 1038 Phone: (512) 432-8455
>Austin, TX 78728-6699 Fax: (512) 432-8247
Received on Wed Nov 05 1997 - 00:00:00 CST