Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Upload Data from MS-Access to SAP?

Go to solution
Former Member

‎09-22-2005 11:04 AM

0 Kudos

Dear Experts,

Can you please tell me if there is a way to upload data from <u><b>MS Access file(.MDB) into itab</b></u> ?

<b>Upload MS ACCESS to SAP R/3</b>

Cheers Ranjan MR

1 ACCEPTED SOLUTION

Go to solution
sapkor
Explorer

‎03-02-2006 3:10 AM

0 Kudos

&----

*& Report ZYSH01 *

*& Developer : Hong young sun (sapkor@yahoo.co.kr *

*& Create Date : 2006.02.27 *

*& SAP R/3 Version : 4.6C *

&----

REPORT ZYSH01.

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(10) ,

F2 TYPE I,

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:\db2.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 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 OTHERS.

I1-F2 = 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.

ENDLOOP.

  • connetion close & destroy

FREE OBJECT con.

FREE OBJECT rec.

26 REPLIES 26

Go to solution
Former Member

‎09-22-2005 11:52 AM

0 Kudos

Hi Ranjan,

I think its supported only one way. You cannot upload.

Check OSS 443027 . Only two scenarios are supported for upload and that too they have specific exe files.

Cheers.

Sanjay

Go to solution
pedro_restrepo
Explorer

‎09-23-2005 3:10 AM

0 Kudos

Hi,

What a rare point !!!

I guess you can invoke MSAccess from an ABAP program using OLE techniques.

In this case, ABAP program will act as client application and MSAccess will act as server application.

I guess this is the method used by SAP programs when they invoke MSExcel from SAP R/3, but I had not verified it.

Exploring help documents from both sides (ABAP and MSAccess) you can test this solution.

Good luck....

(And please tell me if this suggestion works)

Go to solution
sapkor
Explorer

‎03-02-2006 3:10 AM

0 Kudos

&----

*& Report ZYSH01 *

*& Developer : Hong young sun (sapkor@yahoo.co.kr *

*& Create Date : 2006.02.27 *

*& SAP R/3 Version : 4.6C *

&----

REPORT ZYSH01.

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(10) ,

F2 TYPE I,

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:\db2.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 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 OTHERS.

I1-F2 = 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.

ENDLOOP.

  • connetion close & destroy

FREE OBJECT con.

FREE OBJECT rec.

Go to solution
Former Member
In response to sapkor

‎03-09-2006 4:45 PM

0 Kudos

Dear young sun hong ,

Thanks a Lot.

I will try this logic. Thanks a lot.

Have one simple request, can you suggest some some help files, where I can read and debug these classes and Objects.

Thanks a lot once Again.

Cheers RMR

Go to solution
Former Member
In response to sapkor

‎04-03-2006 3:57 PM

0 Kudos

Dear Sir,

Waiting for your response.

Please help.

Go to solution
Former Member
In response to sapkor

‎04-03-2006 4:17 PM

0 Kudos

I would suggest to implement an ABAP-RFC-FM that can be called from your MS Access DB (via VBA) and is able to receive the data you want to upload to your SAP-system.

Go to solution
Former Member
In response to sapkor

‎04-03-2006 4:52 PM

0 Kudos

Hello MIKE,

Thanks for responding. I would like to know how should i start with VBA coding.

Sorry for my ignorance. I dont know what is VBA coding or RFC programming ?

Go to solution
Former Member
In response to sapkor

‎04-04-2006 7:46 AM

0 Kudos

Hello RMR,

VBA means 'VisualBasic for Applications'. It is a VisualBasic derivate, delivered with all MS Office applications like Word, Excel, ... It can be used to implement your own functions within an existing Excel-Sheet for example. I have done a lot of VBA in former times, but that a long time ago. Please check the web for further information.

RFC means 'Remote Function Call'. It is used for SAP and/or ABAP applications to communicate with each other over the network. I.e. it is possible for system a to call the RFC-enabled function module RFC_READ_REPORT within system b to retreieve the coding of a given ABAP report.

Hope that helps.

--MIKE

Go to solution
Former Member
In response to sapkor

‎04-09-2006 7:17 AM

0 Kudos

Thanks MIKE, I really Appreciate.

I have a question for you.

From Java , I am using RFC_READ_TABLE to read an SAP Database table. Before extracting the data from database Table I need to LOCK and then extract. And then after extract I need to UNLOCK.

If I am in SAP , I know I can use ENQUE and DEQUE, but these FM are "NOT" Remote enabled and hence I cannot call from JAVA.

Therefore my question is - Are there any REMOTE ENABLED function Module for LOCK AND UNLOCK database Tables, that I can call from JAVA using RFC?

Go to solution
Former Member
In response to sapkor

‎04-09-2006 7:33 AM

0 Kudos

RMR,

I don't think for the purposing of reading the data, you need to lock the table, unless you eventually will be updating the same.

In that case, I would suggest a wrapper which would lock the table and then read the data. Also, while updating the data, you need to update and then unlock the table.

Regards,

Ravi

Note : Please mark the helpful answers

Go to solution
Former Member
In response to sapkor

‎04-09-2006 8:05 AM

0 Kudos

Hello Ravi,

How can I wrap that ?

Go to solution
Former Member
In response to sapkor

‎04-09-2006 8:15 AM

0 Kudos

RMR,

Write a function module, with whatever data you are passing or receiving, and call the locking function and the other function module inside that. Make that remote enabled, in the attributes of the function and you should be able to call that from outside the system.

Regards,

Ravi

Note : Please mark the helpful answer

Go to solution
Former Member
In response to sapkor

‎04-09-2006 8:27 AM

0 Kudos

Hello Ravi,

Yes I completely agree with your point-of-view. But the requirement is that <b>"I am " </b>not supposed to develop any Custom codes in SAP because,<b><i> the client is not in favour of allowing any custom developments in their SAP system. </i>

</b>

<b><i>Hence, what ever I do must be outside of SAP [ ie in JAVA ]. </i></b>

With this Contraint in mind I asked , are there any SAP RFC enabled FM in SAP for LOCKING and UNLOCKING , so that I can use it from JAVA?

Thanks.

<i><b><u>I really appreciate your help </u></b></i>

Go to solution
Former Member
In response to sapkor

‎04-09-2006 8:34 AM

0 Kudos

RMR,

Well, I guess you are only reading the data from the table, right? Then why do you want to lock it. Even SAP standard functions doesn't lock the table while reading the data.

Please mark the helpful answers, if they are helping you - Way of appreciating the help.

Regards,

Ravi

Go to solution
Former Member
In response to sapkor

‎04-09-2006 8:58 AM

0 Kudos

Dear Ravi,

Indeed a very good question. The reason why we need to Lock "<b>eventhough its JUST a READ</b>" is because for these reasons .....

1 ). The <b>RFC_READ_TABLE</b> has a restriction of <b>512 characters per read</b>. And lets say I am reading MARA table from SAP which have <b>more than 120 Fields</b>. <u><b><i>This means at ONE CALL it may not be possible to get entire fields at all which would fit within 512 characters.</i></b></u> Definately it will overflow.

Therefore, I need to read the same table in such a way that i need to work with the restriction of 512 characters.

2 ). Lets say, we have read <u><b>[ 60/120 fields ] for the first time</b></u> and <b>60 more to go</b>. Then it <u><b>between the first read CALL and the second read CALL, MARA could be UPDATED in SAP</b>.</u>

3 ). Well, in SAP <b>you do not face </b>this situvation [ both 1 and 2 ] is because you can construct an ITAB <b>LIKE</b> MARA and when you use [select * from MARA ] , it will read the entire fields which do not have a restriction of 512 characters for the ITAB.

<u>

<b>TO AVOID PROBLEM (1) and (2), we have to use LOCK before READ and UNLOCK after READ.</b></u>

Hope this helps.

Go to solution
Former Member
In response to sapkor

‎04-09-2006 9:09 AM

0 Kudos

RMR,

Is your requirement deal with reading different tables dynamically?

If you are dealing with a material, then you probably can look at calling the MATERIAL BAPI, which will give you all the details of the given material in one go and there will not be any restriction on the amount of data as well.

Look at the functions.

BAPI_MATERIAL_GET_DETAIL - Gives basic details of the Material.

BAPI_MATERIAL_GETALL

BAPI_MATERIAL_GET_ALL

regards,

Ravi

Note : Please mark the helpful answers

Go to solution
Former Member
In response to sapkor

‎04-13-2006 4:51 PM

0 Kudos

Hi Ravi,

Is your requirement deal with reading different tables dynamically?

YES - EXACTLY ... this is the requirement.

Cheers

Go to solution
Former Member
In response to sapkor

‎01-22-2007 1:13 PM

0 Kudos

Hi

We tried using the same program for our requirement

but the following statement is giving sy-subrc 2

*******

SQL = 'insert into [test] values('.CONCATENATE SQL '''A''' ',' '''1''' '

CALL METHOD OF REC 'Open'

EXPORTING #1 = SQL

#2 = CON

#3 = '1'.

******

Can u pls help in this regard

Regards

MD

Go to solution
Former Member
In response to sapkor

‎02-04-2007 5:52 AM

0 Kudos

Please Check the SQL Insert statement in MS-Access to verify if the syntax is correct.

Do send me your email id, by which i will send you the screenshots.

Go to solution
ferry_lianto
Active Contributor

‎03-09-2006 5:03 PM

0 Kudos

Hi RMR,

Please look at the following FM.

MSACCESS_STRUCT_EXPORT_RFC

POPUP_FOR_DEST_AND_DBNAME

PS_MATERIAL_RUE_VIA_ACCESS

PS_MATMELDUNGEN_MSACCESS

PS_ZEITRUECKMELDUNGEN_MSACCESS

STRUCTURE_EXPORT_TO_MSACCESS

TABLE_CONVERT_FOR_MSACCESS

TABLE_EXPORT_TO_MSACCESS

TABLE_EXPORT_TO_MSACCESS_RFC

TABLE_EXPORT_TO_MSACCESS_RFCFE

Hope this will help.

Regards,

Ferry Lianto

Go to solution
Former Member

‎03-15-2006 12:19 PM

0 Kudos

hi,

actually we were facing the problem during the connectivity of SAP with MS ACCESS.

the problem mainly seems to be with the connection becoz when we try to debug the program..the sy-subrc value is becoming 2 which means "<b>Method CALL resulted in an error</b>".

and the code is...

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(10) ,

F2 TYPE I,

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:\db2.mdb''' ';'

INTO SQL.

CONCATENATE SQL 'Mode=' '''Share Deny None'''

INTO SQL.

  • MDB connection ...

CALL METHOD OF CON 'Open'

EXPORTING #1 = SQL.

*****

added code

*#2 = CON

*#3 = '1'.

**********

  • 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 OTHERS.

I1-F2 = 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.

ENDLOOP.

  • connetion close & destroy

FREE OBJECT con.

FREE OBJECT rec.

so can any one help us in this regard ASAP

Thanking you,

Regards,

bindu.

Go to solution
Former Member
In response to Former Member

‎04-03-2006 3:54 PM

0 Kudos

Hi,

Can you please help me if you have solved this problem.

Thanks and Regards,

Ranjan MR

Go to solution
Former Member

‎04-09-2006 7:46 AM

0 Kudos

Hi,

Easiest solution will be to convert you MS Access file to MS-Excel.

Both MS-access and MS-excel are from the MS-office suite, conversion from access to excel is very easy.

Once you get your access data in excel format, you have plenty of options to upload data in SAP.

This simple conversion from access to excel will help you avoiding many problems like RFC,setting the remote connections etc.

Hope, this helps you.

Regards,

Tarun

Go to solution
Former Member

‎03-03-2008 10:46 AM

0 Kudos

Thanks to young sun hong . His program worked.

Thanks again for all who read and answered this question.

Go to solution
Former Member
In response to Former Member

‎06-02-2010 4:56 AM

0 Kudos

I tried to use those codes in my program which the database locate at another server in my LAN, for example

'Data Source=' '''\\10.0.4.111\...'

Unfortunately, it did not work. It can not establish the connection, and the return value of sy-subrc is 2

CALL METHOD OF CON 'Open'
 EXPORTING #1 = SQL.
--> SY-SUBRC = 2

Please help me solve this problem!

Thanks in advanced.

Go to solution
Former Member

‎09-20-2010 10:10 PM

0 Kudos

Hi Ranjan,

I am facing same problem.Please let me know how you have achieved this?

Thanks & Regards