>> adodb recordset.addNew

[Jonathan]

Hi using Access 2003. I would like to use a generic function to insert a new
record and return the id of the new record. a parameter is the field list and
another is the new values.

Public Function SaveRecordGetNewID(TableName As String, IdField As String,
FieldList As String, Values As String) As Long

The following code snipet triggers an error at the add new line.

With rst
Set .ActiveConnection = conn
.CursorLocation = adUseServer
.CursorType = adOpenDynamic
.LockType = adLockBatchOptimistic
.Source = TableName
.Open
.AddNew FieldList, Values
recordID = .Fields(IdField)
.Close
End With

FieldList = "dbCode,dbvVersion,dbvBuild"
Values = "'xxx',999,0"

The error number is 3265, Item not found in this collection. All 3 fields
exist in the table

If I reduce the field list and values to a single field the error number is
-2147217887, Application-defined or object-defined error.

Changing the cursor location and lock type has not resolved this.

Any ideas or suggestions appreciated
Many thanks,
Jonathan

 

[Dirk Goldgar]

Hi using Access 2003. I would like to use a generic function to insert a
new
record and return the id of the new record. a parameter is the field list
and
another is the new values.

Public Function SaveRecordGetNewID(TableName As String, IdField As String,
FieldList As String, Values As String) As Long

The following code snipet triggers an error at the add new line.

With rst
Set .ActiveConnection = conn
.CursorLocation = adUseServer
.CursorType = adOpenDynamic
.LockType = adLockBatchOptimistic
.Source = TableName
.Open
.AddNew FieldList, Values
recordID = .Fields(IdField)
.Close
End With

FieldList = "dbCode,dbvVersion,dbvBuild"
Values = "'xxx',999,0"

The error number is 3265, Item not found in this collection. All 3 fields
exist in the table

If I reduce the field list and values to a single field the error number
is
-2147217887, Application-defined or object-defined error.

Changing the cursor location and lock type has not resolved this.

Any ideas or suggestions appreciated

The problem is that the ADO AddNew method doesn't want to receive a strings
containing delimited lists; it wants to receive arrays of Variants. So
each of your arguments FieldList and Values must be converted to an array of
Variants. I can't help thinking there must be a better way to do it than
this, but here's one way:

'----- start of code snippet -----
Dim aFields() As String
Dim aValues() As String

Dim avFields() As Variant
Dim avValues() As Variant

Dim I As Long

aFields = Split(FieldList, ",")
aValues = Split(Values, ",")

ReDim avFields(UBound(aFields))
ReDim avValues(UBound(aValues))

For I = LBound(aFields) To UBound(aFields)
avFields(I) = aFields(I)
avValues(I) = Eval(aValues(I))
Next I

With rst
Set .ActiveConnection = conn
.CursorLocation = adUseServer
.CursorType = adOpenDynamic
.LockType = adLockBatchOptimistic
.Source = TableName
.Open
.AddNew avFields, avValues
recordID = .Fields(IdField)
.Close
End With

'----- end of code snippet -----

 

[Dirk Goldgar]

The problem is that the ADO AddNew method doesn't want to receive a
strings containing delimited lists; it wants to receive arrays of
Variants. So each of your arguments FieldList and Values must be
converted to an array of Variants. I can't help thinking there must be a
better way to do it than this, but here's one way:

'----- start of code snippet -----
Dim aFields() As String
Dim aValues() As String

Dim avFields() As Variant
Dim avValues() As Variant

Dim I As Long

aFields = Split(FieldList, ",")
aValues = Split(Values, ",")

ReDim avFields(UBound(aFields))
ReDim avValues(UBound(aValues))

For I = LBound(aFields) To UBound(aFields)
avFields(I) = aFields(I)
avValues(I) = Eval(aValues(I))
Next I

With rst
Set .ActiveConnection = conn
.CursorLocation = adUseServer
.CursorType = adOpenDynamic
.LockType = adLockBatchOptimistic
.Source = TableName
.Open
.AddNew avFields, avValues
recordID = .Fields(IdField)
.Close
End With

'----- end of code snippet -----

Hmm, I think you need to call the recordset's Update method before closing
it.

 

[Jonathan]

Yep your right.

Many thanks,
Jonathan

Hmm, I think you need to call the recordset's Update method before closing
it.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Sylvain Lafontaine]

Hum, not sure to remember exactly but I think that if you are using an Array
with more than a single value then probably that you should use .UpdateBatch
instead of .Update. The same thing should apply when you are using the
adLockBatchOptimistic option instead of adLockOptimistic.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Jonathan]

Hi Sylvain, you're right. If using a client side curser then have to use
adLockBatchOptimistic and .UpdateBatch

Many thanks,
Jonathan.