In case you are facing issue connecting MS Access with SAP PI, this document might be useful to you. This provides a step by step approach to resolve "driver not found" issue and specially written for PI users using Windows 7 (64 bits system). But for others also, this may have some information which will guide to reach to the actual root cause.
Below is the error message:
Error when attempting to get processing resources: com.sap.aii.af.lib.util.concurrent.ResourcePoolException: Unable to create new pooled resource: DriverManagerException: Cannot establish connection with the registered driver. sun.jdbc.odbc.JdbcOdbcDriver returns: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified. : SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
First thought came to our mind when we started connecting SAP PI with MS Access, that we don’t need any driver installation as our operating system was windows and it bundles ODBC driver along with it. But, when I configured my communication channel(CC), I realized that my CC was not able to identify registered driver and some driver had to be installed. To verify it I checked my odbc administrator from Control panelà administrative tools-à ODBC and under driver tab I could see only two sql driver. So it made it clear that driver is missing.
While searching for this issue on internet I read through several blogs and forum and it was clear to me that if your Java version in 32 bits then your ODBC administrator would be under C:\Windows\SysWOW64 dir and I checked this dir and I found drivers were installed there but when I checked java version called by my application carefully I found that is 64 bits jdk. So that mean my application is calling ODBC located at C:\Windows\System32.
Now in case you are facing this error please follow below steps-
- Check your OS version- In case it is 64 bits that means you have two ODBC driver present
One under – 32 bits ODBC is at C:\Windows\SysWOW64
Another under – 64 bits ODBC is at C:\Windows\System32
- Check your JDK version. In case you have 32 as well as 64 bits both, please ensure which one is used by your Sap PI
- In case you have SAP pi using 32 bits java version then CC will pick up ODBC driver reference from C:\Windows\SysWOW64
- If you are using 64 bits jdk then your Communication channel is looking for ODBC driver at C:\Windows\System32
- In my case under C:\Windows\SysWOW64 dir when I opened odbcad32.exe, I found all the drivers were already present.
- But as my JDK was not 32 bits so it was not taking driver refernce from this location.instead it was looking at C:\Windows\Sy stem32 and there my ODBC administrator was not showing any access driver
- We have installed AccessDatabaseEngine_x64.exe from http://www.microsoft.com/en-us/download/details.aspx?id=13255
- After installation Access driver was visible under ODBC administrator
- I had configured my communication channel as per the blog by sameer-
sameer.shadab/blog/2005/10/24/connecting-to-ms-access-using-receiver-jdbc-adapter-without-dsn
My configuration was as follow-
JDBC driver : sun.jdbc.odbc.JdbcOdbcDriver
connection : jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ= path\db.mdb
- With above configuration I was still getting some error and then I changed my DBQ path to absolute path which was earlier pointing to mapped drive.
- But error was still there and the final step, which resolved the error, was changing the connection string as below. For 64 bits connection it should have ‘*.accdb ‘ as well.
jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=
to
jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=
References-
http://social.msdn.microsoft.com/Forums/is/adodotnetdataproviders/thread/4112c367-a196-4f26-bb55-009...
http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/ef10d625-80d9-4666-b33e-...
- SAP Managed Tags:
1 Comment
