Dear All,
I'm trying to use MS Access .mdb file as source to my OWB application. I need to connect to the ms access .mdb file first using odbc. Here are the step I do:
1. Define ODBC connectifity
control panel -> admin tools -> data sources -> System DSN -> Add
Choose Microsoft Access Driver (*.mdb) -> Finish
Data source name: msaccess -> Database: Select -> .mdb file location (C:\MSAccess\att2000..mdb)
2. Configure Listener
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:C:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
(SID_DESC =
(SID_NAME = msaccess
(ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)
(PROGRAM = hsodbc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(SID_DESC =
(SID_NAME = msaccess
(ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)
(PROGRAM = hsodbc)
)
)
ADR_BASE_LISTENER = C:\app\Administrator
3. Configure Oracle HS
File name initmsaccess.ora
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = msaccess
HS_FDS_TRACE_LEVEL = 1
#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>
4. Edit tnsnames.ora
msaccess=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SID=msaccess)
(SERVICE_NAME=msaccess)
)
(HS=OK)
)
5. create database link
create database link msaccess using 'msaccess';
6. Run a query to test connection
SQL> select * from departments@msaccess;
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified
I check the tns using tnsping msaccess, it return
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhos
t)(PORT = 1521)) (CONNECT_DATA = (SID=msaccess) (SERVICE_NAME=msaccess)) (HS=OK))
OK (120 msec)
Any body know how to solve this?
Best Regards
Akhmad H Gumas