Working with databases (OleDb, MS Access 2007) code-wise (UPDATE, INSERT, DELETE COMMANDS)

In my previous post Working with databases (OleDb, MS Access 2007) code-wise (SELECT COMMAND) I’ve made a small introduction regarding this matter, so I won’t bother doing the same here.

In order to execute UPDATE, INSERT OR DELETE statements against our database, we pretty much need the same objects as we did for retrieving data from a database:

1. A database connection
2. A SQL command

We’ll start off again by declaring the objects we need:

'Declaring required objects
Dim oCon As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=MyDatabase.accdb;Persist Security Info=False;")
Dim oCommand As New OleDb.OleDbCommand("", con)

The following code snippet will show how to execute one of the statements:

'Code example for executing an UPDATE statement against our database
Try
	'Set the query
	oCommand.CommandText = "UPDATE MyTable SET MyField = 'MyNewValue' WHERE MyField = 'MyCurrentValue'"

	'Open connection
	oCon.Open()

	'Execute the command
	oCommand.ExecuteNonQuery()

	'Close connection
	oCon.Close()

Catch ex As OleDb.OleDbException
	'Show oledb exception
	MessageBox.Show(ex.ToString)
End Try

Now this example showed how to do UPDATE commands, but in order to execute INSERT or DELETE statement it works the exact same way.
Just replace the oCommand.CommandText = “…” with the statement you wish to use 🙂

Have fun!