Problems with UPDATE statement for MS Access

I have a a short procedure which runs through a datatable and updates a certain number of entries. It doesn't seem to make the changes to the database at all. It displays the correct row counts etc. but just won't update the rows in the database.

I don't mind how it happens, i just want the contents of two fields in the Database swopped in certain cases.

Any suggestions on what i have done wrong would be very much appreciated.

Dim Conn As New OleDbConnection
        Conn.ConnectionString = My.Settings.ConnReminder
        Dim strSQL As String
        strSQL = " SELECT * FROM tblReminders"

        Dim ds As New DataSet
        Dim dt As New DataTable
        Dim da As New OleDbDataAdapter(strSQL, Conn)
        Dim row As DataRow
        Dim strReminderText As String
        Dim strSubject As String
        Dim strRemNo As String
        Dim donecount As Integer
        donecount = 1


        For Each row In dt.Rows
            If row("MMDept").ToString = "MBM" Then
                donecount = donecount + 1
                strRemNo = row("RemNo").ToString
                strSubject = row("Subject").ToString
                strReminderText = row("ReminderText").ToString

                Dim myCommand As New OleDbCommand("UPDATE tblReminders SET [ReminderText] = @strSubject, [Subject] = @strReminderText WHERE [RemNo]= @strRemNo", Conn)

                With myCommand.Parameters
                    .AddWithValue("@strSubject", strSubject)
                    .AddWithValue("@strReminderText", strReminderText)
                    .AddWithValue("@strRemNo", strRemNo)
                End With
                da.UpdateCommand = myCommand
            End If



Open in new window

Who is Participating?
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
You can also just update directly:

mycommand.commandtext = "UPDATE tblReminders SET [ReminderText] ='" & strSubject & "', [Subject] =' & " strReminterTExt & "' WHERE [RemNo]='" & strRemNo & "'"
Shaun KlineLead Software EngineerCommented:
See this link on Microsoft's website, under remarks:
The OLE DB.NET Framework Data Provider uses positional parameters that are marked with a question mark (?) instead of named parameters.

As an alternative to using the command object, you could use the data adapter's update method.
BozMAuthor Commented:
Thanks Guys,

I will try Scott's shorter version. Is there anything actually wrong with my code? I know it's long winded but is there any error in it?
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I don't use that syntax, but perhaps changing the order in which you declare the parameters:

Using cmd As New OLEDB.Command
  With myCommand.Parameters
   .AddWithValue("@strSubject", strSubject)
   .AddWithValue("@strReminderText", strReminderText)
   .AddWithValue("@strRemNo", strRemNo)
  End With
cmd.CommandText = ""UPDATE tblReminders SET [ReminderText] = @strSubject, [Subject] = @strReminderText WHERE [RemNo]= @strRemNo"

<etc etc here>
End Using
Shaun KlineConnect With a Mentor Lead Software EngineerCommented:
Per Microsoft's documentation, you cannot use named parameters in your query. You need to use the question mark (?) placeholder, and the parameters you define are substituted into your query at execution time in the order you add the parameters to the command object.
BozMAuthor Commented:
Apologies all, I have been out of the office for the last few days.

I will rework the code on Monday and come back to you. In code for the same form, albeit for a different button, I have named parameters for an MS Access command and that work's fine - I will try your suggestions on Monday and report back.

Thanks for your patience.
BozMAuthor Commented:
Thanks guys, so in the end, I just used executenonquery and took on Shaun's suggestion about positional parameters. Worked in the end. Thanks for your help. Happy Christmas to you both.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.