describes problems using Access as
frontend, ODBC Configuration, year 2000
Internal Tel No: 89869
(last revised February 2011).
This page is also in
- Use Data Sources (ODBC) from the Administrative Tools section of the Control Panel if you can not find the ODBC administrator
Oracle Database Name resolution by LDAP- an Introduction
Connecting to the Datahub (gives ORA files)
- New training document:
Accessing the DataHub using ODBC (39 pages)
to create an ODBC data
source to access Oracle data
Importing Data from Excel into
Access. It is not possible to export
directly into Oracle from Excel since ODBC is not listed as a
possible file type. Export or link into
an Access database, and then export into Oracle. It is however
possible to get Oracle data directly into a spreadsheet
using Data/Get External data/new database query
Comparing Oracle and Access SQL (Jun 98)
- ODBC Year
2000 Information (Dec 98)
Converting from Access to Oracle (mod Jan 2004) contains useful
information about names, data types, speeding up
etc. Also contains a useful Oracle
script to rename column_names so that they are useable in Oracle.
- To convert Access yes/no fields to Oracle use:
decode(ENGLISH_TEST_REQUIRED,0,'N','Y') in your Oracle script
Access section with tutorials and solutions- includes
how to create an
autocounter (cannot use an autocounter as a default in Oracle)
- ADO Connection
- Oracle ODBC FAQ
- A conference paper I presented (Oracle User Group Sep 95) about
interfacing Access 2 with Oracle 6.
Gives timings and hints and tips on speeding up queries and forms
any version of ODBC and Oracle), error mesages, comparisons of Access and
datatypes, MSysConf etc.
Interfacing Access with Oracle . Powerpoint summary of ODBC points
and some more timings
- Passing data between Microsoft Office products and ORACLE
To restrict the list of linked tables to avoid seeing the whole of the
create an ALL_OBJECTS view and ALL_SYNONYMS view in the user's schema.
--NOTE that this technique has the limitation that only objects actually
owned by the view user will be viewable. For example, if the user has
a synonym that points to an object in another user's schema, it will not
- Query times out with 1013 user requested cancel of current
- set connectiontimeout and query_timeout to 0 using
- check configuration parameters of data source with ODBC Administrator-
disable enable query
timeout, enable LOBs, enable Result sets, enable Disable MTS
- look at Tools/options/Advanced- you may want to decrease the ODBC
- Look at the properties of the query and set ODBC timeout to 0.
By default it is set to 60 seconds
- Object invalid or no longer set
someone has changed the definition of the linked table- it may not always
be cleared by the linked table manager especially if the name has been
changed- in this case, reattach the table and redefine the links.
Another reason is that the data types of linked tables do not match
local tables exactly eg Oracle data type number(12) does not consistently
Access data type decimal 12 Access data type decimal 12 and may cause this
(Access long integer only maps to number(10,0). Joins seem to work on
Windows 2000 but not
Windows NT (same database, same version of Access, same version of ODBC!)- one either gets the object
error inconsistently when one tries to save the design or one gets no results at all (again
inconsistently). To get round, this you should always define the oracle data type as number(10) to link
to Access long integer on a local table.
This problem can also result in no current record error
- The data values show as #deleted in a query or a form (but not
table view or on a report!). Try refreshing the link to the tables.
The other problem may be that you may have specified the wrong primary
key (can happen if the table is actually an Oracle view). If you have
specified the correct primary key and the table is read
only, try reattaching the table making sure not to specify a primary key
- SQL Plus Worksheet seems to have been introduced on the Oracle 8i
disc. It is found under the database administration set of programs. It is
very confusing to use. It is far better to use SQL Plus from the
Application Development set of programs
Installation and Configuration
Oracle 11 client
Use Data Sources (ODBC) from the Administrative Tools section of the Control Panel if you can not find the ODBC administrator
Oracle 10 client
- Installing Net10
and ODBC on Windows2000 or XP
- Oracle Client 10.2.0.1.0 can use LDAP directory rather than Oracle
Edit LDAP.ORA and SQLNET.ORA.
as described in
install the Oracle 10 client on an XP
machine that already had Oracle 9 client on it
will break all the existing ODBC connections -
they can not be edited, only deleted
(reinstalling Oracle 9 will enable existing connections to work again)
and recreated it using the
Oracle 10 ODBC Driver.
- When Testing Connection if you got an error "Resolving ORA-12705:
Cannot access NLS data files or invalid environment specified",
you need to verify that NLS_LANG is "unset" at the
* Windows - The NLS_LANG must be unset in the Windows registry
(re-named is best). Look for the NLS_LANG subkey in the registry at
\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE, and rename it."
Rename all such keys as there may be several, and Voila! it works.
Oracle 9 client
- The Oracle 9i CD installs
happily on XP- it
works even if the database is still
version 7 and you are not using Oracle Internet directory.
- You should have Oracle 9 ODBC installed with the security option
- Installing Net9
and ODBC on Windows2000 or XP
How to remove data source names from the registry. This is useful if
you have mangled your ODBC installation and could not deinstall previous
- Installing Net8
and ODBC on Windows2000
- includes setting up a data source.
- If you are using Windows 2000, make sure you download the latest
version of ODBC from
at least 8.1.5 (CD with 8.1.5 for NT just hangs when you
though tnsping OK)
University sqlnet.ora files
to allow use of central Oracle Names servers
- While establishing connection for the first time to the database, you get error Error cannot load resource file
- Use the DOS prompt to test the connection using tnsping connection_name
- If this is successful, look at the user path by typing path. This should show \oracle\ora90\bin first
- Look at the system path by looking at the environment variables of My Computer. This should be the same as the user path
- If there is still a problem, copy sqresus.dll, and oranmsp.dll from \oracle\ora90\bin to the \winnt folder
Oracle 8 client
Office 2000 ODBC link problems to Oracle
SQL*NET V2 Configuration and diagnostics (revised January 2001)
To test if the connection works, use the DOS prompt from the Run
typing cmd. You can then type tnsping connection_name and be
able to read the result.
If you are getting dns configuration errors, and ping works and
tnsping does not work, and error number 1114 referring to sqlresus.dll,
reboot the machine. A network error has caused a problem somewhere!
There is a bug in the installation of the Oracle8 client (8.1.7). The
Pentium 4 systems, causing the software to refuse to install.
To get it to work copy the installation to disk (or server) and rename all
occurrences (of symcjit.dll to e.g. symcjit.old.
The software will install and work.
I have it running on W2k and XP.
- If you are installing Oracle Client 8i (8.1.5) on Windows 2000 make sure
that there is no space in
the directory filename.
By default, it may try to install in the Program Files folder. If you accept this you
get the message:
- Net8 Configuration Assistant "failed(see details..." (details:
Class not found:Files\Oracle\ora8i\network\jlib). Net8 Easy Config
The ODBC driver will still work if you can copy listener.ora, sqlnet.ora and tnsnames.ora
from another machine into network\admin.
tnsping, sqlplus and odbc administrator will work, but odbctest and net8 configuation assistant
will not work. This error arises because
Java Installer doesn't manage space in installation path.
Maintained by Maggie Shapland (set up 1999) revised June 2008
Computing Service, University of Bristol BS8 1UD