AddNew in Access VBA does not add a new row or report any error

Question

I am importing the rows of a CSV file into an Access table using the following code:

Set readingSet = CurrentDb.OpenRecordset("decibellog", dbOpenDynaset)

abortImport = False
fileHandle = FreeFile()                                 ' Get the next available file handle.
lineImportCount = 0

Open fileSpec For Input As #fileHandle                  ' Open the decibel logger file.

While Not EOF(fileHandle) And Not abortImport           ' While not at End-Of-File (EOF).

    Line Input #fileHandle, fileLine                    ' Read a line from the decibel logger file.
    lineArray = Split(fileLine, vbTab)                  ' Parse the line into an Array.
    
    If lineArray(0) = "Place" Then
    
        ' Skip this header line.
    Else
    
        readingSet.AddNew                               ' Append a new record to the DecibelLog table.
        
        readingSet!jobinstrumentimportid = jobInstImportId
        readingSet!readingdate = DateValue(lineArray(1) & " " & lineArray(2))
        readingSet!readingtime = lineArray(2)
        readingSet!decibelreading = CDbl(lineArray(3))
        readingSet!dbweighting = lineArray(4)
        
        lineImportCount = lineImportCount + 1
                   
    End If
    
    DoEvents                                            ' Yield to operating system.
    
Wend

readingSet.Close                                        ' Close the decibelreading dataset.
Set readingSet = Nothing

The readingSet.AddNew command does not add a new row to the decibellog table.
No error is thrown.
The RecordCount property of readingSet never increments. No rows are added to the table.
Do I need to use a SQL INSERT instead of .AddNew?

You should close the fileHandle after the While loop, otherwise the file will stay open. — Olivier Jacot-Descombes, Aug 13 '21 at 14:39
You should avoid the bang operator when it's not really needed because https://stackoverflow.com/questions/2923957/bang-notation-and-dot-notation-in-vba-and-ms-access — HackSlash, Aug 13 '21 at 15:25
`While...Wend` is deprecated in favor of `Do While...Loop`, the former being included for backwards compatibility with legacy codebase — HackSlash, Aug 13 '21 at 15:26
To control object scope I recommend using a [With Block](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/with-statement) in place of your `readingSet` variable. This makes the code more clear and concise. — HackSlash, Aug 13 '21 at 15:28

score 4 · Accepted Answer · answered Aug 13 '21 at 14:21

You need to add

readingSet.Update

after writing to the fields, in order to save the new record.

See https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/recordset-addnew-method-dao

After you modify the new record, use the Update method to save the changes and add the record to the Recordset. No changes occur in the database until you use the Update method.

If you issue an AddNew and then perform any operation that moves to another record, but without using Update, your changes are lost without warning. In addition, if you close the Recordset or end the procedure that declares the Recordset or its Database object, the new record is discarded without warning.

AddNew in Access VBA does not add a new row or report any error

1 Answers1