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.

Can you log the calls from oracle? (I know you can't at the access end) If it is an ODBC link then you will probably be able to turn logging on. — Stephen Turner
– Stephen Turner, Commented Dec 7, 2011 at 9:32

Brian · Accepted Answer · 2011-12-07 14:26:19Z

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

answered Dec 7, 2011 at 14:26

Brian

2,25918 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How do I access an MS Access database from Oracle?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related