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.
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.
OpenRecordset | Close AddNew | Edit | Delete Update FindFirst | FindLast | FindNext | FindPrevious MoveFirst | MoveLast | MoveNext | MovePrevious NoMatch | BOF | EOFAddNew 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.
.Bookmark = .LastModified (from the AddNew Method Example)
Copy and Paste
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
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