2005 Sep 22 11:04 AM
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
2006 Mar 02 3:10 AM
&----
*& 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.
2005 Sep 22 11:52 AM
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
2005 Sep 23 3:10 AM
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)
2006 Mar 02 3:10 AM
&----
*& 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.
2006 Mar 09 4:45 PM
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
2006 Apr 03 3:57 PM
2006 Apr 03 4:17 PM
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.
2006 Apr 03 4:52 PM
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 ?
2006 Apr 04 7:46 AM
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
2006 Apr 09 7:17 AM
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?
2006 Apr 09 7:33 AM
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
2006 Apr 09 8:05 AM
2006 Apr 09 8:15 AM
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
2006 Apr 09 8:27 AM
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>
2006 Apr 09 8:34 AM
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
2006 Apr 09 8:58 AM
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.
2006 Apr 09 9:09 AM
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
2006 Apr 13 4:51 PM
Hi Ravi,
Is your requirement deal with reading different tables dynamically?
YES - EXACTLY ... this is the requirement.
Cheers
2007 Jan 22 1:13 PM
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
2007 Feb 04 5:52 AM
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.
2006 Mar 09 5:03 PM
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
2006 Mar 15 12:19 PM
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.
2006 Apr 03 3:54 PM
Hi,
Can you please help me if you have solved this problem.
Thanks and Regards,
Ranjan MR
2006 Apr 09 7:46 AM
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
2008 Mar 03 10:46 AM
Thanks to young sun hong . His program worked.
Thanks again for all who read and answered this question.
2010 Jun 02 4:56 AM
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.
2010 Sep 20 10:10 PM
Hi Ranjan,
I am facing same problem.Please let me know how you have achieved this?
Thanks & Regards