How to save datagridview data to the database file.

[pescadore]

I'm using VB Express 2008 in Windows 7 64-bit. I'm working on a project with a datagridview bound to a MS Access 2007 database. I want to use the datagridview to add, delete and update the data in the MS Access database. When I added the database file to the project through the Datasource configuration Wizard, I selected 'No' to copy the file to the project folder. The connection string automatically placed in the project settings is: "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="F:\My Money\DoBills\DoBills2009.accdb" The 'Copy to Output Directory" property for the DoBills2009DataSet.xsd is set to '*Do not copy*'(and I've tried every other setting available). So, as I understand it, I am working with the original file, not a copy, and any changes (additions, deletions, field changes, etc.) are made directly to the original file. I have added the following code to the datagridview rowleave event:

Private Sub dgvSortBills_RowLeave(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvSortBills.RowLeave
Try
Me.Validate()
Me.SortBillsBindingSource.EndEdit()
Me.TableAdapterManager.UpdateAll(Me.DoBills_2009DataSet)
Me.DoBills_2009DataSet.AcceptChanges()
CalcTotals() 'sub procedure that doesn't affect the datafile.
Catch ex As Exception
MsgBox("Update failed")
End Try
End Sub

Later, I'll figure out a way to skip the update if nothing is changed, but for right now, it doesn't work. I've searched MSDN, the MS Community forum and several other forums for a solution and all I have found is what I already have and it doesn't work. I can make changes at run-time alright, but the exception it triggered when I change rows, and the data is not saved to the database file. When I close/re-run the app, the changes to the data are not there. There has to be a way to save changes to the datafile, but I'm just not finding it. Can someone please help?

 

[pescadore]

I've solved this problem, but I'm not sure I can adequately explain how the solution works. First of all, the datasource was a query residing in my MS Access datafile rather than a table. When I changed the datasourse to the the tables in the datafile, insert, delete and update commands were automaically created. So, I can now save changes in the datagridview data back to the database. If anyone is interested in more specific details, I would be more than happy to put more effort into all the necessary changes.