Update MS Access via vb.net

Question

the below code runs without error, but the MS Access table isn't updating. What am I missing?

    Try
        cnn = New OleDbConnection(ConfigurationManager.ConnectionStrings("accConnectionString").ToString())
        cnn.Open()
    Catch ex As Exception
        Debug.Print("Oops - no connection to database")
        Exit Sub
    End Try

    Dim sql As String
    sql = "SELECT * FROM tblSend WHERE UploadedSuccessfullyOn is null ORDER BY QueuedOn;"
    Dim da As New OleDbDataAdapter(sql, cnn)
    Dim ds As New DataSet
    da.Fill(ds, "dsQueuedToSend")
    For Each dr As DataRow In ds.Tables("dsQueuedToSend").Rows

    ' Perform other unrelated tasks in this space, removed for brevity

        ' If those other tasks were successful, update Access.
        If success = True Then
            sql = "UPDATE [tblSend] SET [UploadedSuccessfullyOn] = @uploadedOn WHERE (([UploadID]) = @uploadId);"
            Dim cmd As OleDbCommand
            cmd = New OleDbCommand(sql, cnn)
            cmd.Parameters.Add("@uploadedOn", OleDbType.Date).Value = Now
            cmd.Parameters.Add("@uploadedId", OleDbType.Integer).Value = dr("UploadID")
            cmd.ExecuteNonQuery()
            cmd.Dispose()
            Console.WriteLine("Success: updated.")
        Else
            Console.WriteLine("Failed: Not updated.")
        End If
    Next

    da.Dispose()
    cnn.Close()

I've also tried with:

 sql =  "UPDATE [tblSend] SET [UploadedSuccessfullyOn] = ? WHERE (([UploadID]) = ?);"

Or simply add the values in the not-preferred/non-parameter approach:

sql =  "UPDATE [tblSend] SET [UploadedSuccessfullyOn] = #" & Now & "# WHERE (([UploadID]) = " & dr("UploadID") & ");"
Dim cmd As OleDbCommand
cmd = New OleDbCommand(sql, cnn)
cmd.ExecuteNonQuery()
cmd.Dispose()

None of these approaches updates the MS Access table. Ideas? Thanks!

When you say "runs without error" do you mean that you've run the application in its entirety and it didn't crash, or do you mean that you stepped through the code in a debugger and verified that every expected code path was reached and all expected runtime values were correct? — David
The second: I step through with the debugger and verified that the connection is open, that the sql statement produces a syntactically correct working sql (tested the non-parameter option by copying over and running manually in Access, which worked), the datarow has an accurate value, and best I can tell, the rest of the runtime values are correct. This is why I'm stumped - looks like this code should work, yes? — ericrdb
<connectionStrings> <add name="accConnectionString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\ups-soap.accdb" providerName="System.Data.OleDb"/> </connectionStrings> — ericrdb

Ben McIntyre Ben McIntyre · Accepted Answer · 2013-12-28T07:02:17

One thing I have been caught by before with Visual Studio is that when embedding an Access database in a WinForms project, when you run the project, the Access database is copied over from the original to the runtime directory, overwriting any changes you've made.

This can create the illusion that an update is not taking place, when in fact if you check at the right moment, it is.

Update MS Access via vb.net

1 Answers