read, write and delte in access strSQL

[vadarv]

Hia!
I'm a total amateur to VBS but need help on a VBS script. This is used in a HMI system from Siemens called WinCC, used for process viewing and control.

What I need to is to write to a table in Access, then read these values and then delete records (rows) in Access.
By using help functions in WinCC I have this set up:

I created an Access database with the WINCC_DATA table and columns (ID,
TagValue) with the ID as the Auto Value.

Then I can write to Access by the following code:

Dim objConnection
Dim strConnectionString
Dim lngValue
Dim strSQL
Dim objCommand
strConnectionString = "Provider=MSDASQL;DSN=SampleDSN;UID=;PWD=;"
lngValue = HMIRuntime.Tags("Tag1").Read
strSQL = "INSERT INTO WINCC_DATA (TagValue) VALUES (" & lngValue & ");"
Set objConnection = CreateObject("ADODB.Connection")
objConnection.ConnectionString = strConnectionString
objConnection.Open
Set objCommand = CreateObject("ADODB.Command")
With objCommand
.ActiveConnection = objConnection
.CommandText = strSQL
End With
objCommand.Execute
Set objCommand = Nothing
objConnection.Close
Set objConnection = Nothing

This actually works fine, pure luck I guess!

Can anyone help me with scripts for:
1. Reading one record (row) according to a ID number
2. Deleting the a record (row) according to a ID number

I would be very thankful for any help!

Best regards
Vadar

 

[Pampers]

Hi Vadarv,
Here is an example of loading one Order based on a selection from a combox (using OrderID). Maybe you can use it.

Public Sub cmdLoad_Click()
    Dim rst As ADODB.Recordset
    Dim fld As ADODB.Field
    Dim lngOrderID As Long
    Dim fOK As Boolean
    Dim strID As String
    Dim strSQL As String
    Dim strMsg As String

    On Error GoTo HandleErr

    ' User Selected an order to display.
    ' Check to make sure we have a numeric OrderID
    If IsNumeric(Me.txtOrderID) Then
        lngOrderID = CLng(Me.txtOrderID)
    Else
        strMsg = "Please select or enter an OrderID"
    End If
    
    ' Bail if invalid or blank OrderID
    If Len(strMsg) > 0 Then
        MsgBox strMsg, , "Can't Display Order"
        Me.cboCustomer.SetFocus
        GoTo ExitHere
    End If
    
    ' Ensure the right subform is visible
    Me.fsubOrderDetail.Visible = True
    Me.fsubLineItemAddEdit.Visible = False
    
    ' Connect to Northwind
    fOK = OpenConnection()
    If fOK = False Then
        MsgBox "Unable to Connect", , _
          "Can't connect to the database."
        Forms!frmLogin.Visible = True
        GoTo ExitHere
    End If

    ' Get the order record
    Set rst = New ADODB.Recordset
    rst.CursorLocation = adUseClient
    rst.Open Source:="EXEC procOrderSelect " & _
      lngOrderID, _
      ActiveConnection:=gcnn
    
    ' Display order data in form controls
    If rst.EOF Then
        MsgBox "Record can't be found.", , _
          "Order does not exist"
        GoTo ExitHere
    Else
        For Each fld In rst.Fields
            Me(fld.Name).Value = fld.Value
        Next
        ' Synchronize the customer combo box,
        '    in case OrderID was filled in manually.
        Me.cboCustomer = Me.CustomerID
    End If
    
    ' Set ConcurrencyID property
    Me.ConcurrencyID = rst!ConcurrencyID
    
    ' Call procedure in subform
    '   to create recordset of line items
    '   and bind the subform to the recordset
    Me.fsubOrderDetail.Form.LoadLineItems
    SaleRecalc
    
    ' Nothing to save yet
    Me.IsDirty = False
    ' Not on a new record
    Me.IsNew = False

ExitHere:
    Exit Sub
    
HandleErr:
    Select Case Err
        Case 2465   ' Field does not exist
            Resume Next
        Case Else
            MsgBox Err & ": " & Err.Description, , "cmdLoad_Click() Error"
    End Select
    Resume ExitHere
    Resume
End Sub

Pampers
Just back from holiday...
that may it explain it.