MS Access/ADO AddNew method not appending record

Question

Hi,

I'm using ADO 2.1 in MS Access 2003 and calling the AddNew method of an ADO recordset using an array of field names and an array of values. I am getting no error messages however, the record is not being written to the table.

I have tried following the command with a .Update and a .Requery to no avail.

Any ideas?

Public Function ReadFileViaTextStream(ByVal PortfolioName As String, ByVal SourceFile As String, ByVal TargetTable As String, _
                                                    ByRef TargetFields(), ParamArray SourceFields() As Variant)



Dim p_adoRS As ADODB.Recordset
Dim ForWriting() As Variant

Set p_adoRS = New ADODB.Recordset

p_adoRS.Open TargetTable, CurrentProject.Connection, adOpenDynamic, adLockBatchOptimistic, adCmdTable

If p_adoRS.Supports(adAddNew) Then
    p_adoRS.AddNew TargetFields(), ForWriting()
    p_adoRS.Update
    p_adoRS.Requery
End If

TargetTable is a string parameter, TargetFields is an array of field names and ForWriting is an array of values.

Answer 1

Right - Myself and a colleague have found the issue. The recordset should be opened as adLockOptimistic, not adLockBatchOptimistic.