This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(313.6, 0%, 40%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EDR%3C/text%3E%3C/svg%3E)
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
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(291.2, 11%, 38%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EHL%3C/text%3E%3C/svg%3E)
Hi,@Darren Rose. Welcome Microsoft Q&A. Your code looks mostly correct.
You could try to check:
Primary Key in the Database Table: The OleDbCommandBuilder relies on having a primary key defined in your database table to generate proper update commands. Ensure that your table has a primary key defined. If not, you may need to manually specify the update command.
AutoIncrement Column: If your primary key column is an auto-incrementing column (such as an Access AutoNumber field), you need to ensure that the DataTable properly reflects this. You may need to set the AutoIncrement property of the primary key column to True and the AutoIncrementSeed and AutoIncrementStep properties accordingly.
DataAdapter Configuration: Make sure that the DataAdapter (adapter) is properly configured to handle updates, inserts, and deletes. The DataAdapter needs to have its UpdateCommand, InsertCommand, and DeleteCommand properties set in order to save changes back to the database.
Refresh Data: After calling adapter.Update(table), try refreshing the data in your DataTable by re-filling it from the database. This ensures that your DataTable reflects any changes made to the database during the update process.
If the problem persists, can you show a minimal, reproducible example without private information so I can reproduce the issue? What are the steps to reproduce the problem?
HI, thanks for your reply.
In the Access database file the FileAs field is set as primary key.
It is not an AutoIncrement column.
The code I am using is as posted, with the form1 containing the grid control and a single button to trigger saving the changes.
I can provide a sample of the .accdb file if needed.
Re your point about DataAdapter Configuration how do I do this? I thought the line where I declare the commandbuilder should automatically create these?
Hi,@Darren Rose . To load table data, you could use the DataGridView control instead of Grid control.
You can use a DataGridView control to display data with or without an underlying data source. Without specifying a data source, you can create columns and rows that contain data and add them directly to the DataGridView using the Rows and Columns properties.
I don’t know if it’s because of the control, you could try the following steps to see if the data is updated.
update your code:
DataGridView1.DataSource = table
The control I am using is a DevExpress GridControl - a more powerful equivalent to the built-in DataGridView, sorry I didn't make that clear in my initial post, but whatever control I use doesn't solve the issue I have asked about, as underlying data source is still not update when clicking my button
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!