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?
Microsoft Access Database Connectivity
-
- Posts: 132
- Joined: Sun Apr 02, 2006 10:36 am
- Location: Pakistan
-
- Posts: 615
- Joined: Mon Sep 18, 2006 1:32 am
- Location: United Arab Emirates
- Contact:
<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';
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';
-
- Posts: 615
- Joined: Mon Sep 18, 2006 1:32 am
- Location: United Arab Emirates
- Contact:
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';
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';
Who is online
Users browsing this forum: No registered users and 5 guests