After Connecting from VBA, you are ready to update Sage 50 UK from macros. The following sections show how to execute parameterized queries to update data. See Executing Parameterized Queries for a complete macro.

Insert

Call the Insert method to execute SQL INSERT statements.

public bool Insert(string queryString, object paramNames, object paramValues)

The Insert method returns a boolean indicating the success or failure of the statement execution and accepts the following parameters:

queryString	The query to execute; for example, INSERT INTO TradingAccounts (Name) VALUES ('Smith')
paramNames	An array with the named parameters used in the query. Required if using a parameterized query.
paramValues	An array with the values that correspond to the parameter names. Required if using a parameterized query.

The following example inserts the values specified in the parameter arrays and displays a message box indicating the success or failure of the INSERT.

Dim nameArray
nameArray = Array("Name", "TradingAccountUUID")
Dim valueArray
valueArray = Array("Joe Smith", "c2ef66a5-a545-413b-9312-79a53caadbc4")
Dim module As New ExcelComModule
module.SetProviderName ("Sage50UK")
module.SetConnectionString ("URL=http://localhost:5493/sdata/accounts50/GCRM/{C4C863BE-B098-4A7D-A78B-D7A92B8ADB59};User=Manager;Password=xxxxxx;")
Query = "INSERT INTO TradingAccounts (Name, TradingAccountUUID) VALUES (@Name, @TradingAccountUUID)"
If module.Insert(Query, nameArray, valueArray) Then
  MsgBox "The insertion was successful."
  'lastInsertedRowIdentity = GetLastInsertedRowIdentity(module)
  'MsgBox lastInsertedRowIdentity
Else
  MsgBox "The insertion failed."
End If
module.Close

For a successful insertion, and if SCOPE_IDENTITY() is supported by the Sage 50 UK provider, then the following VBA function example can be called to retrieve the last inserted row identity.

Function GetLastInsertedRowIdentity(module) As String
  Dim lastInsertedRowIdentity As String
  lastInsertedRowIdentity = ""
  If module.Select("SELECT SCOPE_IDENTITY()", prmNames, prmValues) Then
    lastInsertedRowIdentity = module.GetColumnValue(0)
  End If
  GetLastInsertedRowIdentity = lastInsertedRowIdentity
End Function

Update

Call the Update method to execute SQL UPDATE statements.

public bool Update(string queryString, object paramNames, object paramValues)

The Update method returns a boolean indicating the success or failure of the statement execution and accepts the following parameters:

queryString	The statement to execute.
paramNames	An array with the named parameters used in the query. Required if using a parameterized query.
paramValues	An array with the corresponding parameter values. Required if using a parameterized query.

The following example updates the values specified in the parameter arrays and displays a message box indicating the success or failure of the update.

Dim nameArray
nameArray = Array("Name", "TradingAccountUUID","TradingAccountUUID")
Dim valueArray
valueArray = Array("Joe Smith", "c2ef66a5-a545-413b-9312-79a53caadbc4","c2ef66a5-a545-413b-9312-79a53caadbc4")
Dim module As New ExcelComModule
module.SetProviderName ("Sage50UK")
module.SetConnectionString ("URL=http://localhost:5493/sdata/accounts50/GCRM/{C4C863BE-B098-4A7D-A78B-D7A92B8ADB59};User=Manager;Password=xxxxxx;")
Query = "UPDATE TradingAccounts SET Name=@Name, TradingAccountUUID=@TradingAccountUUID WHERE TradingAccountUUID=@TradingAccountUUID
If module.Update(Query, nameArray, valueArray) Then
  MsgBox "The update was successful."
Else
  MsgBox "The update failed."
End If
module.Close

Delete

Call the Delete method to execute SQL DELETE statements.

public bool Delete(string queryString, object paramNames, object paramValues)

The Delete method returns a boolean indicating the query's success or failure and accepts the following parameters:

queryString	The query to execute; for example, SELECT TradingAccountUUID, Name FROM TradingAccounts WHERE TradingAccountUUID = 'c2ef66a5-a545-413b-9312-79a53caadbc4'
paramNames	An array with the named parameters used in the query. Required if using a parameterized query.
paramValues	An array with the values that correspond to the parameter names. Required if using a parameterized query.

The following example deletes a record, specified by its key, and displays a message box indicating the success or failure of the delete.

Dim module As New ExcelComModule
module.SetProviderName ("Sage50UK")
module.SetConnectionString("URL=http://localhost:5493/sdata/accounts50/GCRM/{C4C863BE-B098-4A7D-A78B-D7A92B8ADB59};User=Manager;Password=xxxxxx;")
Dim nameArray
nameArray = Array("TradingAccountUUID")
Dim valueArray
valueArray = Array("c2ef66a5-a545-413b-9312-79a53caadbc4")
Query = "DELETE FROM TradingAccounts WHERE TradingAccountUUID='c2ef66a5-a545-413b-9312-79a53caadbc4'"
If module.Delete(Query,nameArray,valueArray) Then
  MsgBox "The delete was successful."
Else
  MsgBox "The delete failed."
End If 
module.Close