12

How to add new records to a new & empty ADODB.Recordset manually?

Right now, here's what I'm doing that isn't working:

Dim rs as ADODB.Recordset
rs.Open
Dim Fields() as String
Fields(0) = "SomeFieldName"

Dim Values() as String
Value(0) = "SomeValue"

rs.AddNew Fields, Values
Deanna
  • 23,876
  • 7
  • 71
  • 156
bitcycle
  • 7,632
  • 16
  • 70
  • 121

3 Answers3

16

In-place:

rs.AddNew "SomeFieldName", "SomeValue"

Or in-place multiple fields

rs.AddNew Array("SomeFieldName", "AnotherFieldName"), Array("SomeValue", 1234)

Or using separate vars

Dim Fields As Variant
Dim Values As Variant

Fields = Array("SomeFieldName")
Values = Array("SomeValue")
rs.AddNew Fields, Values

Edit: This is how to synthesize a recordset for the AddNew sample above

Set rs = new Recordset
rs.Fields.Append "SomeFieldName", adVarChar, 1000, adFldIsNullable
rs.Fields.Append "AnotherFieldName", adInteger, , adFldIsNullable
rs.Open

I'm usually using a helper function CreateRecordset as seen this answer.

Update 2018-11-12

You can also use field indexes as ordinals instead of field names as strings for the fields array like this

rs.AddNew Array(0, 1), Array("SomeValue", 1234)
wqw
  • 11,771
  • 1
  • 33
  • 41
  • Could you include the declaration and preparation of the recordset prior to the addnew() call? I want to make sure that I'm doing all that needs to be done, no more and no less. – bitcycle Feb 19 '10 at 15:53
  • What would be the best way to execute an SQL statement on the recordset, after the AddNew update? – Lo Bellin Oct 28 '20 at 17:32
  • @LoBellin This synthetic client-side recordset is not persisted in a RDBMS. Think of it as a glorified 2D array. It's not possible to execute any SQL as there is no RDBMS. – wqw Oct 29 '20 at 07:11
6
set rs = new ADODB.Recordset
rs.Open "Select SomeFieldName, AnotherFieldName FROM MyTable", myConnection, adOpenDynamic, adLockOptimistic

rs.AddNew
rs("SomeFieldName").Value = "SomeValue"
rs("AnotherFieldName").Value = 1
rs.Update

rs.AddNew
rs("SomeFieldName").Value = "AnotherValue"
rs("AnotherFieldName").Value = 2
rs.Update

rs.Close
C-Pound Guru
  • 15,967
  • 6
  • 46
  • 67
0

With an open connection Conn:

sSql="INSERT into mytable (somefieldname, anotherfieldname) values  ('Somevalue','Anothervalue')"
      Conn.Execute sSql
JMax
  • 26,109
  • 12
  • 69
  • 88
Claudio
  • 39
  • 3