How to edit a record in an access database

I want to edit a specific record in an access database but I keep on getting errors

this is the database I want to edit: Access database

these are flashcards that the user has created and stored in an access database. What I want is that the user is able to edit the difficulty so it appears more/less often

This is the module:

Module Module1
Public Function runSQL(ByVal query As String) As DataTable
    Dim connection As New OleDb.OleDbConnection("provider=microsoft.ACE.OLEDB.12.0;Data Source=flashcard login.accdb") 'Establishes connection to database
    Dim dt As New DataTable 'Stores database in table called dt
    Dim dataadapter As OleDb.OleDbDataAdapter

    connection.Open() 'Opens connection
    dataadapter = New OleDb.OleDbDataAdapter(query, connection)
    dt.Clear() 'Clears datatable
    dataadapter.Fill(dt) 'Fills datatable
    connection.Close()
    Return dt
End Function
End Module

And here is the button that the user can press to edit the database:

Private Sub btnSubmit_Click(sender As Object, e As EventArgs) Handles btnSubmit.Click
    Dim sql As String
    sql = "UPDATE flashcards set difficulty = '" & TxtDifficulty.Text
    runSQL(sql)
End Sub

The difficulty column in the database should be able to be edited by the user through the value they entered in txtDifficulty.text

CodePudding user response：

Good to hear I found the problem with the apostrophe.

I am going to need a where statement but the problem I have is that the user can create as much flashcards as they want so how would I write the where statement?

An INSERT statement does not have a WHERE clause but an UPDATE does and is usually by a primary key.

Look at how I add a new record ignoring mHasException and specifically using parameters. In this case a List is used but with little effort an array of DataRow can be passed instead.

Here is an example for updating a record with a DataRow.

To get other code samples for ms-access see the following repository.

In closing, in the above repository I don't get into all possibilities yet there should be enough there to get you going. And when reviewing code I flip between Add for adding a parameter and AddWithValue while Add is the recommend way but both are shown to see differences. see also Add vs AddWithValue.