Why won't this update my database?

Hi Experts,
I'm trying to update a record in a table in an Access database. My code doesn't throw me any error messages and stepping through the code I can see the value of the field gets changed in the datarow. But somehow it doesn't get saved in the database itself. What am I doing wrong?
thanks,
Michiel
Dim _dbCon As New OleDb.OleDbConnection
        Dim _dataSet As New DataSet
        Dim _dataAdap As OleDb.OleDbDataAdapter
        Dim _command As String
        Dim _row As DataRow

        Try
            _dbCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\michiel\Desktop\2011\Test.mdb"
            _dbCon.Open()

            _command = "SELECT * FROM tbl_Table WHERE RecID=1"
            _dataAdap = New OleDb.OleDbDataAdapter(_command, _dbCon)
            _dataAdap.Fill(_dataSet, "tbl_Table")
            _row = _dataSet.Tables("tbl_Table").Rows(0)

            _row("Field1") = "Modiefied!"
            _row.AcceptChanges()
            _dataAdap.Update(_dataSet, "tbl_Table")
            _dbCon.Close()
        Catch _ex As Exception
            MessageBox.Show(_ex.Message)
        End Try

Open in new window

altiplanoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CodeCruiserCommented:
Remove the

 _row.AcceptChanges()
0
CodeCruiserCommented:
I hope this is just for testing code as other wise its a terrible way to update a record.
0
altiplanoAuthor Commented:
Thanks for the very quick reply! Removing the line as you suggested throws me the following error:
"Update requires a valid UpdateCommand when passed DataRow collection with modified rows."

Yes, this is for testing purposes. In the real application I have to update 25 fields and I just don't like to build such a long SQL-string. But if you have a cleaner method I would be very interested.
regards,
Michiel
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

lucius_theCommented:
you also need to create an oledbcommandbuilder:

dim cb as new oledbcommandbuilder (dataAdap)

before the line:
_dataAdap.Update(_dataSet, "tbl_Table")
0
CodeCruiserCommented:
You would need to build the sql. But to get the above code to work, make sure you have a primary key field in your select statement.
0
altiplanoAuthor Commented:
Hi Lucius,
At first your suggestion did not work, but then, on a hunch, I compacted the database and all of a sudden it worked. I really don't have a clue what this statement does, but it seems to be necessary.
thanks,
Michiel
0
lucius_theCommented:
The statement creates the command objects, that your dataadapter needs to update the datatable to the DB backend. You can either write them manually or use the commandbuilder which does it automatically.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
altiplanoAuthor Commented:
Hi Codecruiser,

I agree with you that this is a very convoluted way of updating racords. Could you please show me what would be the most efficient way of achieving the following?

1. Check if a record exist by searching for the key in field RecID
2. If the record doesn't exist, create a new one and add 25 values to it
3. If the record exists, update the same 25 fields with new values.

(If you feel this requires a separate question, than that's fine with me, the points for this question go to Lucious).

regards,
Michiel
0
altiplanoAuthor Commented:
Hello gentlemen,
I created a separate question for the above: Q_27483388. I would greatly apreciate your help.
regards,
Michiel
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.