Member Avatar for Mr.BunyRabit

Hey guys. I am seriously not as smart as you all.

I am working on this program and need read info from an access database to a DataGridView. Now that part i can do. the part i cannot do, and the code i dont know what it is, is to save or update the newly edited data to the access file.

Can you guys please help me. I have no clue what so ever.

Thank you.
Ruan

  • 2 Contributors
  • 6 Replies
  • 130 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by JJCollins

Dani AI

Generated

As noted, issuing SQL (INSERT/UPDATE) works, but a simpler, maintainable pattern for an editable DataGridView bound to an Access file is to use an OleDbDataAdapter + DataTable and let the adapter push changes back to the database. For — who already loads rows into a grid — the flow is: load schema (so the primary key is known), bind the DataTable, commit any in-progress edits, then call the adapter's Update method.

A minimal VB.NET pattern:

Imports System.Data.OleDb

Dim connStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Path\MyDB.accdb;"
Dim conn As New OleDbConnection(connStr)
Dim da As OleDbDataAdapter
Dim ds As New DataSet()

Sub LoadGrid()
    da = New OleDbDataAdapter("SELECT * FROM tblExample", conn)
    da.FillSchema(ds, "tblExample", SchemaType.Source) ' capture PK
    da.Fill(ds, "tblExample")
    Dim cb As New OleDbCommandBuilder(da) ' generates Insert/Update/Delete
    DataGridView1.DataSource = ds.Tables("tblExample")
End Sub

Sub SaveChanges()
    BindingSource1.EndEdit()
    DataGridView1.EndEdit()
    da.Update(ds.Tables("tblExample"))
End Sub

Troubleshooting and cautions:

  • Call FillSchema before Fill so the DataTable has the primary key; CommandBuilder needs it to generate UPDATE/DELETE.
  • Do not call AcceptChanges() before Update() — that clears row states and prevents updates.
  • Ensure edits are committed with BindingSource.EndEdit() or DataGridView.EndEdit() before da.Update(...).
  • Match app bitness to the Access provider (ACE is often 32-bit); set the project to x86 or install a compatible driver.
  • "Operation must use an updateable query" usually signals file/folder permission issues — place the .mdb/.accdb where the process can write.
  • CommandBuilder works for simple single-table selects; for joins or complex logic, supply explicit parameterized commands or use a server DB for multi-user scenarios.

This keeps the grid-binding workflow and automates the SQL work mentioned while avoiding manual per-row SQL strings.

Recommended Answers

All 6 Replies

Member Avatar for JJCollins

You would need to use an SQL Insert query to achieve this, for example;

INSERT INTO tblExample(Field1, Field2, Field3) VALUES (@Field1,@Field2,@Field3)

This would need to be done programatically, i've never used Access so all my code snippets and samples are SQL Server 2005 orientated although i can post them if you wish, for you to get an idea of how it's done?

- Jordan

Edited by JJCollins because: n/a
Member Avatar for Mr.BunyRabit

Im getting there.... its 1am... and i have officially hit another brick wall.... i think i made a wrong decision to have used access...

Edited by Mr.BunyRabit because: n/a
Member Avatar for Mr.BunyRabit

You would need to use an SQL Insert query to achieve this, for example;

INSERT INTO tblExample(Field1, Field2, Field3) VALUES (@Field1,@Field2,@Field3)

This would need to be done programatically, i've never used Access so all my code snippets and samples are SQL Server 2005 orientated although i can post them if you wish, for you to get an idea of how it's done?

- Jordan

Wow, thank you Jordan, that will Really be helpful... I myself am a beginner at database connections. i just used access as i assumed it would be the easiest. but if you suggest that i must use SQL, and give me a few pointers, i will change my program as soon as now. Because, this isnt the easiest one i am connecting to i am beginning to realize.

Member Avatar for JJCollins

Well at work we use SQL Server 2005 because of the ease of use and the portability more than anything, I'm not in a position to tell you what you should use as it can come down to personal preference, I have little experience with Access though so I wouldn't be of much help when it came down to using Access :)

- Jordan

Member Avatar for Mr.BunyRabit

=) Thank you. Oky, so this project is for next week, i will let you know how it went.. This is the part of my life where i am going to be ever against or for access or SQL, so lets see which one ill side with in the end.

Ruan. =)

Member Avatar for JJCollins

No problem and good luck :)

- Jordan

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.