Using JDBC to send data to MsAccess database

[Former Member]

Hi,

I have received a MSAccess database on a file. I have placed the database on a networkdrive, and need to write data to the tables in this database. The networkdrive is mapped like //Data/DB/Databse.mdb

How do I connect the jdbc adapter to this database? Can it be done, like you normally connect to a database system with servername and port?

Kind regards

Mikkel Íversen

[Former Member · Accepted solution]

Hi

Connecting to MS Access using receiver JDBC Adapter

/people/sameer.shadab/blog/2005/10/24/connecting-to-ms-access-using-receiver-jdbc-adapter-without-dsn

Thanks

[Former Member]

Hi All,

still same problem. I have the file on the C:\ of the XI server.

Driver: sun.jdbc.odbc.JdbcOdbcDriver

Path: jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=C://VDBLoad.mdb

I have tried all variations of the path, c:/, c:\ and c:
still same error. Is there a service or odbc thing that must be setup on the server the Access database is located on?

Kind regards

Mikkel

[Former Member]

Hi,

the driver should be installed., see the below link

http://help.sap.com/saphelp_nw04/helpdata/en/c7/641f44be9f1e4ebbe807cfbad61d44/content.htm

also check

connecting to MS access (Without DSN) Receiver JDBC scenario - /people/sameer.shadab/blog/2005/10/24/connecting-to-ms-access-using-receiver-jdbc-adapter-without-dsn

refer SAP note 941317

you can get official driver from microsoft site.

Chilla

[Former Member]

Hi Thanks for your replies.

I have done what you indicate, but get the following error:

Error during database connection to the database URL 'jdbc:odbc:Driver={Microsoft Access Driver (.mdb)};DBQ=//usr/sap/DX2/DVEBMGS01/work/VDBLoad.mdb' using the JDBC driver 'jdbc.odbc.JdbcOdbcDriver': 'com.sap.aii.adapter.jdbc.sql.DriverManagerException: Cannot establish connection to URL 'jdbc:odbc:Driver={Microsoft Access Driver (.mdb)};DBQ=//E://usr/sap/DX2/DVEBMGS01/work/VDBLoad.mdb': SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified'

Hope you can help

Kind regards

Mikkel

[Former Member]

'jdbc:odbc:Driver={Microsoft Access Driver (.mdb)};DBQ=//usr/sap/DX2/DVEBMGS01/work/VDBLoad.mdb' using the JDBC driver 'jdbc.odbc.JdbcOdbcDriver': 'com.sap.aii.adapter.jdbc.sql.DriverManagerException: Cannot establish connection to URL 'jdbc:odbc:Driver={Microsoft Access Driver (.mdb)};<b>DBQ=//E://usr/sap/DX2/DVEBMGS01/work/VDBLoad.mdb'</b>: SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified'

why "//" before and after E: ?????

[Former Member]

just paste your VDBLoad.mdb file in anydrive say....C:

DBQ=C:\VDBLoad.mdb

finallly ur location will be....

<b>jdbc:odbc:Driver={Microsoft Access Drive (*.mdb)};DBQ=C:\VDBLoad.mdb</b>

Thanks

Farooq.

[Former Member]

Also make sure there is no single quote ' or double " quote in the connection URL..

If it does not work then try

<b>DBQ=C:/VDBLoad.mdb </b> (as \ is illegal character in Java)

This Error is only possibe if the database url or connection parameter...is not wright..also if the path where data base is places if its not correct then also it pop up the same Erro.

Thanks

Farooq.

[Former Member]

Hi,

thanks for your replies. I have copied the database to the C root, and inserted the proposed path. Still same error.

Now I wonder, could it be the JDBC driver? The XI system, is running on a Windows platform, so it should be ok when accessing Access, as far as I know. But should we install as Access driver non the less?

Thanks

Mikkel

[prateek]

<i>But should we install as Access driver non the less?</i>

No drivers needs to be installed separately

Have u tried pasting ur database on the XI server and c if it works. Then the path would be //XIserver/folder/database.mdb

Regards,

Prateek

[Former Member]

Hi

For Access no driver need to be installed.I think it the problem of the location of the file.

Thanks

[Former Member]

Hi

In that case u have to provide the Connection parameter and the driver name.

Refer today thread : https://www.sdn.sap.com/irj/sdn/profile?userid=3596219

Enter the following parameter in the JDBC adapter..

JDBC Driver = "sun.jdbc.odbc.JdbcOdbcDriver"

Connection = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=
DataDBDatabse.mdb"

if there is authentication then enter :

uername & Password.

Also note your .mdb file should be on the same server where the XI is installed.

Thanks

Farooq.

*Rewards points if you find it useful*