JDBC Sender for MS Access (how to?)

[Former Member]

Hi experts,

I would like to read a MS Access DB allocated in an external server, but I am still a bit confused after reading many threads and blogs about this issue.

Most of the people says no driver installation is needed when PI is running on Windows (my case).

I also use to see the following configuration of the CC connecting parameters:

JDBC Driver: jdbc.odbc.JdbcOdbcDriver

Connection: jdbc:odbc:Driver={Microsoft Access Driver (.mdb)};DBQ=//xiserver*/xitemp/db/TestDB.mdb;Uid=yourid;Pwd=yourpwd;

Does it mean it is mandatory the DB file to be in the PI server or is this only for test's purpose? How to update the DB then?

Is it possible to connect to an Access DB in a server different from the PI server as well? i.e. ...DBQ=//<server IP>/path

Many thanks in advance,

Paco

[Former Member]

You dont need to install a specific driver for MS Access because it uses the JDBC-ODBC Bridge driver which I believe is standard as part of the JRE. For other external databases, for performance reasons or additional technical features you will want to use a type 3 or 4 driver. Explainations of the driver types are in this link.

http://www.javaworld.com/javaworld/jw-07-2000/jw-0707-jdbc.html

For connecting to the database you do use the connection details:

JDBC Driver: sun.jdbc.odbc.JdbcOdbcDriver

Connection: jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=
<server path visible to XI>\TestDB.mdb;Uid=yourid;Pwd=yourpwd;

You can host the database on any server XI has access to and to update it you would use Native SQL or XML based SQL updates as described in the SAP help.

http://help.sap.com/saphelp_nw04/helpdata/en/0f/80243b4a66ae0ce10000000a11402f/frameset.htm

Hope this helps... If so... Give points...

Pete

[Former Member]

Thanks Pete,

Your answer has helped me to understand this issue better.

Kind Regards,

Paco

[prasad_ulagappan2]

For an FAQ on the JDBC adapter, see SAP Note 831162, which would give you much more details abt JDBC drivers.

[Former Member]

Hi,

>>Does it mean it is mandatory the DB file to be in the PI server or is this only for test's purpose? How to update the DB then?

It doesn't mean that the file shld be there in XI Sever or some where else.

inorder to communicate Xi with MS Access DB by using the JDBC Adapter , Xi requires Drivers and the connection parameters.

So thats why we need to install the JDBC Drivers on XI Server Before Connecting with Ms Access

Regards

Seshagiri

[Former Member]

Hi Seshagiri,

> It doesn't mean that the file shld be there in XI Sever or some where else.

>

So, if I understood, then tha fact of creating a blank database and saving the mdb file in the XI server is just to be able to test the interface using a demo. Right?

> inorder to communicate Xi with MS Access DB by using the JDBC Adapter , Xi requires Drivers and the connection parameters.

>

Wich *.jar do we need to install for MS Access, ojdbc14?

Thanks,

Paco

[Former Member]

Hi,

To install JDBC driver follow the how to guide.

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.km.cm.docs/library/xi/xi-how-to-guide...

Regards

Seshagiri

[Former Member]

Hi Carlos,

We are finishing PI installation so I cannot perform real tests so far. I was retrieving some information in order to know the possibility of connecting to MS Access DB and to get on with the developments.

Thanks,

Paco

[Former Member]

Hi Francisco,

Always you want to access to a external DB from XI by using JDBC sender or Receiver adapter,

you will need to deploy the proper drivers in XI system.

I have never try to do it with Access DB, so I don't know if you need to deploy a

specific driver if your XI runs under Windows server.

Did you try to configure your JDBC adapter and try to update/ select any record of the DB access?

Configure your JDBC adapter and post the errors if any of that are displayed.

Carlos