Insert a row in an Excel Recordset through Excel VBA

Question

I am trying to insert some values on the last row of the recordset which in this case is an Excel file that serves as my database. I have the code below that works in inserting the value of the textbox to the last row of the excel recordset. However, it did not create a new table row where the value was inserted.

Sub CreaterRow()
    Dim strFile As String
    Dim strConnect As String
    Dim strSQL As String
    Dim lngCount As Long
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    strFile = "C:\Excel\Test.xlsx"
    strConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile & _
    ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
    cnn.Open ConnectionString:=strConnect
    strSQL = "SELECT [ID] FROM [Sheet1$]"
    rst.Open Source:=strSQL, ActiveConnection:=cnn, CursorType:=adOpenForwardOnly, Options:=adCmdText

    With rst
        .AddNew
           .Fields("ID").Value = tbx_ID.Value 'Inserting this in the recordset did not create a new row
         .Update
    End with 

    rst.Close
    cnn.Close
End Sub

How can the table automatically create a new row that will include the value is inserted in the lastrow? Thank you.

Thanks for pointing that out. I was unable to paste '.Update' My question remains the same. — xnewbie
– xnewbie, Commented Jan 22, 2020 at 20:58

Tim Williams · Accepted Answer · 2020-01-23 20:03:34Z

1

This worked for me. You need to have the right cursor and lock type.

Sub CreaterRow()
    Dim strFile As String
    Dim strConnect As String
    Dim strSQL As String
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset

    strFile = ThisWorkbook.Path & "\Data.xlsx"

    strConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""" & strFile & _
                """;Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
    cnn.Open ConnectionString:=strConnect
    strSQL = "SELECT [ID] FROM [Sheet1$]"

    rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic

    With rst
        .AddNew
        .Fields("ID").Value = "ID00020"
        .Update
    End With

    rst.Close
    cnn.Close
End Sub

EDIT: if you're querying data from a Table/Listobject then appending records will not resize the list to include the added records. See: ADO: Excel: Is it possible to open recordset on table name?

EDIT2: If you use a named range instead of a ListObject, then you can query it by name (instead of using the sheet name) and the range will adjust when you insert new rows.

edited Jan 23, 2020 at 20:03

answered Jan 23, 2020 at 0:56

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

xnewbie Over a year ago

It is working without errors, but still it's not creating a table row. Did your result created new table row

Tim Williams Over a year ago

Yes - added at the end of the existing data.

xnewbie Over a year ago

For me, the value was inserted and attached to the end of the data, but the table didn't create a new row. I will try to create a new table if that will work. Thanks.

Tim Williams Over a year ago

stackoverflow.com/questions/36855243/…

xnewbie Over a year ago

D***. I highly appreciate you for providing insight. if you can please alter your answer that will reflect also the information in the link, then I will check your answer. So in sum, it's not possible to add a table row in this case, right?

|

Collectives™ on Stack Overflow

Insert a row in an Excel Recordset through Excel VBA

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related