AnsweredAssumed Answered

Update or Delete rows in VB through SQL Server and a Linked Server PIOLEDBENT PI AF

Question asked by Dubus on Jun 7, 2018
Latest reply on Jun 8, 2018 by gregor



In MS SQL Server, I have created a Linked Server with a PIOLEDBENT (PI OLEDB Enterprise).

In a sheet Excel, I've created a VB code to do a select in EnumerationSet,EnumerationValue and it works (see code below)

Now, I would like to do an update and a delete of an enumeration value but I don't find the right syntax or parameters. I'm connected with admin rigths.


Exemple to do a select :

'VB Code :

Sub Get_Enum_Act_list()

'Déclaration des variables
Dim cnx As New ADODB.Connection
Dim rst As New ADODB.Recordset

cnx.ConnectionString = "UID=xxx;PWD=xxx;DRIVER={SQL Server};Server=xxx;Database=master;"


'Exécution de la requête
Set rst = cnx.Execute("SELECT FROM [PIAF].[Database1].[Asset].[EnumerationValue] as v, [PIAF].[Database1].[Asset].[EnumerationSet] as s where v.EnumerationSetID = s.ID and = 'Activities' order by")

Sheets("Activity").Range("A1").CopyFromRecordset rst

End Sub


I tried first to delete in MS SQL Server but without success:


- Select * from Openquery(PIAF, 'delete from [Database1].[Asset].[EnumerationValue] where value = 15')

- delete openquery(PIAF,'select * from  [Database1].[Asset].[EnumerationValue] where value = 15')


- delete from [PIAF].[Database1].[Asset].[EnumerationValue] where value = 15

- select * from openquery(PIAF, 'EXECSCRIPT([DELETE FROM Database1.Asset.EnumerationValue WHERE id = 15])')





With PIAF = the Linked Server


Any idea to update or delete rows with Linked Server in MS SQL Server ?


Thank you very much