Problem: Insert statements run through VBA are returning rowsAffected = 1, however when a select statement is run on that table, it does not show any new rows. Running the exact same insert statement through SQL Server works as intended, so I know it isn't the statement itself that is the issue.
I have created a spreadsheet in which my colleagues can input some data into columns and my VBA will determine what data needs to be updated into our server. I am having trouble with INSERT statements, however my UPDATE statements are working just fine.
Below is the code I am running. It runs an UPDATE query first, and if rowsAffected returns 0, then it instead grabs the corresponding INSERT statement and attempts to run that. Prior to this subroutine running, I have another sub that creates all the UPDATE queries and all of the INSERT queries and stores them in collections which are passed to the below subroutine.
Private Sub sqlQueryNotes(queries As Collection, iQueries As Collection)
'queries collection stores all of the UPDATE queries
'iQueries collection stores all of the INSERT queries
Dim rAffected As Integer
Dim cn As Adodb.Connection
Set cn = New Adodb.Connection
Dim cString As String
cString = "Provider=MSOLEDBSQL;" & _
"Server=[SERVERNAME];" & _
"Database=[DATABASENAME];" & _
"Trusted_Connection=yes;"
cn.connectionString = cString
cn.Open
Dim i As Integer
For i = 1 To queries.Count
cn.Execute "Begin Tran " & queries.Item(i), rAffected 'UPDATE statement
If rAffected = 0 Then
'below code looks a bit messy but it works, it finds the contractID from the update query and uses that as a key for the insertQuery key to find the corresponding insert query. It could be done better, but it works
cn.Execute "Begin Tran " & iQueries.Item(getContractID(queries.Item(i))), rAffected
If rAffected > 1 Then
cn.Execute "Rollback"
GoTo NextQuery
ElseIf rAffected = 0 Then
GoTo NextQuery
Else
cn.Execute "Commit"
GoTo NextQuery
End If
ElseIf rAffected = 1 Then
cn.Execute "Commit "
End If
NextQuery:
Next i
cn.Close
Set cn = Nothing
End Sub
I have tested this dozens of times, each time individually stepping through each line and checking the rowsAffected variable manually confirming that it has in fact inserted the data I want (by returning the value 1). This code DOES work exactly the way as intended, and I do in fact get rowsAffected = 1 when I run the INSERT statements.
However, when I run a SELECT statement for the table it is inserting into, it returns no new rows!
I have taken the INSERT statements generated with my VBA code and have run them through SQL Server and they work fine. So why won't it work through my VBA code?
In case it is relevant, below is the code I run to generate the UPDATE statements and the INSERT statements. I have removed quite a few irrelevant lines for the sake of brevity
Sub UpdateBackendDatabase()
Dim ws As Worksheet
Dim updNoteCol As Integer: updNoteCol = Range("_" & Replace(Left(ws.Name, 6), " ", vbNullString) & "_uNotes").Column
Dim refNoteCol As Integer: refNoteCol = updNoteCol - 1
Dim contractIdCol As Integer: contractIdCol = Range("_" & Replace(Left(ws.Name, 6), " ", vbNullString) & "_contractId").Column
Dim contractID As String
Dim tbl As Range
Set tbl = ws.Range("tblReporting_" & Replace(Left(ws.Name, 6), " ", vbNullString))
Dim notesQueriesColl As New Collection
Dim insCollection As New Collection
Dim updateQry As String
Dim insertQry As String
'Loop through all rows in table and find values that have been updates and add them to a query and add the queries to a collection of queries.
Dim i As Integer
For i = 3 To Range("tblReporting_" & Replace(Left(ws.Name, 6), " ", vbNullString)).Rows.Count + 2
contractID = tbl.Cells(i, contractIdCol).Value
'===============
'==== NOTES ====
'===============
If tbl.Cells(i, updNoteCol).Value <> vbNullString Then
'grabs notes from the refNoteCol and concats it with new additional notes from the updNotesCol
updateQry = "Update tblCMNotes " & _
"Set strNotes = '" & tbl.Cells(i, refNoteCol) & "; " & Format(Date, "dd/mm/yyyy") & " - " & tbl.Cells(i, updNoteCol).Value & "' " & _
"Where fkidContract = " & CStr(contractID)
insertQry = "Insert Into tblCMNotes " & _
"(fkidContract, strNotes) " & _
"Values " & _
"(" & contractID & ", '" & tbl.Cells(i, refNoteCol) & "; " & Format(Date, "dd/mm/yyyy") & " - " & tbl.Cells(i, updNoteCol).Value & "') "
notesQueriesColl.Add updateQry
insCollection.Add insertQry, getContractID(updateQry)
End If
Next i
Set ws = Nothing
End Sub
Thank you very much for looking into this question.
