Search on this Website

Thursday, May 31, 2007

Get Data From MS Access Into Internal Table

*———————————————————————*
* Report Name : YGECICI
*———————————————————————*
* Author : Amit Khari
* Location :ABAPcode site
* Date / Time : 05 may 2007
* Subject : Upload Data From MS Accsess Into an Internal Table
*———————————————————————*
REPORT YGECICI MESSAGE-ID 00
No Standard Page Heading
Line-Size 200
Line-Count 65.

INCLUDE Ole2incl.

DATA: Con TYPE Ole2_Object,
Rec TYPE Ole2_Object.

DATA SQL(1023).

DATA: BEGIN OF SPL OCCURS 0,
VAL(1023),
END OF SPL.

DATA: BEGIN OF I1 OCCURS 0,
F1 TYPE I ,
F2(10),
F3(10),
END OF I1.

IF Con-Header IS INITIAL OR Con-Handle = -1.
CREATE OBJECT Con ‘ADODB.Connection’.
IF NOT Sy-Subrc = 0.
EXIT.
ENDIF.

CREATE OBJECT REC ‘ADODB.Recordset’.
IF NOT Sy-Subrc = 0.
EXIT.
ENDIF.

ENDIF.

* MDB Connetion Infomations ….
CONCATENATE ‘Provider=’ ”’Microsoft.Jet.OLEDB.4.0”’ ‘;’
INTO SQL.
CONCATENATE SQL ‘Password=’ ””” ‘;’
INTO SQL.
CONCATENATE SQL ‘User ID=’ ”’Admin”’ ‘;’
INTO SQL.
CONCATENATE SQL ‘Data Source=’ ”’C:\Mm.Mdb”’ ‘;’
INTO SQL.
CONCATENATE SQL ‘Mode=’ ”’Share Deny None”’
INTO SQL.

* MDB Connection …
CALL METHOD OF CON ‘Open’
EXPORTING #1 = SQL.
*

* Query (Insert) Statement …
SQL = ‘Insert Into [Test] Values(’.
CONCATENATE SQL ”’A”’ ‘,’ ”’1”’ ‘)’ INTO SQL.

* Query Run …
CALL METHOD OF REC ‘Open’
EXPORTING #1 = SQL
#2 = CON
#3 = ‘1′.

* Query (Select) Statement …
SQL = ‘Select * From[Test]’.

* Query Run …
CALL METHOD OF REC ‘Open’
EXPORTING #1 = SQL
#2 = CON
#3 = ‘1′.

* Selecting MDB Record Into SAP Internal Table …
DO.
CALL METHOD OF REC ‘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 SPL. CLEAR SPL.
SPLIT SQL AT ‘|’ INTO TABLE SPL.
LOOP AT SPL.
CASE SY-TABIX.
WHEN 1.
I1-F1 = SPL-VAL.
When 2.
I1-F2 = SPL-VAL.
WHEN OTHERS.
I1-F3 = SPL-VAL.
ENDCASE.
ENDLOOP.
APPEND I1. CLEAR I1.
ELSE.
EXIT.
ENDIF.

ENDDO.

* Result Writing …
LOOP AT I1.
WRITE: AT /1(10) I1-F1,
AT (10) I1-F2, AT (20) I1-F3.
ENDLOOP.

* Connetion Close & Destroy
FREE OBJECT Con.
FREE OBJECT Rec.

No comments: