Read,write and delte from access by 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

A complete script would of course be the best thing for a "stupid" amateurlike me! But I would be very thankful for any help!

Best regards
Vadar

 

[Golom]

Something like this

Dim objConnection
Dim strConnectionString
Dim IDNum
Dim strSQL
Dim rs

strConnectionString = "Provider=MSDASQL;DSN=SampleDSN;UID=;PWD=;"
Set objConnection = CreateObject("ADODB.Connection")
objConnection.ConnectionString = strConnectionString
objConnection.Open

IDNum = 55

[COLOR=black cyan]' Retrieve data into a recordset[/color]
strSQL = "Select * From (TagValue) Where ID = " & IDNum
Set rs = CreateObject("ADODB.Recordset")
rs.CursorLocation = 3
rs.Open strSQL, objConnection

If Not rs.EOF Then
    [COLOR=black cyan]' Delete data from the table[/color]
    strSQL = "Delete * From (TagValue) Where ID = " & IDNum
    objConnection.Execute strSQL
End If
rs.Close
objConnection.Close
Set objConnection = Nothing

 

[vadarv]

Dim objConnection
Dim strConnectionString
Dim strSQL
Dim objCommand

strConnectionString = "Provider=MSDASQL;DSN=SampleDSN;UID=;PWD=;"
strSQL = "Delete from WINCC_DATA Where ID=1"
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 worked fine!!! I'm a happy man and a bit less amateur ?!

Thank you for your help and input!!
Great forum!


Cheers
Vadar