Skip to main content

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

Language


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 Thu, 03/17/2011 - 10:34

It was really useful, thank you for teach us how to do this. :D

Submitted byAnonymous (not verified)on Sun, 03/20/2011 - 16:09

dear sir, am added to the records in access ... but i want to change and show records one by one please reply me ...

thanking you always

Submitted byAnonymous (not verified)on Mon, 03/21/2011 - 17:35

I hv made connection with access database successfully. I can Add rows also successfully but unable to update the table.
I can show data in datagridview and want to add new, update, delete the selected rows.
I can add and delete successfully but unable to update the selected rows within database.
I can use the flag property in VB.net. i can add rows with its value property false and want to update the selected row on its value true.
Please help me asap. on the e-mail id
[email protected]

Thanks & regards
Shilpi garg

Submitted byAnonymous (not verified)on Mon, 03/21/2011 - 17:55

strDeleted = ExecNonQuery("DELETE Customers.CustomerID FROM Customers WHERE CustomerID= '" & CustomerID & "'")

please..

the 2 "Customers" is the name of the table in MS ACCESS ryt?

how about the three "CustomerID"?

Submitted byAnonymous (not verified)on Fri, 04/29/2011 - 18:05

In reply to by Anonymous (not verified)

u should mention delete tablename from table where condition thats all

and at last one more single code is not necessary

ok;....

Submitted byAnonymous (not verified)on Mon, 08/08/2011 - 08:47

In reply to by Anonymous (not verified)

tutorial for vb.net 2008
first import the following in the reference:
3 files of adodb reference files.

Start coding:
Dim dc As New ADODB.Connection
Dim rs As New ADODB.Recordset
dc.Open("Provider = microsoft.jet.oledb.4.0; data source = filename.mdb")
rs.Open("tablefilename", dc, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic)
rs.MoveFirst()
rs.AddNew()

rs.Fields("txtsample").Value = txtsample.Text

rs.Update()
rs.Close()
dc.Close()

Submitted byAnonymous (not verified)on Wed, 05/04/2011 - 16:13

matindi tlga ang mga programmer,,,astig...paturo nga..tsk..
gradauting n lmang aq,,haizt

Submitted byAnonymous (not verified)on Mon, 05/09/2011 - 10:07

In reply to by Anonymous (not verified)

wahahaha. .Dota na lng tayo.. yan kc.

Submitted byAnonymous (not verified)on Thu, 05/12/2011 - 14:24

In reply to by Anonymous (not verified)

maganda yung parehong kang magaling.. magaling ka mag dota then magaling ka rin mag programming.. dobol kill yun! eh ang dota gawa yan sa VB platform

Submitted byAnonymous (not verified)on Fri, 05/13/2011 - 18:57

hai
plz provide me the code for reading the values from textbox in the vb form and get saved to ms access data base
plz send me the code
to [email protected]

Submitted byAnonymous (not verified)on Wed, 06/15/2011 - 13:07

Hi,
I need code for add,edit,delete,update records in Access Database using C#.
Plz help me as early as possible. plz provide code me on my mail id: [email protected]

Thank You.

Submitted bylbraeckmon Wed, 06/22/2011 - 04:25

Hello,

your example helped me a lot.
However, I'm facing a problem:
I did a query according:

  1. Dim sqlQRY As String = "SELECT * FROM JSM_LEDEN WHERE Kaartnummer = " & IntCardNr
  2.  
  3. Dim JSM_DataAdapter As OleDbDataAdapter = New OleDbDataAdapter(sqlQRY, JSM_Conn)
  4.  
  5. Dim JSM_DataSet As DataSet = New DataSet
  6.  
  7. JSM_DataAdapter.Fill(JSM_DataSet, "JSM_Leden")
  8.  
  9. Dim JSM_DataTable As DataTable = JSM_DataSet.Tables("JSM_Leden")
  10.  
  11. Dim row As DataRow
  12. Dim NumLessons As Integer = 0
  13. Dim LastLogDate As Date

.. as the IntCardNr variable is a unique number, the query results in a single row from my database following code displays it:

  1. For Each row In JSM_DataTable.Rows
  2. Txt_Name.Text = row("Naam")
  3. Txt_Forename.Text = row("Voornaam")
  4. Txt_Address.Text = row("Straat")
  5. Txt_City.Text = row("Gemeente")
  6. Txt_Zip.Text = row("Postcode")
  7.  
  8. If IsDBNull(row("Tel")) Then
  9. Txt_Phone.Text = ""
  10. Else
  11. Txt_Phone.Text = row("Tel")
  12. End If
  13.  
  14. If IsDBNull(row("GSM")) Then
  15. Txt_GSM.Text = ""
  16. Else
  17. Txt_GSM.Text = row("GSM")
  18. End If

Now I want to update a couple of fields (LastLog and NumLessons)

  1. If DateDiff(DateInterval.DayOfYear, CurrentDate, LastLogDate) Then
  2. NumLessons = Val(row("Aantal_Lessen")) + 1
  3. 'Update the database
  4. End If

what would be the best approach to achieve this?

Submitted byAnonymous (not verified)on Thu, 06/23/2011 - 13:12

Hi Master in VB.Net and SQL Server..

Please help me i cannot add new data in sql server as my backend only update are working...

below is my code:

Imports System.Data.SqlClient
Public Class frmUsers
Private cs As New SqlConnection("Data Source = PC_ROB\SQLEXPRESS;Initial Catalog=GTIGOLDEN;Integrated Security=True")
Private da As New SqlDataAdapter("SELECT * FROM tblUsers", cs)
Private ds As New DataSet
Private cmb As New SqlCommandBuilder(da)
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Me.Top = 200
Me.Left = 200
Me.WindowState = 0
cs.Open()
da.Fill(ds, "tblClients")
dg.DataSource = ds.Tables("tblClients")
cs.Close()
End Sub
Private Sub btnClose_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
Me.Close()
End Sub
Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
Dim x As Integer
da.UpdateCommand = cmb.GetUpdateCommand
x = da.Update(ds.Tables("tblUsers"))
MsgBox(x & " Record(s) Updated")
End Sub
Private Sub btnadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnadd.Click
Dim sqlcom As New SqlCommand
sqlcom.Connection = cs

If u_txtempid.Text "" And u_txtuname.Text "" And u_txtempname.Text "" And u_txtupos.Text "" And u_cmbacc "" And u_txtpass.Text "" Then
Call Confields()
dg.Update()
MsgBox("New User has been added., MsgBoxStyle.Information")
End If
Try
Catch ex As Exception
End Try
da.Fill(ds, "tblClients")
dg.DataSource = ds.Tables("tblClients")
End Sub
Private Sub Confields()
dg.DataSource!EmployeeID = u_txtempid.Text
dg.DataSource!UserName = u_txtuname.Text
dg.DataSource!EmployeeName = u_txtempname.Text
dg.DataSource!Position = u_txtupos.Text
dg.DataSource!AccessLevel = u_cmbacc.Text
dg.DataSource!Password = u_txtpass.Text
End Sub
Private Sub Retfields()
u_txtempid.Text = dg.DataSource!EmployeeID
u_txtuname.Text = dg.DataSource!UserName
u_txtempname.Text = dg.DataSource!EmployeeName
u_txtupos.Text = dg.DataSource!Position
u_cmbacc.Text = dg.DataSource!AccessLevel
u_txtpass.Text = dg.DataSource!Password
End Sub
End Class

Submitted byAnonymous (not verified)on Thu, 06/23/2011 - 17:26

i need codes for log in system in c#.net based from ms access database. the user can log in based from the registered/saved username and password in ms access database. i urgently need it. please help. Thanks. T_T...

Submitted byjackyon Sat, 06/25/2011 - 13:38

hi...i am new in programming....recently i have download your coding and try to edit or modify it to the program i want....lastly i successful modify it but it can shown the data insert in the list view but the data in successfully insert to ms access d. Can you please help me troubleshoot it?? thanks a lot:)

[email protected]

Submitted byAnonymous (not verified)on Sun, 07/17/2011 - 13:55

hi............
i need to do library management system using c++ with the backend ms access... can any one tell me how to connect ms access with the c++ coding for library management system

Submitted byAnonymous (not verified)on Sat, 07/23/2011 - 22:16

arrggghhhh* ang sakit sa ulo magprogram.. ang hirap di ko magawa, saan ba ko magsisimula. patulong naman anu bang magandang gamitin na software para sa payroll system? beginner palang ako sa paggawa ng program, any suggestions? tnx a lot :D

Submitted byAnonymous (not verified)on Mon, 07/25/2011 - 17:08

I'm using VB.NET 2008 my update code is like that how can tell me the error

Try
If MsgBox("Are You Sure You Wish To Delete " & txtuserid.Text & "?", vbYesNo + vbQuestion, "Delete Record?") = vbYes Then
myconn.Open()
Dim str As String = "delete from useraccount where Userid='" & txtuserid.Text & "'"
Dim cmd As New SqlCommand(str, myconn)
cmd.ExecuteNonQuery()
'Display Success Message
MsgBox("The Record Has Been Deleted Successfully!", vbInformation, "Successful Delete Procedure!")
c.clearallfields(grp.Controls) 'Calling a Private Function To Clear All Fields
Else
'Display 'Delete Procedure Cancelled' Message
MsgBox("The Delete Procedure Was Cancelled!", vbExclamation, "Delete Procedure Cancelled!")
c.clearallfields(grp.Controls) 'Calling a Private Function To Clear All Fields
End If

Me.Close()
MDIParent1.Button3.PerformClick()
myconn.Close()
Catch ex As Exception
MsgBox(ex.Message)
End Try

Submitted byAnonymous (not verified)on Fri, 09/09/2011 - 10:45

In reply to by Anonymous (not verified)

//hello i ned the codes for adding and searching editing and updating plz// thanks!!!

Submitted byAnonymous (not verified)on Tue, 07/26/2011 - 00:48

thank you

Submitted byAnonymous (not verified)on Tue, 08/02/2011 - 08:22

i downloaded your code, studied and copied it but i changed the variables.. but when i run my program it always shows an error..

It says. Unable to cast object of type 'System.Data.OleDb.OleDbException' to type 'System.Data.OleDb.OleDbDataReader'...

What do you think is the problem? The error came from the module form, in the FillListView(lvVehicles, GetData(sSql)) line..

thanks you very much..

Submitted byAnonymous (not verified)on Sun, 10/16/2011 - 00:46

In reply to by Anonymous (not verified)

it's an error from ur compilation

hi.. i downloaded your project.. as i study it, i created my own project and copied your code but change the variables and the database.. as i debugged the codes an error came out.. i checked the codes from my project to yours and found no errors..

this is what it said..

Unable to cast object of type 'System.Data.OleDb.OleDbException' to type 'System.Data.OleDb.OleDbDataReader'.

and it's pointing from the module form "FillListView(lvVehicles, GetData(sSql))" line..

thank you very much..

Submitted byAnonymous (not verified)on Mon, 08/08/2011 - 13:18

Dear,

I have a software in access 2003 this software is showing all the records when run in windows XP, When i try to upgrade it in server 2008/ windows seven with same size of file it shows 1912 record. please hep to solve this one

Submitted byAnonymous (not verified)on Sun, 08/21/2011 - 15:04

plizzz i need coding to connect my system to database using barcode scanner..i need the code..pliz help me..

Submitted byAnonymous (not verified)on Tue, 08/23/2011 - 14:39

Hello everybody, can i ask for something? how can i connect my database in vb? I'm using a 2007 MS Access. One last question, how can i put a code in save command button? i need help, please.... :) here's my email add... [email protected]

Submitted byAnonymous (not verified)on Tue, 08/23/2011 - 14:48

good day sir, can you please provide me some code for Add, save, and edit button. and Sir, can you please give me some instruction how can i connect database in vb? I'm using 2007 MS Access. I already created a program, but i did not know how can i connect the database into my program..please help me Sir. here's my email add.. [email protected]

Thanks. I'm looking forward for your response soonest..
:)

Submitted byAnonymous (not verified)on Thu, 08/25/2011 - 01:57

dear sir ,

I want a code for code for delete data from record in my project when i deleted any record from flexgrid then only last data is deleted but i wantonly those data is deleted whose that i select.

Submitted byAnonymous (not verified)on Sat, 10/15/2011 - 23:49

In reply to by Anonymous (not verified)

Please send me you sample code

Submitted byAnonymous (not verified)on Thu, 08/25/2011 - 14:19

H!..pls help me in solving my problem in java...
i need the codes that you can insert,delete and change your record from a file using JAVA.

Submitted byAnonymous (not verified)on Tue, 08/30/2011 - 17:34

great hand... thanks guys

Submitted byAnonymous (not verified)on Thu, 09/08/2011 - 15:34

Private Sub a()
If ListBox1.Items.Count > 0 Then
ListBox1.SelectedIndex = 0
For i = 0 To ListBox1.Items.Count - 1
Dim t As String = Mid(ListBox1.Text, 1, TextBox1.Text.Length)
If TextBox1.Text.ToUpper t.ToUpper Then
If ListBox1.SelectedIndex = ListBox1.Items.Count - 1 Then
ListBox1.SelectedIndex = -1
End If
ListBox1.SelectedIndex = ListBox1.SelectedIndex + 1
Else
Exit For
End If
Next
End If
End Sub

but it take too much time with long database pls help me

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

how to add,delete,update oracle9i or access database2003 in vb.net2003

Submitted byAnonymous (not verified)on Sat, 09/10/2011 - 22:31

I Need To feed data to EXCELL from Access, So Excell should Calculate the result from the data Recieved through Access.. But i cant link a Access file To Worksheet for auto Updation...

On otherhand If i link a Spreadsheet to Access as a table means i cant append data in Access in that table..

So i need a solution for autoupdation of data in excell that i entered in Access table..
Please help me My email: [email protected]

Submitted byAnonymous (not verified)on Thu, 09/15/2011 - 14:19

hi
i'm write code in vb.net and use the database as ms access,now how can i search the record from database by using textbox????

Submitted byAnonymous (not verified)on Tue, 09/20/2011 - 18:48

LIST VIEW FORM CODE
Imports System.Data.OleDb
Imports System.Data.OleDb.OleDbConnection

Public Class frmDReport
Dim sSQL As String
Private Sub frmDReport_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
sSQL = "SELECT RecivedDate, Pump, SlipNo, Quantity, InitialPReading, FinalPReading, PickUpVihical, VihicalNo, DriverName, CustomerName, SiteName, DieselInLit, CurrentRate FROM Diesel_Report ORDER BY RecivedDate ASC"
Call FillList()
End Sub

Public Sub FillListView(ByRef lvList As ListView, ByRef myData As OleDbDataReader)
Dim itmListItem As ListViewItem
Dim strValue As String
Do While myData.Read
itmListItem = New ListViewItem()
strValue = IIf(myData.IsDBNull(0), "", myData.GetValue(0))
itmListItem.Text = strValue

For shtCntr = 1 To myData.FieldCount() - 1
If myData.IsDBNull(shtCntr) Then
itmListItem.SubItems.Add("")
Else
itmListItem.SubItems.Add(myData(shtCntr).ToString())
'itmListItem.SubItems.Add(myData.GetString(shtCntr)) '// Sugested by Chetan Makadia
End If
Next shtCntr
lvList.Items.Add(itmListItem)
Loop
End Sub

Private Sub lvList_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles lvList.DoubleClick
Dim RecivedDate As String
For Each sItem As ListViewItem In lvList.SelectedItems
RecivedDate = sItem.Text
Next
With frmDAdd
.State = CustomerModule.FormState.adStateEditMode
.RecivedDate = RecivedDate
.ShowDialog()
Call FillList()
End With
frmDAdd = Nothing
End Sub

Private Sub frmDReport_Resize(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Resize
If Me.WindowState FormWindowState.Minimized Then
If Me.Width If Me.Height lvList.Height = Me.Height - 96
lvList.Width = Me.Width - 10
End If
End Sub

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
Dim RecivedDate As String
Dim Diesel As New frmDAdd
Diesel.State = CustomerModule.FormState.adStateAddMode
For Each sItem As ListViewItem In lvList.SelectedItems
RecivedDate = sItem.Text
Next
frmDAdd.RecivedDate = RecivedDate
frmDAdd.ShowDialog()
Call FillList()
End Sub

Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
Dim RecivedDate As String
For Each sItem As ListViewItem In lvList.SelectedItems
RecivedDate = sItem.Text
Next
If RecivedDate "" Then 'Delete the selected record
Dim strDeleted As Boolean
strDeleted = ExecNonQuery("DELETE Diesel_Report.RecivedDate FROM Diesel_Report WHERE RecivedDate= '" & RecivedDate & "'")
'strDeleted = ExecNonQuery("DELETE RecivedDate FROM Diesel_Report WHERE RecivedDate = '" & RecivedDate & "'")
If strDeleted = "True" Then
MsgBox("Record's deleted.", MsgBoxStyle.Information)
Call FillList()
Else
MsgBox(strDeleted)
End If
Else
MsgBox("Please select record to delete.", MsgBoxStyle.Critical)
End If
End Sub

Private Sub FillList()
With lvList
.Clear()
.View = View.Details
.FullRowSelect = True
.GridLines = True
.Columns.Add("RecivedDate", 60)
.Columns.Add("Pump", 100)
.Columns.Add("SlipNo", 50)
.Columns.Add("Quantity", 50)
.Columns.Add("InitialPReading", 60)
.Columns.Add("FinalPReading", 60)
.Columns.Add("PickUpVihical", 80)
.Columns.Add("VihicalNo", 80)
.Columns.Add("DriverName", 100)
.Columns.Add("CustomerName", 100)
.Columns.Add("SiteName", 100)
.Columns.Add("DieselInLit", 80)
.Columns.Add("CurrentRate", 50)

FillListView(lvList, GetData(sSQL))
End With
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
sSQL = frmDCond.ShowDialog()
Call FillList()
End Sub
End Class

Submitted byAnonymous (not verified)on Sat, 10/08/2011 - 23:17

Does any1 like to help me to code for the add, update nd delete command of the vb.net 2008 for oracles database....plz help me...i refered 2 books bt i could not get any thing from dat....plz mail me on [email protected]

Submitted byJonas (not verified)on Tue, 01/10/2012 - 15:17

Can I ask for a help in creating database using phpmyadmin? As well us on how to view, search, add, and delete the stored items on the database? If it will be okay. :)

Submitted byRODOLFO ACEBEDO (not verified)on Wed, 01/11/2012 - 13:41

GOOD DAY!

SIR, can you help me in my thesis about the student record information system.. Can you help me on how to code this kind of system. I will appreciate if you can help me in this matter..GOD BLESS AND MORE POWER.

Submitted bySofia Helen Gabarda (not verified)on Wed, 01/11/2012 - 14:04

good day..
can you help me to code delete,search,add and save...plz mail me on [email protected] reply..

Submitted byshann (not verified)on Fri, 01/20/2012 - 19:34

sir can this also be done on data grid.. i min instead of using listbox ill use a data grid.. can you teach me sir how to do it.. thank you.

Submitted byrhod patrick v… (not verified)on Wed, 01/25/2012 - 16:36

In reply to by shann (not verified)

just call me 09477367941 OR EMAIL me..to teach you on how to connect and processing in GRID

Add new comment

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