Skip to main content

How to Connect MS-Access with Oracle Database

How to Connect MS-Access with Oracle Database

You can configure heterogeneous service to allow an oracle database to connect with Microsoft Access database from about any platform Linux or Windows.
ODBC stands for Open database connectivity is an interface written in C program. Oracle ODBC driver provides a standard interface that allows one application to access many different data sources.
Differentiate between ODBC and Net8?
ODBC is a database independent where as NET8 is database dependent.
ODBC is used to connect to any database that is supported by SQL where as Net8 is an oracle protocol, is used to connect only oracle database.
ODBC uses drivers for connecting to different database where as NET8 uses network listener for connecting to oracle database.
What are requirement to work on ODBC?
–        A client software such as SQL*Net or Net8 on the client machine. However a third party solution OCI driver already includes within the driver thus no need oracle Net installation.
–        A TCP/IP connection is required.
How to attached oracle table in MS-Access?
–        Start the linking process by either selecting File–>Get External data–>Link Table or right click on within the table list and select link table.
–        Select ODBC from the drop down list.
–        Then from Machine data source tab select the appropriate data source name and click OK. This will open a login dialog box.
–        Once we login successfully onto oracle, a list of table displayed.
–        Now you are able to use this table in the MS-Access. 
Prepare MS-Access Environment:
Create a database in MS-Access and create at least one table in this database. While creating the database ensure *.mdb file is used to save the Access database. For Example: you can see in the below picture a table named ‘shahid’ is created under the database db1.

Define ODBC Connectivity:
Open the utility Microsoft ODBC Administrator and click on ‘Add’ button to add a new data source by providing description, service name and user ID & password. Generally if you expand the Start->Programs->ORACLE-HOME->Configuration and Migration tools path, you will find this utility. Finally test your connection before moving to next step.

Prepare the Oracle Environment:
Configure the Oracle Listener on the Windows machine. Add the following entry in LISTENER.ORA and TNSNAME.ORA
 (SID_LIST =
  (SID_DESC =
   (SID_NAME = hsodbc)
   (ORACLE_HOME = D:\Oracle\ora92)
   (PROGRAM = hsodbc)
  ))
ACCESS_DB.WORLD =
  (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = DBSERVER) (PORT = 1521)
  )
  (CONNECT_DATA = (SID = hsodbc)
  )
  (HS=OK)
 )
Ensure to check the tnsping for new entry and if needed you can start/stop listener from the command line:
C:\>tnsping access_db
C:\>lsnrctl stop
C:\>lsnrctl start
Configure Oracle HS: Edit the ORACLE_HOME\hs\admin\inithsodbc.ora file and add your ODBC System DSN Name.
HS_FDS_CONNECT_INFO = odbc1
HS_FDS_TRACE_LEVEL = off
Connect Oracle with Sys user and execute this script.
SQL>@D:\oracle\RDBMS\ADMIN\caths.sql
SQL> select * from SYS.HS_FDS_CLASS;
FDS_CLASS_NAME FDS_CLASS_COMMENTS FDS_CLASS_ID
-------------  ------------------ -------------
BITE Built-In Test Environment 1
Create a database link to access from Oracle database
SQL> CREATE DATABASE LINK access_db USING ‘access_db.world’; 
Database link created.
The tables in the MS-Access database can now be accessed from the Oracle environment.
SQL> SELECT * FROM shahid_table@access_db;
        ENO ENAME        SALARY     ADDRESS
 ---------- ------------ --------   ---------------------
         1 shahid       6000       Riyadh
         2 xyz           5000       Delhi
         3 pwz          4000       Banglore
         4 bwz          3000       Mumbai
SQL> CREATE TABLE my_oracle_tab AS SELECT * FROM Shahid_table@access_db;

Table created.

Comments

Post a Comment

Popular posts from this blog

Tablespace Management using Toad

You can view all of your tablespace information using toad tablespace screen from Database –> Administer –> Tablespaces Here each table describes different detailed information across all the tablespaces in database. If you open this screen from the toad DBA module then you are also able to see the “space history” and “IO history” tab along with Files, Free space, object, Fragmentation. With these tab you are able to able perform space monitoring and planning steps. These tab permits you check graphically space and IO usage over time. Through this way DBA are also able to estimate ‘Object size’ based on this estimation. From the below “alter tablespace” tab you can §          Modify the tablespace and datafile size. §          Add new datafile to the tablespace.
Post a Comment
Read more

Import Excel Data into Oracle using Oracle form Button

Import Excel Data into Oracle using Oracle form Button 1. Create a button on the form with the name "IMPORT_EXCEL" and write a pl/sql on "WHEN BUTTON PRESSED" trigger. BEGIN IF :System.Cursor_Block<>'GL_DAILY_COMPOUND_HEADER' THEN   Go_Item('GL_DAILY_COMPOUND_HEADER.V_DATE'); END IF; IF :System.Mode = 'NORMAL' AND :System.Record_Status IN ('NEW','INSERT') THEN   IMPORT_EXCEL_PROC;   ---Form procedure ELSE  MESSAGE('Import allowed only for new entry!!!');  MESSAGE('Import allowed only for new entry!!!'); END IF; END; 2. Now write the procedure "IMPORT_EXCEL_PROC" into the program unit. Don't forget to create required table and folder for procedure IMPORT_EXCEL_PROC PROCEDURE IMPORT_EXCEL_PROC IS application    OLE2.Obj_Type; workbooks      OLE2.Obj_Type; workbook       OLE2.Obj_Type; worksheets      OLE2.Obj_Type; worksheet       OLE2.Obj_Type; ce...
Post a Comment
Read more

Changing National Character Set AL16UTF16 to UTF8

Changing National Character Set AL16UTF16 to UTF8 The national character set is used for data that is stored in table columns of the types NCHAR, NVARCHAR2, and NCLOB.  In contrast, the database character set is used for data stored in table columns of the types CHAR, VARCHAR2 and CLOB. Like the database character set, the national character set is defined when the database is initially created and can usually no longer be changed, at least not easily or without involving quite a lot of work (export, recreate database, import). Except when creating the database, where the national character set is defined explicitly, it can change implicitly even when upgrading the database from Oracle8i to Oracle9i (or Oracle10g). You require SYSDBA authorization to change the national character set. Changing the national character set means changing an Oracle Dictionary entry, but no data is changed.  $sqlplus /nolog SQL> CONNECT / AS SYSDBA SQL> Select property_...
Post a Comment
Read more