ABAP - Insert a record into MS Access table using OLE

[rubenswalter_blattler]

Hello, Gurus!

I have to develop a shop floor interface using MS ACCESS and ABAP (OLE).

I've created a file called "shop_floor.mdb" to exemplify my problem.

This database has a table called "USUARIOS". The table "USUARIOS" contains 3 fields: "USER_MAIL", "USER_NAME" and "USER_PASS".

I've developed an ABAP program to read the records and also to insert a record in that table (MS Access).

I've not had problems to read the records from the table USUARIOS, however I'm not able to insert a record into MS Access table.

Even I did a source (MACRO) in Access to simulate the record insertion - the test was successful! See below:

Sub TESTE()
Const adOpenStatic = 3
Const adLockOptimistic = 3


Dim v_sql As String


Set o_conn = CreateObject("ADODB.Connection")
Set o_rsdb = CreateObject("ADODB.Recordset")


o_conn.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = E:\pruefung_ms_access\shop_floor.mdb"


v_sql = "INSERT INTO USUARIOS (USER_MAIL, USER_NAME, USER_PASS)" & _
        "VALUES ('FULANO@EMAIL.COM', 'FULANO CICLANO DA SILVA', '123456')"
               
o_rsdb.Open v_sql, o_conn, adOpenStatic, adLockOptimistic
        
End Sub

My ABAP source code follows below:

report  zblaettler.

* Objects declaration
type-pools ole2.

data: o_conn type ole2_object,
      o_rsdb type ole2_object,

      v_sql  type c length 1024,
      v_temp type string,

      begin of wa_usuarios,
        email type text100,
        nome  type text100,
        senha type text100,
      end of wa_usuarios,

      t_usuarios like table of wa_usuarios,
      t_temp     like standard table of v_temp.

* Event start-of-selection
start-of-selection.

break-point.

* Create objects
  create object o_conn 'ADODB.Connection'.
  create object o_rsdb 'ADODB.Recordset'.

* DB connection
  concatenate 'Provider=Microsoft.Jet.OLEDB.4.0;'
              'Data Source=E:\pruefung_ms_access\shop_floor.mdb;'
              into v_sql.

  call method of o_conn 'Open' exporting #1 = v_sql.

* Query statement
  v_sql = 'SELECT * FROM USUARIOS'.

* Run statement (SELECT)
  call method of o_rsdb 'OPEN' exporting #1     = v_sql
                                         #2     = o_conn
                                         #3     = '1'.

* Save records from MS ACCES TABLE (USUARIOS) INTO INTERNAL TABLE T_USUARIOS
  do.
    call method of o_rsdb 'getstring' = v_sql
      exporting
        #1          = '2'
        #2          = 1
        #3          = '|'
        #4          = '|'.

    if sy-subrc is initial.
      clear: t_temp[], v_temp, wa_usuarios.
      split v_sql at '|' into table t_temp.
      loop at t_temp into v_temp.
        case sy-tabix.
          when 1.
            wa_usuarios-email = v_temp.
          when 2.
            wa_usuarios-nome  = v_temp.
          when others.
            wa_usuarios-senha = v_temp.
        endcase.
      endloop.
      append wa_usuarios to t_usuarios.
    else.
      exit.
    endif.
  enddo.

* Everithing is OK at this point. The internal table T_USUARIOS has the records from Access table

break-point.

* Query statement (INSERT) - attemp 1 (NOT OK)
*  concatenate 'INSERT INTO USUARIOS (USER_MAIL, USER_NAME, USER_PASS) VALUES ('
*              '''' 'FULANO@EMAIL.COM' '''' ',' '''' 'FULANO CICLANO DA SILVA' '''' ',' '''' '123456' '''' ')'
*              into v_sql.

* Query statement (INSERT) - attemp 2 (NOT OK)
* v_sql = 'INSERT INTO USUARIOS ("USER_MAIL","USER_NAME","USER_PASS") VALUES ("FULANO@EMAIL.COM","FULANO CICLANO DA SILVA","123456")'.

* Query statement (INSERT) - attemp 3 (NOT OK)
 v_sql = 'INSERT INTO USUARIOS VALUES ("FULANO@EMAIL.COM","FULANO CICLANO DA SILVA","123456")'.

* Run statement (INSERT)
  call method of o_rsdb 'OPEN' exporting #1     = v_sql
                                         #2     = o_conn
                                         #3     = '3'
                                         #4     = '3'.
* SY-SUBRC = 2 at this point

break-point.

end-of-selection.

Do you know what might be wrong with the "INSERT" statement?

Thank you very much!

Rubens W. Blättler

[raymond_giuseppi]

You pass the sql command to open method, could you try

" Either, execute a SQL statement
CALL METHOD of o_conn 'Execute' exporting #1 = v_sql
" OR (excl.) or create new record, change fields value, update
CALL METHOD of o_rsdb 'AddNew' " ...
CALL METHOD of o_rsdb 'Fields' " ...
CALL METHOD of o_rsdb 'Update' " ...

[rubenswalter_blattler]

Thanks for the answer, Raymond, but I've ever try this. It doesn't work in ABAP.