How do I access an MS Access database from Oracle?

Question

Configured access from Oracle to MS Access.

Oracle XE 11.2.0.2.

Microsoft Access 2010

There are two INSERTS (insert into table that is residing in MS ACCESS db) operations. One of them is using pl\sql variables, another one is not.

First insert:

insert into SomeTable@MSADB("ID", "Name")
  values(l_ID, l_Name);

Second one:

insert into SomeTable@MSADB("ID", "Name")
  values(123, 'SomeName');

In a case of second insert, all is fine. But first one gives an error "HYC00 NativeError=106 optional feature not implemented". I just can't understand why this is happening and how to solve this problem.

Bind variables(execute immediate) didn't solve this problem. DBMS_HS_PASSTHROUGH.BIND_VARIABLE didn't solve this problem either.

Only solution I see is embedding those variables into a string and then execute it. Are there another solutions to force the first insert to work?

Thank you.

Answer 1

There was a known issue in the Oracle 11g client with the ODBC driver connecting to excel workbooks and it was giving the same error. Oracle released Patch 7009291 to address that issue so you might take a look at that patch to see if it will address the access issue as well.

You can also enable DG4ODBC tracing to see what is going on and identify if this is a similar issue to the excel one. Tracing is enabled by setting in your init.ora file HS_FDS_TRACE_LEBVEL=255 The trace file is then found in your \hs\trace