Reading MS Access table data from SAP

[Former Member]

Hi All,

I have one table in MS Access with 1 Milion records and I need to develop a report based on this access table data in SAP , but directly from MS Access, not using TXT File or Excel File. Someone can help me please ?

Thanks in Advance.

Regards,

sravan.

[Former Member]

TRY SOMTHING LIKE THIS

INCLUDE OLE2INCL.

DATA: APPLICATION TYPE OLE2_OBJECT,

CURRENDBD TYPE OLE2_OBJECT,

RECORDSET TYPE OLE2_OBJECT,

WORKSHEET TYPE OLE2_OBJECT,

EOF TYPE I.

DATA : BEGIN OF EMPLOYESS_OBJ,

EMPLOYEEID TYPE OLE2_OBJECT,

EMPLOYEESURNAME TYPE OLE2_OBJECT,

EMPLOYEEFIRSTNAME TYPE OLE2_OBJECT,

END OF EMPLOYESS_OBJ.

DATA : BEGIN OF EMPLOYESS,

EMPLOYEEID TYPE I,

EMPLOYEESURNAME TYPE CHAR100,

EMPLOYEEFIRSTNAME TYPE CHAR100,

END OF EMPLOYESS.

IF APPLICATION-HEADER = SPACE OR APPLICATION-HANDLE = -1.

CREATE OBJECT APPLICATION 'Access.Application'. "CREATE OBJECT MSACESS

ENDIF.

***OPEN MSACCESS

CALL METHOD OF APPLICATION 'OpenCurrentDatabase'

EXPORTING #1 = 'C:\TEMP\Timesheet.accdb'.

CALL METHOD OF APPLICATION 'CurrentDb' = CURRENDBD. "GET CURRENT DATABASE

******CREATE RECORDSET

CALL METHOD OF CURRENDBD 'OpenRecordset' = RECORDSET

EXPORTING #1 = 'SELECT * FROM Employees'."

• M_MESSAGE.

CALL METHOD OF RECORDSET 'MoveFirst'. "GO TO FIRST RECORD

***loop at records

DO.

GET PROPERTY OF RECORDSET 'EmployeeID'

= EMPLOYESS_OBJ-EMPLOYEEID. "create field id

GET PROPERTY OF RECORDSET 'EmployeeSurName'

= EMPLOYESS_OBJ-EMPLOYEESURNAME. "create field surname

GET PROPERTY OF RECORDSET 'EmployeeFirstName'

= EMPLOYESS_OBJ-EMPLOYEEFIRSTNAME."create field firs name

********get fields values

GET PROPERTY OF EMPLOYESS_OBJ-EMPLOYEEID 'Value'

= EMPLOYESS- EMPLOYEEID.

GET PROPERTY OF EMPLOYESS_OBJ-EMPLOYEESURNAME 'Value'

= EMPLOYESS-EMPLOYEESURNAME.

GET PROPERTY OF EMPLOYESS_OBJ-EMPLOYEEFIRSTNAME 'Value'

= EMPLOYESS-EMPLOYEEFIRSTNAME.

*****write values

WRITE:/ EMPLOYESS-EMPLOYEEID,

EMPLOYESS-EMPLOYEESURNAME,

EMPLOYESS-EMPLOYEEFIRSTNAME.

GET PROPERTY OF RECORDSET 'EOF' = EOF. "get END OF FILE FLAG OF THE RECORDSET

IF EOF <> 0. "CHECK IF IS THE LAST RECORD

EXIT. "EXIT OF LOOP

ENDIF.

CALL METHOD OF RECORDSET 'MoveNext'. "GO TO NEXT RECORD

ENDDO.

CALL METHOD OF APPLICATION 'CloseCurrentDatabase'. "CLOSE MSACCESS

[Former Member]

Hi,

Please check the simple program that show how to read data from Microsoft Access.

In this case we read data from MS Access Table: PA0001 with 2 field: PERNR and Name.

MS Access file is: Data.mdb and saved in drive C.

Type and Data declaration:

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,

IT_TEMP LIKE STANDARD TABLE OF WA_TEMP.

DATA: BEGIN OF WA_PA0001,

PERNR TYPE PERSNO ,

ENAME TYPE EMNAM,

END OF WA_PA0001,

IT_PA0001 LIKE TABLE OF WA_PA0001.

Create ADODB Object:

START-OF-SELECTION.

CREATE OBJECT CONN 'ADODB.Connection'.

CREATE OBJECT RSDB 'ADODB.Recordset'.

Set connection and run query:

• MDB Connetion infomations

CONCATENATE 'Provider=Microsoft.Jet.OLEDB.4.0;'

'Data Source=C:\Data.mdb;'

INTO SQL.

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

• Query Statement ...

SQL = 'SELECT * FROM PA0001'.

• 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-PERNR = WA_TEMP-VALUE.

WHEN OTHERS.

WA_PA0001-ENAME = WA_TEMP-VALUE.

ENDCASE.

ENDLOOP.

APPEND WA_PA0001 TO IT_PA0001.

CLEAR WA_PA0001.

ELSE.

EXIT.

ENDIF.

ENDDO.

Display data:

ULINE AT (55).

WRITE: / '|' NO-GAP, (010) 'N I K' LEFT-JUSTIFIED,

'|' NO-GAP, (040) 'N A M E' LEFT-JUSTIFIED,

'|'.

ULINE AT /(55).

LOOP AT IT_PA0001 INTO WA_PA0001.

WRITE: / '|' NO-GAP, (010) WA_PA0001-PERNR LEFT-JUSTIFIED,

'|' NO-GAP, (040) WA_PA0001-ENAME LEFT-JUSTIFIED,

'|'.

ENDLOOP.

ULINE AT /(55).

Regards

Pugazhenthi.P

Satyam computer Services Ltd

[Former Member]

Hi Pugal,

Thanks for your responce.

I want to read data directly from Acess tables not from the files.

cheers,

srak.

[Former Member]

TRY SOMTHING LIKE THIS

INCLUDE OLE2INCL.

DATA: APPLICATION TYPE OLE2_OBJECT,

CURRENDBD TYPE OLE2_OBJECT,

RECORDSET TYPE OLE2_OBJECT,

WORKSHEET TYPE OLE2_OBJECT,

EOF TYPE I.

DATA : BEGIN OF EMPLOYESS_OBJ,

EMPLOYEEID TYPE OLE2_OBJECT,

EMPLOYEESURNAME TYPE OLE2_OBJECT,

EMPLOYEEFIRSTNAME TYPE OLE2_OBJECT,

END OF EMPLOYESS_OBJ.

DATA : BEGIN OF EMPLOYESS,

EMPLOYEEID TYPE I,

EMPLOYEESURNAME TYPE CHAR100,

EMPLOYEEFIRSTNAME TYPE CHAR100,

END OF EMPLOYESS.

IF APPLICATION-HEADER = SPACE OR APPLICATION-HANDLE = -1.

CREATE OBJECT APPLICATION 'Access.Application'. "CREATE OBJECT MSACESS

ENDIF.

***OPEN MSACCESS

CALL METHOD OF APPLICATION 'OpenCurrentDatabase'

EXPORTING #1 = 'C:\TEMP\Timesheet.accdb'.

CALL METHOD OF APPLICATION 'CurrentDb' = CURRENDBD. "GET CURRENT DATABASE

******CREATE RECORDSET

CALL METHOD OF CURRENDBD 'OpenRecordset' = RECORDSET

EXPORTING #1 = 'SELECT * FROM Employees'."

• M_MESSAGE.

CALL METHOD OF RECORDSET 'MoveFirst'. "GO TO FIRST RECORD

***loop at records

DO.

GET PROPERTY OF RECORDSET 'EmployeeID'

= EMPLOYESS_OBJ-EMPLOYEEID. "create field id

GET PROPERTY OF RECORDSET 'EmployeeSurName'

= EMPLOYESS_OBJ-EMPLOYEESURNAME. "create field surname

GET PROPERTY OF RECORDSET 'EmployeeFirstName'

= EMPLOYESS_OBJ-EMPLOYEEFIRSTNAME."create field firs name

********get fields values

GET PROPERTY OF EMPLOYESS_OBJ-EMPLOYEEID 'Value'

= EMPLOYESS- EMPLOYEEID.

GET PROPERTY OF EMPLOYESS_OBJ-EMPLOYEESURNAME 'Value'

= EMPLOYESS-EMPLOYEESURNAME.

GET PROPERTY OF EMPLOYESS_OBJ-EMPLOYEEFIRSTNAME 'Value'

= EMPLOYESS-EMPLOYEEFIRSTNAME.

*****write values

WRITE:/ EMPLOYESS-EMPLOYEEID,

EMPLOYESS-EMPLOYEESURNAME,

EMPLOYESS-EMPLOYEEFIRSTNAME.

GET PROPERTY OF RECORDSET 'EOF' = EOF. "get END OF FILE FLAG OF THE RECORDSET

IF EOF <> 0. "CHECK IF IS THE LAST RECORD

EXIT. "EXIT OF LOOP

ENDIF.

CALL METHOD OF RECORDSET 'MoveNext'. "GO TO NEXT RECORD

ENDDO.

CALL METHOD OF APPLICATION 'CloseCurrentDatabase'. "CLOSE MSACCESS