Reading MS Access database table from Application Server into ABAP Program

[Former Member]

Hi,

I have a requirement wherein I have to access MS Access database from Application Server and fetch the table records into ABAP program. For this purpose I am using the below logic but my program is not fetching the data from application server. The same logic works fine if my file is in Presentation Server. I guess there is some problem in this part of code: 'Data Source=//192.168.0.70/usr/sap/tmp/db1.mdb;'. Can someone suggest me where I am going wrong and what can we do for that to get it rectified.

*&---------------------------------------------------------------------*
*& Report  Z_DATA_FROM_MSACCESS
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

REPORT  Z_DATA_FROM_MSACCESS.

TYPE-POOLS OLE2.

DATA: CONN      TYPE OLE2_OBJECT,
      RSDB      TYPE OLE2_OBJECT,
      SQL        TYPE C LENGTH 1024.

DATA: BEGIN OF WA_TEMP,
       VALUE TYPE C LENGTH 1024,
      END OF WA_TEMP.

DATA : IT_TEMP LIKE STANDARD TABLE OF WA_TEMP.

DATA: BEGIN OF WA_PA0001,
            field1 TYPE char10 ,
            field2 TYPE char10,
            field3 type char10,
          END OF WA_PA0001.

DATA : IT_PA0001 LIKE TABLE OF WA_PA0001.


*CREATE ADODB OBJECT:
START-OF-SELECTION.

  CREATE OBJECT CONN 'ADODB.Connection'.
  CREATE OBJECT RSDB 'ADODB.Recordset'.

* MDB Connetion infomations
*&*& PATH OF MS-ACCESS DB FILE.

  CONCATENATE 'Provider=Microsoft.Jet.OLEDB.4.0;'
*  'Data Source=C:\db1.mdb;'
  'Data Source=//192.168.0.70/usr/sap/tmp/db1.mdb;'
  INTO SQL.

  CALL METHOD OF CONN 'Open' EXPORTING #1 = SQL.

* Query Statement ...
*&*& TABLE1 - IS THE TABLE NAME OF THE TABLE INSIDE DB1
  SQL = 'SELECT * FROM SMSInteg'.

* Run Query
  CALL METHOD OF RSDB 'OPEN'
    EXPORTING #1 = SQL
    #2 = CONN
    #3 = '1'.

*SAVE RECORDS INTO INTERNAL TABLE:
  DO.
    CALL METHOD OF RSDB 'getstring' = SQL
    EXPORTING #1 = '2' "Do not modify!
    #2 = 1    "Do not modify!
    #3 = '|'  "Do not modify!
    #4 = '|'. "Do not modify!

    IF SY-SUBRC EQ 0.
      REFRESH IT_TEMP.

      SPLIT SQL AT '|' INTO TABLE IT_TEMP.
      LOOP AT IT_TEMP INTO WA_TEMP.
        CASE SY-TABIX.
          WHEN 1.
            WA_PA0001-field1 = WA_TEMP-VALUE.
          WHEN 2.
            WA_PA0001-field2 = WA_TEMP-VALUE.
          WHEN 3.
            WA_PA0001-field3 = WA_TEMP-VALUE.
        ENDCASE.
      ENDLOOP.
      APPEND WA_PA0001 TO IT_PA0001.
      CLEAR WA_PA0001.
    ELSE.
      EXIT.
    ENDIF.

  ENDDO.

*DISPLAY DATA:
  ULINE AT (97).
  WRITE: / '|' NO-GAP, (010) 'Field1'   LEFT-JUSTIFIED,
           '|' NO-GAP, (030) 'Field2' LEFT-JUSTIFIED,
           '|' NO-GAP, (050) 'Field3' LEFT-JUSTIFIED,
           '|'.
  ULINE AT /(97).
  LOOP AT IT_PA0001 INTO WA_PA0001.
    WRITE: / '|' NO-GAP, (010) WA_PA0001-field1   LEFT-JUSTIFIED,
             '|' NO-GAP, (030) WA_PA0001-field2   LEFT-JUSTIFIED,
             '|' NO-GAP, (050) WA_PA0001-field3   LEFT-JUSTIFIED,
             '|'.
  ENDLOOP.
  ULINE AT /(97).

Regards,

Nitish.

Edited by: Nitish Cherukupally on Mar 2, 2009 1:26 PM

Edited by: Nitish Cherukupally on Mar 3, 2009 7:03 AM

[Former Member]

hi,

please refer to this link

http://learningabap.wordpress.com/2007/04/11/get-data-from-ms-access-into-internal-table/

http://abapcode.blogspot.com/2007/05/get-data-from-ms-access-into-internal.html

http://abapcode.blogspot.com/2007/06/sample-program-to-upload-excel-document.html

thanks

[Former Member]

I think you need to make ODBC connection on application server with your MS Access.

[Former Member]

but there's no type for MS Access Database?? so what i have to do?

[former_member222860]

Nitish,

When i check the utl_file location in my system,, it is like this:

:\usr\sap\tmp

Pl. re-check the datasource u've given

thanks|

Mahesh

Edited by: Mahesh Reddy on Mar 3, 2009 7:39 AM

[former_member38964]

Hi Nitish,

Do you get any solution on this please suggest.

Thanks & Regards,

Sweta Gohil.

[matt]

myuser From 12 years ago? Do you expect a useful answer? You might follow the answers below, but they'll probably be out of date. How about posting your own question?