Access Database Tables Manipulation with VBA

Sub ReadQuantity()

'Variable Declarations
'Orders is database table name
Set myR = CurrentDb.OpenRecordset("Orders")
'Loop Could be used to iterate over column
record = myR![Quantity]

End Sub

Read Values of Whole Column

'Make sure their are no null values in the table
Private Sub Command26_Click()
Dim myR As Recordset
Set myR = CurrentDb.OpenRecordset("Sale Orders")
Do Until myR.EOF
 MsgBox myR![Unit of Measurement]
 myR.MoveNext
Loop
End Sub

Add New Record

Option Compare Database

Sub AddNewRecored()
Dim myR As Recordset
Set myR = CurrentDb.OpenRecordset("Sale Orders")
myR.AddNew
myR![CustNum] = myR.RecordCount + 1
myR![CustFirstName] = "Curtis"
myR![CustLastName] = "Frye"
myR.Update

End Sub

Use of Transaction/Workspaces

Display Table Properties

using TableDef Object

Closing Tables Properly

Option Compare Database
Sub CloseTable()
Dim myR As Recordset
Set myR = CurrentDb.OpenRecordset("Sale Orders")
MsgBox "Number of Orders is :" & myR.RecordCount & "."
myR.Close
Set myR = Nothing

End Sub

Find and Delete the Record in Tables

'Keep more data then you need, think thrice before deleting any record
Option Compare Database
Sub DeleteRecord()
Dim myR As Recordset
Set myR = CurrentDb.OpenRecordset("Sale Orders", dbOpenDynaset)
myR.FindFirst "[Product] = 'Test'"
myR.Delete

MsgBox("Record deleted.")

myR.Close
Set myR - Nothing
End Sub