MS Access with SAP

[Timo van Esch]

Hi,

Currently we are trying to automate Variant manipulation in SAP through Access. Does anyone here have any knowledge about that?

The current script runs and works like this:
- SAP login through SAP references
- Use Access for Transactions and Variant names
- Input values in Variants by using the Macro Recorder in SAP
This last step I want to reprogram to pure VBA.

Function SAP(UserName as String, Password as String)
    Dim oBapiCtrl As Object
    Dim oBapiLogon As Object
    
    Set oBapiCtrl = CreateObject("sap.bapi.1")
    Set oBapiLogon = CreateObject("sap.logoncontrol.1")
    
    oBapiCtrl.Connection = oBapiLogon.NewConnection
    oBapiCtrl.Connection.ApplicationServer = xx
    oBapiCtrl.Connection.System = xx
    oBapiCtrl.Connection.Client = xx
    oBapiCtrl.Connection.User = UserName
    oBapiCtrl.Connection.Password = Password
    oBapiCtrl.Connection.Language = "EN"
    oBapiCtrl.Connection.SystemNumber = xx

    If oBapiCtrl.Connection.Logon(0, True) <> True Then
        MsgBox "not connected", vbInformation, "SAP Logon"
        Exit Function
    End If
         
    'Logged in to SAP
    If oBapiCtrl.Connection.IsConnected Then
    
        Dim db As DAO.Database
        Dim tblTr As DAO.Recordset
        Dim tblVr As DAO.Recordset
        Dim sTr As String
        Dim TrString As String
        Dim TrSAP As String
        Dim VarString As String
        Dim fldTr As Field
        Dim fldVr As Field
    
        Dim dtmDate As Date
        Dim FirstDate As String
        Dim LastDate As String
        
        'Open current database
        Set db = CurrentDb
        
        'Open Transaction Table
        Set tblTr = db.OpenRecordset("tbl_Transactions", dbOpenTable)
        tblTr.MoveFirst
        
        'Loop through Transactions
        If Not tblTr.BOF And Not tblTr.EOF Then
            Do Until tblTr.EOF
            With tblTr
            For Each fldTr In tblTr.Fields
                TrString = tblTr!Transactions
                TrSAP = tblTr!Fields
    
                'Open Variant tables, one by one
                Set tblVr = db.OpenRecordset("tbl_" & TrString, dbOpenTable)
                tblVr.MoveFirst
                
                'Adjust date to current month
                dtmDate = Date
                FirstDate = Format(DateSerial(Year(dtmDate), Month(dtmDate), 1), "dd.mm.yyyy")
                LastDate = Format(DateSerial(Year(dtmDate), Month(dtmDate) + 1, 0), "dd.mm.yyyy")
            
                If Not tblVr.BOF And Not tblVr.EOF Then
                    Do Until tblVr.EOF
                    With tblVr
                    For Each fldVr In tblVr.Fields
                        VarString = fldVr.Value
                
[QUOTE]       'Session.findById("wnd[0]/tbar[0]/okcd").Text = "/N" & TrString
                        'Session.findById("wnd[0]").sendVKey 0
                        'Session.findById("wnd[0]").Maximize
                        'Session.findById("wnd[0]").sendVKey 17
                        'Session.findById("wnd[1]/usr/txtV-LOW").Text = VarString
                        'Session.findById("wnd[1]/usr/txtV-LOW").caretPosition = 8
                        'Session.findById("wnd[1]").sendVKey 8
                        'Session.findById("wnd[0]/usr/ctxtR_" & TrSAP & "-LOW").Text = FirstDate
                        'Session.findById("wnd[0]/usr/ctxtR_" & TrSAP & "-HIGH").Text = LastDate
                        'Session.findById("wnd[0]/usr/ctxtP_DISVAR").SetFocus
                        'Session.findById("wnd[0]/usr/ctxtP_DISVAR").caretPosition = 5
                        'Session.findById("wnd[0]").sendVKey 11
                        'Session.findById("wnd[0]").sendVKey 11
                        'Session.findById("wnd[1]/usr/btnBUTTON_1").press
[/QUOTE]                    'Next Variant
                    Next fldVr
                    End With
                    tblVr.MoveNext
                    Loop
                    tblVr.Close
                    Set tblVr = Nothing
                End If
            
            'Next Transaction
            Next fldTr
            End With
            tblTr.MoveNext
            Loop
            tblTr.Close
            Set tblTr = Nothing
        End If
    End If
    
    Set oBapiLogon = Nothing
    Set oBapiCtrl = Nothing
End Function

The part in "Quotes" is what I do not like. What I want to do, is to avoid the SAP macro editor and manipulate the fields directly. I don't really understand how to do this, though.

This is the TO-BE situation:
- SAP login through SAP references
- Use Access for Transactions and Variant names
- Input values in Variants by VBA programming, using the oBapiCtrl.Value methods

Can anyone give me a hint or example as to how to program this directly, instead of using the "Session.FindById"-blabla?

Thanks in Advance,
Timo