How to Add/Update/Delete Record using MS Access Database

Submitted by admin on Tuesday, March 10, 2009 - 15:01.

Language

  • Visual Basic .NET

The primary purpose of this code is to teach beginner programmer to familiarize the concept of database programming.

This is particularly for beginner but may also applicable for intermediate programmer.

Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. After downloading it, you will need a program like Winzip to decompress it.

Virus note: All files are scanned once-a-day by SourceCodester.com for viruses, but new viruses come out every day, so no prevention program can catch 100% of them.

FOR YOUR OWN SAFETY, PLEASE:

1. Re-scan downloaded files using your personal virus checker before using it.
2. NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.

Comments

Submitted byAnonymous (not verified)on Tue, 10/20/2009 - 02:53

thank you>>>>>>>>>>>>>>>
Submitted byAnonymous (not verified)on Thu, 11/12/2009 - 18:42

how we can use " insert command " in vb ? pls send me some examples which describe the code
Submitted byAnonymous (not verified)on Thu, 12/10/2009 - 14:26

Could you please help me i used all types of memory stream code i have an error parameter in not valid; here is my code OleDbCommand cmd = new OleDbCommand("SELECT photo FROM stdetails where stuid='" + txtstid.Text + "'", conn); DataSet ds = new DataSet(); OleDbDataAdapter da = new OleDbDataAdapter(cmd); da.Fill(ds, "stdetails"); byte[] content = (byte[])ds.Tables[0].Rows[0]["photo"]; try { MemoryStream stream = new MemoryStream(content,true); // i have problem in this place pictureBox2.Image = Image.FromStream(stream,true,true); } catch (Exception ex) { MessageBox.Show(ex.Message.ToString()); MessageBox.Show(ex.StackTrace.ToString()); }
Submitted byAnonymous (not verified)on Mon, 12/28/2009 - 09:53

Hi, I'm beginner of VB.NET. Pls help me about the database connection using vb.net with sql server,ms access and oracle with step by step process with screen shot. pls pls pls........... My mail ID is:[email protected]
Submitted byAnonymous (not verified)on Tue, 01/26/2010 - 10:53

..plz post the step by step on how to add/update/and delete the record using ms access..tnx...i will wait for the response...Godbless....!!!
Submitted byAnonymous (not verified)on Fri, 03/12/2010 - 10:58

i face a problem saving password to Access. it work nicely if i remove password. the error occur on INSERT and UPDATE query. please help. using OleDb.. i can c the password value from the sqlcommand, but it just pop up the error message.
Submitted byAnonymous (not verified)on Sat, 03/20/2010 - 17:55

i hpe yor code will help me in mine...:)
Submitted byAnonymous (not verified)on Wed, 04/21/2010 - 01:03

cant see no codes here although i clicked to download them
Submitted byAnonymous (not verified)on Sun, 04/25/2010 - 21:51

Hi, I am using msaccess 2003 as backend in project. The code is just saving data which newly added but I want to update existing record of database using bindingnavigator. The code is throwing exception. Please help me as soon as possible since the date of my project submission is coming nearer. Thanks [email protected]
Submitted byAnonymous (not verified)on Mon, 04/26/2010 - 23:24

when ever i update my record it updtae only one record. if i add no-1 and then no-2. so in my view report only 2 will show 1 will delete , why its is happening
Submitted byAnonymous (not verified)on Sun, 06/20/2010 - 15:32

Hi, I am a beginner (vb.net programmer). I have ms access database with 2 tables. I connect to the database successfully but I want the primary key (Auto number) in table two to automatically appear in Foreign Key (Number) in table one after setting up the relationship. Please I need your help. [email protected] Thanks in advance.
Submitted byAnonymous (not verified)on Thu, 07/29/2010 - 12:29

hi, I am a start with vb.net. i have one table in Ms Access 2003 location is drive c: but I am unknown about insert data from vb.net to Ms Access 2003. Please, Can you tell me about this code? [email protected] Thank you!
Submitted byAnonymous (not verified)on Thu, 09/09/2010 - 21:57

Write in Form:- Imports System.Data.OleDb Public Class form1 Dim v1 As New Class1(Application.StartupPath) Dim ds As New DataSet Private Sub btninsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btninsert.Click v1.ExecCommand("insert into tablename values("Textbox1.Text"','" + Textbox2.Text + "')") MsgBox("Insert Successfully") End Sub Write in Class:- Imports System.Data.OleDb Public Class Class1 Dim cn As OleDbConnection Dim cm As OleDbCommand Dim da As OleDbDataAdapter Dim ds As DataSet Public vfrm As New FrmRecord Public Sub New(ByVal path As String) cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + "\Database.mdb(Table name which is in ms-access") End Sub Public Function GetDataSet(ByVal qry As String) As DataSet cm = New OleDbCommand(qry, cn) da = New OleDbDataAdapter(cm) ds = New DataSet da.Fill(ds) Return ds End Function
Submitted byAnonymous (not verified)on Fri, 09/24/2010 - 20:21

Try this code out, hope it helps: Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Users\dercio.mnisi\Desktop\Samito.mdb" Dim OleDBConne1 As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection(connString) OleDBConne1.Open()` Dim SQLString As String = "INSERT INTO Customer(customer_First_Names,customer_Surname,customer_Cell)VALUES('" & firstNames & "','" & surnames & "','" & cellNum & "')"
Submitted byAnonymous (not verified)on Mon, 01/31/2011 - 01:25

go to Data then
"addnewdatasource" just click Next the default is database then
then after you click Next click on new connection and
click Change and choose this "Microsoft Access Database File (OLE DB)" then
test connection, if its successful your doing right then click OK
note: if theres a messagebox appear during this just click No. as i say No if you dont want to get error..
click Next
now after next choose your database objects... and click Finish..
go to Data again and click show datasource..
u can click and drag the data to forms...
just check to properties to datasources... hope it helps...

Submitted byAnonymous (not verified)on Sun, 06/27/2010 - 10:56

i don know the code insert n delete in database M.Eccess 2007 using button combo box..and arrage the list of combobox follow by alphabet...can 2 help me
Submitted byAnonymous (not verified)on Mon, 08/02/2010 - 22:55

pls sir i really need your help, i need a code that can save data and image from vb.net 2010 to Microsoft access 2003....can u mail it to my box at [email protected]
Submitted byAnonymous (not verified)on Mon, 08/16/2010 - 11:08

i have implement my system using Ms acceess 2003. Now i have a problem with button. I have created one button that name SAVE button..i want this button have two function wich is it can ADD/DELETE at two different table. Can u help me?? Plese give a response at my email [email protected]
Submitted byAnonymous (not verified)on Thu, 09/16/2010 - 13:02

When i open there is no data, i cant add data also...There is no data connection, do i have to make one first?...i am using vb 2010 express.....
Submitted byAnonymous (not verified)on Mon, 09/27/2010 - 11:32

good day! can you help me about my project. How to connect ms access 2003 to vb thanx
Submitted byAnonymous (not verified)on Mon, 09/27/2010 - 22:26

Gud day sir Please I want to know how I can edit and save records into the database. Please i created a form whereby the user will enter a password and will click on a button and the next form will display, but i have tried "Close() code" but it didn't work, please what should I do? Thanks Regards, Chioma [email protected] reply
Submitted byAnonymous (not verified)on Mon, 10/18/2010 - 23:36

using the open.record.set command in excel VB I want to find the access record to update and then pass the data to the appropriate fields. Here is the code that doesn't work. It appears not to have bugs - but then there is nothing happening - no updates. I should mention that the "AddNew" part works just not the "edit" part that is down further.

Sub Export_tear_data()
'
' Upload Macro
' Macro recorded 11/18/2009 by LAB1
'

Sheets("Summary").Select
Range("A5").Select
Sheets("Summary").Select
ActiveWorkbook.save
'Application.Quit

Dim db As Database, rs As Recordset, r As Long, sa As Recordset

Set db = OpenDatabase("X:\PAPLab\PAPLabTables.mdb")
' open the database
' get all records in a table

'Start Data Transfer
Set sa = db.OpenRecordset("ResultTens", dbOpenTable)
r = 7 ' the start row in the worksheet
Do While Len(Range("A" & r).Value) > 0
' repeat until first empty cell in column A

If Range("Q" & r).Value = "- No -" And Len(Range("c" & r).Value) > 0 Then ' Checks if record exists
'With sa
sa.AddNew
If IsNumeric(Range("B" & r).Value) Then
sa.Fields("ResultTensSampleNo") = Range("B" & r).Value
End If
If IsNumeric(Range("C" & r).Value) Then
sa.Fields("ResultTensElmMDR1") = Range("C" & r).Value
End If
If IsNumeric(Range("D" & r).Value) Then
sa.Fields("ResultTensElmMDR2") = Range("D" & r).Value
End If
If IsNumeric(Range("E" & r).Value) Then
sa.Fields("ResultTensElmMDR3") = Range("E" & r).Value
End If
If IsNumeric(Range("F" & r).Value) Then
sa.Fields("ResultTensElmMDR4") = Range("F" & r).Value
End If
If IsNumeric(Range("G" & r).Value) Then
sa.Fields("ResultTensElmMDR5") = Range("G" & r).Value
End If
If IsNumeric(Range("J" & r).Value) Then
sa.Fields("ResultTensElmTDR1") = Range("J" & r).Value
End If
If IsNumeric(Range("K" & r).Value) Then
sa.Fields("ResultTensElmTDR2") = Range("K" & r).Value
End If
If IsNumeric(Range("L" & r).Value) Then
sa.Fields("ResultTensElmTDR3") = Range("L" & r).Value
End If
If IsNumeric(Range("M" & r).Value) Then
sa.Fields("ResultTensElmTDR4") = Range("M" & r).Value
End If
If IsNumeric(Range("N" & r).Value) Then
sa.Fields("ResultTensElmTDR5") = Range("N" & r).Value
End If

'Upload Arm Weight

If IsNumeric(Range("R" & r).Value) Then
sa.Fields("ResultTensElmMDArm") = Range("R" & r).Value
End If
If IsNumeric(Range("S" & r).Value) Then
sa.Fields("ResultTensElmTDArm") = Range("S" & r).Value
End If

sa.Update
End If

'If record for sample already exists
If Range("Q" & r).Value = "- Yes -" Then

sa.Edit
If IsNumeric(Range("B" & r).Value) Then
sa.Fields("ResultTensSampleNo") = Range("B" & r).Value
End If
If IsNumeric(Range("C" & r).Value) Then
sa.Fields("ResultTensElmMDR1") = Range("C" & r).Value
End If
If IsNumeric(Range("D" & r).Value) Then
sa.Fields("ResultTensElmMDR2") = Range("D" & r).Value
End If
If IsNumeric(Range("E" & r).Value) Then
sa.Fields("ResultTensElmMDR3") = Range("E" & r).Value
End If
If IsNumeric(Range("F" & r).Value) Then
sa.Fields("ResultTensElmMDR4") = Range("F" & r).Value
End If
If IsNumeric(Range("G" & r).Value) Then
sa.Fields("ResultTensElmMDR5") = Range("G" & r).Value
End If
If IsNumeric(Range("J" & r).Value) Then
sa.Fields("ResultTensElmTDR1") = Range("J" & r).Value
End If
If IsNumeric(Range("K" & r).Value) Then
sa.Fields("ResultTensElmTDR2") = Range("K" & r).Value
End If
If IsNumeric(Range("L" & r).Value) Then
sa.Fields("ResultTensElmTDR3") = Range("L" & r).Value
End If
If IsNumeric(Range("M" & r).Value) Then
sa.Fields("ResultTensElmTDR4") = Range("M" & r).Value
End If
If IsNumeric(Range("N" & r).Value) Then
sa.Fields("ResultTensElmTDR5") = Range("N" & r).Value
End If

sa.Update

End If

'MsgBox ("Sample " & Range("A" & r).Value & " already exists in the tensile data table. ")
'DoEvents

sa.MoveLast
r = r + 1 ' next row
Loop
sa.Close
Set sa = Nothing

Submitted byAnonymous (not verified)on Sat, 10/30/2010 - 08:34

Hello. I have wasted a lot of time reading and searching the internet for answers to my apparently simple problem. I remembered your site and, tonight, while searching for answers, I stumble on your site by accident -- thank's to Bing.

Anyway, all the examples I run into don't deal with tables that have Primary Keys. I have a small VisualBasic 2010 application that writes to an MS-Access database (*.mdb). Inserting records works, and I had to search hard how to retrieve the Primary Key after insert completed to be able to have it to use as a Foreign Key in my related table. So, my database is very easy, it has only two tables with a one to many relationship.

When I want to update the records, I use the DataAdapter to fill a DataSet. Then I can display the records easily. But the DataAdapter Update method does not work for me, after I make change the fields and want to persist to the DataSource (my MS-Access database).

I suspect VisualBasic will not update the database unless it is instructed specifically on how to deal with the Primary Key issue? At one point, I suspected I have to first update the child table (even though I make no changes to it) in order for the Update on the Parent table to work. But that does not make sense.

I do use CommandBuilder.

[email protected]

Submitted byAnonymous (not verified)on Tue, 12/21/2010 - 00:14

i need coding of insert ,update, and delete...... in c sharp plzzzzzzzzzzz help

Submitted bycedon Tue, 12/21/2010 - 18:16

look for my work.. i have a simple add,edit in c#...
"simple database manipulation in c#"
point to search engine!!!!!!!!!!!

Submitted byAnonymous (not verified)on Thu, 01/13/2011 - 17:54

I NEED how u add delete records in database
from frontend,in vb.net
pls hepl me for this

my email :[email protected]

Submitted byAnonymous (not verified)on Fri, 01/21/2011 - 22:09

i need delete record from ms access in front end.. pls tel this codngs... [email protected],plz..plz..

Submitted byAnonymous (not verified)on Fri, 01/28/2011 - 21:30

hello any body that can help me pls

I successfully insert the record from my from to the
ms access database using vb6 with the datagrid

the problem is unable to update the autoNumber

and unable to delete and update the record
as needed.

the code that I use for deleting the record is

Dim iResponce As Integer

iResponce = MsgBox("Do you want to Delete this record?", vbOKCancel + vbQuestion, "Delete")
If iResponce = vbOK Then
rs.Delete
txtOffice_code.Text = ""
CboOffice_name.Text = ""
Else

rs.CancelUpdate
txtOffice_code.Enabled = False
CboOffice_name.Enabled = False

this is a single program of all there stuff......................

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace CompleteADO
{
public partial class Form1 : Form
{
SqlDataAdapter da;
DataSet ds;
SqlCommand com;
SqlConnection con;
DataTable dt;
int curr = 0;
int totr = 0;
public Form1()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{
string constring = @"Data Source=DEEPAK-7FD6C5E5\MANISH;Initial Catalog=manishv;Integrated Security=true";
con = new SqlConnection(constring);
con.Open();
com = new SqlCommand("Select * from customer", con);
da = new SqlDataAdapter(com);
ds = new DataSet();
da.Fill(ds, "cust");
dt = ds.Tables["cust"];
curr = 0;
totr = dt.Rows.Count;
fillcontrol();
initlizecomm();
togglecontrol(true);

}

public void fillcontrol()
{
textBox1.Text = dt.Rows[curr]["cid"].ToString();
textBox2.Text = dt.Rows[curr]["nm"].ToString();
textBox3.Text = dt.Rows[curr]["loc"].ToString();
}

private void button2_Click(object sender, EventArgs e)
{
curr++;
if (curr >= totr)
curr = 0;
fillcontrol();
}

private void button3_Click(object sender, EventArgs e)
{
curr--;
if (curr curr = totr - 1;
fillcontrol();
}
public void initlizecomm()
{
da.InsertCommand = con.CreateCommand();
da.InsertCommand .CommandText ="insert into customer"+"(cid,nm,loc)"+"values(@cid,@nm,@loc)";
addparams(da.InsertCommand, "cid", "nm", "loc");

da.UpdateCommand = con.CreateCommand();
da.UpdateCommand.CommandText = "update customer set " + "[email protected],[email protected],[email protected] " + "where [email protected]";
addparams(da.UpdateCommand, "cid", "nm", "loc");

da.DeleteCommand = con.CreateCommand();
da.DeleteCommand.CommandText = "delete customer where [email protected]";
addparams(da.DeleteCommand, "cid");
}

public void addparams(SqlCommand com, params string[] col)
{
foreach (string co in col)
{
com.Parameters.Add("@" + co, SqlDbType.Char, 0, co);
}
}
public void togglecontrol(bool val)
{
textBox1.ReadOnly = val;
textBox2.ReadOnly = val;
textBox3.ReadOnly = val;

button1.Enabled = val;
button2.Enabled = val;
button3.Enabled = val;
button4.Enabled = val;
button5.Enabled = val;
button6.Enabled = val;
button7.Enabled = !val;
button8.Enabled = !val;
}

private void button4_Click(object sender, EventArgs e)
{
togglecontrol(false);
}

private void button7_Click(object sender, EventArgs e)
{
DataRow dr = dt.Rows[curr];
dr.BeginEdit();
dr["cid"] = textBox1.Text;
dr["nm"] = textBox2.Text;
dr["loc"] = textBox3.Text;
dr.EndEdit();
da.Update(ds, "cust");
ds.AcceptChanges();
togglecontrol(true);

}

private void button5_Click(object sender, EventArgs e)
{
DataRow dr = dt.NewRow();
dt.Rows.Add(dr);
totr = dt.Rows.Count;
curr = totr - 1;
dr["cid"] = totr;
textBox1.Text = totr.ToString() ;
textBox2.Text = "";
textBox3.Text = "";
togglecontrol(false);
}

private void button6_Click(object sender, EventArgs e)
{
DataRow dr = dt.Rows[curr];
dr.Delete();
da.Update(ds, "cust");
ds.AcceptChanges();
totr--;
curr = totr - 1;
fillcontrol();

}
}
}

Submitted byAnonymous (not verified)on Sun, 02/13/2011 - 23:24

sir,
I can't find the step by step process in your zip file. please sir, i need the documentation on how to do it from start to finish. Im new in this kind of programming but i really want to learn. im using visual basic in visual studio express 2010. hoping for your kindness. Thank you so much!

Submitted byAnonymous (not verified)on Fri, 03/04/2011 - 16:06

Hi,
I want to make two function on single button(Say Update and diplaying textBox). On first click it should shows textbox, which is panel after click on panel's button(say OK) the first button(Update) run second function(Say it get Updated).
Plz help me. :)

Add new comment

About text formats
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.