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

Submitted by: 
Visitors have accessed this post 365616 times.

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

hi
i want this prject with php and jquery ajax and mysql .

vb.net
add coding in ms access

Hi sir,

I was studying vb.net for just a couple of weeks, i read some online tutorial on basics of vb.net and tried it and it worked. I also tried to connect database from access to vb and i made it. I also made some buttons to show records in my database like next,previous buttons but i stuck on update,delete, and save button. Until i found this site, i already downloaded your file and studied the codes in there.

But my problem is, i dont know the functions of codes in that file, like the syntax "with" "gModule" etc.

im working with our thesis "employee management system" our thesis aims to keep the record of the employee's and we also include attendance.

In access i have a query that computes the total working hours of employee per day
here's the query i use.
Number of Hours: DateDiff("n",[Time-in],[Time-out])/60
But i dont know if VB.net have a syntax or code that updates the current time.

I hope that you can help me sir, i need to understand every piece of codes in your HowtoAddUpdateDeleteRecord file.

thank you

Hello sir,
i tried your codes and i modify it so that i could use it to my project, but when i run my program i got an error says "Failed to connect to Database..System Error Message: Unable to cast object of type 'System.Data.OleDb.OleDbException' to type 'System.Data.OleDb.OleDbDataReader'.

I dont know what to do about this error. I hope that you can help me through it sir.

thank you.

Good afternoon
I'm having the same problem that you're being so have you solved this problem?

Hi Admin
can you make this same Example ,but now with add picture too ??

please help me sir, i want your help as early as possible,please.

Hi,can you make this same sistem but with na image too ??

how to save form data in access
please step by step
simple form like name and address
vb.net & access 2007

i downloaded your code
it works for .mdb database
can u help me with .accdb database format

we're having a project system intitled Pupil's information system..we have a problem in query..how can we connect data we entered, example in pupil's information form to Form137??

when change text to number in database it produce error! any solution?

Finally I a true sample

Am new in .net and programming as well, i would like you help me overcome this challenge. l downloaded your hotel reservation system source code, l have tried to open the "frmAccounts" without success.i did try to edit code by changing reference in WHERE CLAUSE but really l don't even understand the error msg. I quote msg: Run-time error '-2147217365(80040e37)'. The Microsoft Office Access Engine cannot find the input table or query 'Client'. Make sure it exists and that its name is spelled correctly. Please kindly help me out. counting on your usual assistance. thank you.

my project title implementation of mail server using smtp.i have no idea in asp.net with c#.what i do plz help 4 me.

i need a program in vb.net that will change the value of a field or update a field.

i am a new student please help me to program using 2010 and ms access database
my email [email protected]

Hi..

I have a problem here, it's because i'm using a text box to sent data to data base.. why it's display like this?

INSERT INTO [Inventory_Desktop_Laptop(Manual)][Manual_Host_Name],[Location],[Level_Department],[Type],[Manual_Serial_No],[Manual_Make],[Manual_Model],[Manual_RAM],[HDD],[Owner],[Maint_Expiry],[Received],[Monitor],[Safeboot],[Remark],[Agreement],[Lease_Co],[Lease_From],[Lease_To],[Lease_Period],[Original_Cost],[Status],[Chronology]) VALUES('','','6','6','','','','','','','','','','','','','','','','','','','','')System.Data.SqlClient.SqlException: Incorrect syntax near 'Manual_Host_Name'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at AddManual.Submit1_Click(Object sender, EventArgs e) in C:\Documents and Settings\erizikri.wahab\My Documents\Visual Studio 2008\WebSites\WebSite1\AddManual.aspx.vb:line 71

so, i hope that you can help me by give me some code or what ever.. =D

thx and regard,
woolaa.

thanks your d best hahah...

rosylicius21

I'm currently developing a sample system wherein the primary key (supposedly id) must be used only once (of course, at it should be)so that there will be no duplicate entries.. I am using vb 2010 and ms access and I'm having problems with the oledbdatareader and the executereader.. I'll just check first your article "Database Programming Made Easy" since MS Access is my database application.. please do comment back if there's any more article I should look for .. thqank you! :)

hi,

i've problem when i running my setup(make with vb6) in other machine without vb6 installed in that machine,because the database path is different,
do u have solution for my problem??
thx

hello,,i have trouble to run my setup/exe in other machine/computer because the database in invalid path,,so my question:

how to run my app in other machine without vb or vb.net installed in that machine??
i realy need your help..

i m doing food ordering system and i wan to connect it to ms. access... sumore wen i change food name at the database its nt changing in vb.net... help me... and i aso wan code for update my final result in database back.... tq......

Where can i see the step by step?

HI can help you help mi put all of the info in my listview into database?
I already know how to put data at my database using textbox but it will only put one items at a time, so what i did i put all the items in listview but i cant put all items in the listview in my database... do please help me, I'm making a POS, the idea is when the customer buys multiples items, the items i clik in datagrid will be put at my listview but I dont know how to put all the items in my listview into the database..
here's where i dont know what todo

Dim ca As New OleDb.OleDbCommandBuilder(dapractice)
Dim dsNewRow As DataRow

dsNewRow = dspractice.Tables("Practice").NewRow()
dsNewRow.Item("Item") = ListView1."I need all of my multiple items in a column to be put in database"
dsNewRow.Item("Stock") = ListView1.

dspractice.Tables("Practice").Rows.Add(dsNewRow)
dapractice.Update(dspractice, "Practice")

Hi, I run the code in vs2010 and am getting an error here "FillListView(lvList, GetData(sSql))" with the exception "System.InvalidCastException was unhandled
Message=Unable to cast object of type 'System.Data.OleDb.OleDbException' to type 'System.Data.OleDb.OleDbDataReader'." I don't know what the problem is.

awesome

can you help me that program will shows when you add it shows that has already a details

cn.Open()
Dim sql As String
sql = "update eform set name = '" & TextBox2.Text & "',address = '" & TextBox3.Text & "',contact = '" & TextBox4.Text & "',dob = '" & TextBox5.Text & "',post = '" & TextBox6.Text & "',depart = '" & ComboBox1.Text & "',head = '" & TextBox7.Text & "',payscale = '" & TextBox8.Text & "',date = '" & TextBox9.Text & "'eid = '" & TextBox1.Text & "'"
Dim cmd As New OleDbCommand(sql, cn)
cmd.ExecuteNonQuery()
MsgBox("Data updated")
cn.Close()
Sir where is the syntax error in this program

we have an application where i need to run select query for more than 30 to 40 times in different location.the problem which i was facing is for some times i was unable to fetch data from database it was returning null

Dear Admin,
i copied ur code for adding record in database. i've a strange situation, the code works fine, and i've no eny errors but as i run my app and add new record. i've no any error but for checking my record i open my table, i dont see my record. where i can b wrong? heres my code

  1. Private Sub NewPasswordTextBox_Enter(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NewPasswordTextBox.Enter
  2. OleDbConnection1.Open()
  3. cb = New OleDbCommand("select * from Administrator", OleDbConnection1)
  4. dr = cb.ExecuteReader()
  5. '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  6. If OldPasswordTextBox.Text.Equals(NewPasswordTextBox.Text) Then
  7. If OldPassword.Text.Equals("Old Password") Then
  8. While dr.Read
  9. If Username2TextBox.Text.Equals(dr("UserName").ToString) Then
  10. Dim dt As DataTable = ds.Tables("Administrator")
  11. dt.Rows(0)("Password") = NewPasswordTextBox.Text
  12. End If
  13. End While
  14. ElseIf OldPassword.Text.Equals("Password") Then
  15. Dim dt As DataTable = ds.Tables("Administrator")
  16. Dim dr = dt.NewRow
  17. Dim bm = Me.BindingContext(dt)
  18. dr.Item("UserName") = Username2TextBox.Text
  19. dr.Item("Password") = NewPasswordTextBox.Text
  20. OleDbDataAdapter1.Fill(ds, "Administrator")
  21. dt = ds.Tables("Administrator")
  22. dt.Rows.Add(dr)
  23. dt.AcceptChanges()
  24. OleDbDataAdapter1.Update(dt)
  25. End If
  26. End If
  27. OleDbConnection1.Close()
  28. End Sub
  29. <vb>

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.

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

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

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.

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. :)

Can any one help give me a sample code for connecting vb 2008 EXpress Edition to MS Access 2007 and 2003 for these operations add, update, save, delete and find.

code could be posted or sent to this email: [email protected]

Thanks

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]

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 < 550 Then Me.Width = 550
If Me.Height < 250 Then Me.Height = 250
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

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????

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]

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

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

great hand... thanks guys

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.

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.

Please send me you sample code

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..
:)

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]

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

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

Pages

Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • You may insert videos with [video:URL]
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <th> <img> <h1> <h2> <h3> <iframe> [video]
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <asp>, <c>, <cpp>, <csharp>, <css>, <html4strict>, <java>, <javascript>, <mysql>, <php>, <python>, <sql>, <vb>, <vbnet>. The supported tag styles are: <foo>, [foo].
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.