Modifying Data
After Connecting from VBA, you are ready to update Avro 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 SampleTable_1 (Column1) VALUES ('John') |
| 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("Column1", "Column2")
Dim valueArray
valueArray = Array("Jon Doe", "Bob")
Dim module As New ExcelComModule
module.SetProviderName ("Avro")
module.SetConnectionString ("URI=C:\folder\table.avro;")
Query = "INSERT INTO SampleTable_1 (Column1, Column2) VALUES (@Column1, @Column2)"
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 Avro 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