Microsoft Access Database Connectivity

[sani204]

Dear Member

How we can connect to Microsoft Access Database Through Oracle Forms?

I want to retrive data from access table and insert into Oracle table?

[admin]

You will have to install and configure ODBC drivers for access, foxpro etc and then in oracle forms you can use in the following way,

select * from my_table@ODBClink;

[mirza_rehan]

You can connect through this
UserName/Password@ODBC:odbcDriverName



Best regards,
R E H A N M I R Z A
System Analyst / Senior Oracle Developer
Cell: +92-304-2120807

When ever you pray
Please remember me and my family

[ahmadbilal]

<hr noshade size="1"><u><i><b>Folllow This Procedure For HSODBC</b></i></u>

How to setup a database link between a Microsoft MDB file and the
Oracle Database using Generic Connectivity for ODBC
-----------------------------------------------------------------

1) With the OUI (Oracle Universal Installer) install
Generic Connectivity using ODBC. This product is part of the server
installation, because a listener is needed.
A directory called HS is created.

2) The Generic Connectivity utility needs data dictionary tables in the
Oracle database. To check their existence, run a query on
i.e. SYS.HS_FDS_CLASS.
If it fails, run the caths.sql script located in ORACLE_HOME\RDBMS\ADMIN\
as user sys or internal.

3) Install the third party ODBC driver from Microsoft.

4) With ODBC Admin Utility configure a SYSTEM DATASOURCE.
i.e. specify as Data Source Name mstest and select as mdb file
the Northwind.mdb from Microsoft.

5) Now configure tnsnames.ora. This file is in ORACLE_HOME\NETWORK\ADMIN.
Add the following lines to the file:

hsodbc.de.oracle.com =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)
(HOST=<HOSTNAME>) |<- adjust hostname
(PORT=1521)) |<- adjust port
(CONNECT_DATA=(SID=hsodbc))
(HS=OK)
)

Please make sure the HOST and PORT entries refer to the ORACLE listener
which contains the HSODBC SID entry. It MUST NOT refer to the foreign database
host and port.
Note, a sample file is located in ORACLE_HOME\HS\ADMIN.

6) Configuring listener.ora:
This file is in ORACLE_HOME\NETWORK\ADMIN.
Add the following line to the SID_List of the listener.ora and restart the
listener afterwards. ( After the restart a service handler for hsodbc should
exist).

(SID_DESC=
(SID_NAME=hsodbc)
(ORACLE_HOME=e:\oracle) |<- adjust Oracle_Home directory
(PROGRAM=hsodbc)
)

Note, a sample file is located in ORACLE_HOME\HS\ADMIN.

7) Adjust the configuration file of the gateway. It is located in
ORACLE_HOME\HS\ADMIN. The name depends on the SID you use for the
Heterogeneous Service.
In our sample the listener SID is hsodbc and so the configuration file
is inithsodbc.ora.

# This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent.
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = mstest |<- ODBC Data Source Name configured in step 4
HS_FDS_TRACE_LEVEL = off |<- trace levels are from off to debug



Testing the connectivity between Oracle database and the mdb file:
- create a datbase link:
create database link hsodbc using 'hsodbc';
- select * from article@hsodbc;

In case of case sensitive usernames and passwords of the foreign database
(for example Sybase, SQL Server, Informix...), the username and password
must be surrounded by double quotes like:
create database link connect to "Hsodbc" identified by "hsodbc" using 'hsodbc';

[ahmadbilal]

How to setup a database link between a Microsoft MDB file and the
Oracle Database using Generic Connectivity for ODBC
-----------------------------------------------------------------

1) With the OUI (Oracle Universal Installer) install
Generic Connectivity using ODBC. This product is part of the server
installation, because a listener is needed.
A directory called HS is created.

2) The Generic Connectivity utility needs data dictionary tables in the
Oracle database. To check their existence, run a query on
i.e. SYS.HS_FDS_CLASS.
If it fails, run the caths.sql script located in ORACLE_HOME\RDBMS\ADMIN\
as user sys or internal.

3) Install the third party ODBC driver from Microsoft.

4) With ODBC Admin Utility configure a SYSTEM DATASOURCE.
i.e. specify as Data Source Name mstest and select as mdb file
the Northwind.mdb from Microsoft.

5) Now configure tnsnames.ora. This file is in ORACLE_HOME\NETWORK\ADMIN.
Add the following lines to the file:

hsodbc.de.oracle.com =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)
(HOST=<HOSTNAME>) |<- adjust hostname
(PORT=1521)) |<- adjust port
(CONNECT_DATA=(SID=hsodbc))
(HS=OK)
)

Please make sure the HOST and PORT entries refer to the ORACLE listener
which contains the HSODBC SID entry. It MUST NOT refer to the foreign database
host and port.
Note, a sample file is located in ORACLE_HOME\HS\ADMIN.

6) Configuring listener.ora:
This file is in ORACLE_HOME\NETWORK\ADMIN.
Add the following line to the SID_List of the listener.ora and restart the
listener afterwards. ( After the restart a service handler for hsodbc should
exist).

(SID_DESC=
(SID_NAME=hsodbc)
(ORACLE_HOME=e:\oracle) |<- adjust Oracle_Home directory
(PROGRAM=hsodbc)
)

Note, a sample file is located in ORACLE_HOME\HS\ADMIN.

7) Adjust the configuration file of the gateway. It is located in
ORACLE_HOME\HS\ADMIN. The name depends on the SID you use for the
Heterogeneous Service.
In our sample the listener SID is hsodbc and so the configuration file
is inithsodbc.ora.

# This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent.
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = mstest |<- ODBC Data Source Name configured in step 4
HS_FDS_TRACE_LEVEL = off |<- trace levels are from off to debug



Testing the connectivity between Oracle database and the mdb file:
- create a datbase link:
create database link hsodbc using 'hsodbc';
- select * from article@hsodbc;

In case of case sensitive usernames and passwords of the foreign database
(for example Sybase, SQL Server, Informix...), the username and password
must be surrounded by double quotes like:
create database link connect to "Hsodbc" identified by "hsodbc" using 'hsodbc';