Databases - MS Access - Modifying Tables

In order to modify the data in a table, you need to use a RecordSet.

The help says that a table must contain a unique index in order to be modified via code. This may not be true for tables in the current database (the current mdb file).

Opening a RecordSet

Dim rsc As Recordset
Dim Some_Recordset As Recordset
  
  Set rsc = Form.RecordsetClone
  Set Some_Recordset = rsc.Clone
  Set Some_Recordset = CurrentDb.OpenRecordset( _
                         "TableName", dbOpenTable)

You must use the Set command. Some variables contain data and others are pointers (references, addresses) to data. Set assigns a reference and is always required.

Speed

When possible, open the recordset once, use it a lot, and then close it.

I did an experiment of adding about 100 records to 4 different tables. In one trial, I opened the recordsets for each addition; in the other, I opened each recordset once, added all the records, then closed everything. The difference - 2 seconds verses 50 seconds.

RecordSet Commands

OpenRecordset | Close

AddNew | Edit | Delete
Update

FindFirst | FindLast | FindNext | FindPrevious
MoveFirst | MoveLast | MoveNext | MovePrevious

NoMatch | BOF | EOF

AddNew and Edit both require an Update in order to save the changes. Moving to another record or closing the RecordSet before executing an Update will cause the changes to be lost.

The help implies that Delete also requires an Update, but, in fact, it doesn't.

The Find commands only work with dynaset- or snapshot-type Recordsets. They also require you to enter a search criteria.

rsc.FindFirst "[Field1]='SomeString'"
rsc.FindFirst "[Field1]='SomeString' and [Field2] like 'xyz*'" 
rsc.FindFirst "[Field1] = """ & Element_Type_ID & """"

Either single quotes or double quotes can be used.

RecordSet Properties

.Bookmark = .LastModified  (from the AddNew Method Example)

Copy and Paste

These are working examples.

Private Sub SaveData()
Dim rsc As Recordset

  If KeyField = "" Then Exit Sub
  
 ' Exit Sub   ' Used to stub this routine when 
              '  debugging other routines
  
  Set rsc = Form.RecordsetClone
  rsc.FindFirst "[Field1]='" & KeyField & "'"
  
  If rsc.NoMatch Then
   rsc.AddNew
   rsc![Field1] = KeyField
   rsc![Field2] = "Some String"
   rsc![Field3] = SomeNumber
   rsc.Update
  Else
   rsc.Edit
   rsc![Field2] = "Some String"
   rsc![Field3] = SomeNumber
   rsc.Update
  End If

End Sub

With and Delete

This code creates n identical labels starting in the specified row and column. It demonstrates using the With and Delete commands.

Notice the use of _UIEdit in the "variable" names - I use this to differentiate between Form components and variables.

Sub Build_Page()
 Dim Some_Recordset As Recordset
 Dim LabelCount
 Dim i

 Set Some_Recordset = CurrentDb.OpenRecordset( _
                         "Avery 5267 - Data Table", dbOpenTable)
 
 LabelCount = 0
 
 With Some_Recordset
     ' Delete existing records
   Do While Not .EOF
     .Delete
     .MoveNext
   Loop
   
     ' Create new records
   For i = 1 To (Row_UIEdit - 1) * 4 + Column_UIEdit - 1
    .AddNew        ' Blank records to skip labels
  '  !Line1 = ""
    .Update
   Next i
   
   For i = 1 To Nz(Number_of_Labels_UIEdit)
    .AddNew
    !Line1 = Row1_UIEdit
    !Line2 = Row2_UIEdit
    !Line3 = Row3_UIEdit
    !Line4 = Row4_UIEdit
    .Update
    LabelCount = LabelCount + 1
   Next i
 End With

Exit_Build_Page:
 Some_Recordset.Close
 LabelCount_UILabel.Caption = LabelCount

End Sub

Author: Robert Clemenzi - clemenzi@cpcug.org