Problem writing changes back to access database from gridcontrol

Darren Rose 281 Reputation points
2024-03-19T12:41:27.8566667+00:00

Hi

I am populating a grid with data from an access database file (.accdb), and it works fine for showing my data.

But I can't seem to get it to save changes from the grid back to the database file, my adapter.update(table) command completes without error, but the changes are not written back to the file.

What am I missing?

Public Class Form1
  
    Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=.\test_databases\Database1.accdb;Persist Security Info=False;"

    Dim connection As OleDbConnection
    Dim adapter As OleDbDataAdapter
    Dim dataSet As DataSet
    Dim commandBuilder As OleDbCommandBuilder
    Dim table As DataTable

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
 
        ' Connect to Access Database
        connection = New OleDbConnection(connectionString)

        ' Query to select data from a table
        Dim query As String = "SELECT FileAs, Dateoflastvisit, Dateoflastservice ,EmailAddress  FROM Contacts_export_test"

        ' Create DataAdapter
        adapter = New OleDbDataAdapter(query, connection)

        ' Create command builder to generate update commands
        commandBuilder = New OleDbCommandBuilder(adapter)

        ' Create and fill table
        table = New DataTable
        adapter.Fill(table)

        ' Bind the Table to the GridControl
        GridControl1.DataSource = table

    End Sub
  

    Private Sub SimpleButton1_Click(sender As Object, e As EventArgs) Handles SimpleButton1.Click
       
        Try

            ' Ensure the connection is open
            If connection.State = ConnectionState.Closed Then connection.Open()

            ' Update changes to the database
            adapter.Update(table)

            ' Close connection
            connection.Close()

            MessageBox.Show("Changes saved to the database successfully!", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)

        Catch ex As Exception
         MessageBox.Show("Error saving changes: " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

    End Sub


End Class

Access
Access
A family of Microsoft relational database management systems designed for ease of use.
390 questions
VB
VB
An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
2,726 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Darren Rose 281 Reputation points
    2024-03-21T10:36:42.63+00:00

    Problem resolved - was down to a simple mistake - database file in project was set to Copy Always to project folder rather than Copy If Newer!

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.