Cannot update ADODB.Recordset from VBScript

In an .hta I am using VBScript to add a new record to a ADODB.Recordset, populate the fields and update the database (Access) by updating the Recordset. The new records are not showing up in the database.
No errors are being generated. The record count climbs as I add new records.
I have experemented with different cursors and recordset types and I am stumped.
I can perform this function in VBA.

      adUseClient = 3
      adOpenKeyset = 1
      adOpenDynamic = 2
      adLockOptimistic = 3
      set cn = createobject("ADODB.Connection")
      set rs = createobject("ADODB.Recordset")
      rs.CursorLocation = adUseClient
      rs.CursorType = adOpenKeyset
      set dao = new clDao
      cn.Open "Driver={Microsoft Access Driver (*.mdb)};Dbq=" + DATA_FILE + ";Uid=Admin;Pwd=;"
      rs.Open "SELECT * FROM trade_import", cn,adOpenDynamic, adLockOptimistic
      set xl = createObject("Excel.Application")
      set bk = xl.Workbooks.Open(TRADE_SOURCE_FILE)
      set wk = bk.Worksheets(1)

      for iRow = 1 to 20
            portfolioCode =  wk.Cells(iRow, 3)
            if portfolioCode <> "" then
                  rs.AddNew
                  rs.Fields("transaction_id").Value = wk.Cells(iRow, 1)
                  rs.Fields("transaction_code").Value = wk.Cells(iRow, 2)                  
            end if            
      next
      xl.Quit
      msgbox "rs count = " & rs.RecordCount
      err = rs.UpdateBatch (i have tried .Update with no help)
      rs.close
      set rs = nothing
      set cn = nothing
tonySchleinAsked:
Who is Participating?
 
alainbrydenConnect With a Mentor Commented:
That's because you aren't actually editing the record, you are only editing the memory. To save the changes you need to add the following two codes:

rs.Edit Before you write anything to it.
rs..Close To write everything you've written under "Edit" mode and close the file (which you have allready)

So just that first one is missing then.
And instead of opening adOpenDynamic, adLockOptimistic
you should simply open it , dbOpenDynaset (or adOpenDynaset as the case may be) but I don't think it will be necessary. (try it as well if the previous doesn't work)

rs.Open "SELECT * FROM trade_import", adOpenDynaset
works for the DAO.Recordset but maybe the notation is a little different for the ADODB.Recordset.
0
 
alainbrydenCommented:
*type: rs.Close, the way you have it. (only one dot, I wrote it with two)
0
 
tonySchleinAuthor Commented:
The ADODB.Recordset does not have an Edit property.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
tonySchleinAuthor Commented:
I do not think that dbOpenDynaset applies to ADODB.Recordsets. dbOpenDynaset has a value of 2 which is the same as adOpenDynamic. If I open a Recordset with adOpenDynamic and I leave off the adLockOptimistic I cannot add records to the Recordset.
0
 
tonySchleinAuthor Commented:
I am still trying to figure this out. Here is another piece of information. Before adding the new records I delete the existing records from the table. I use a query stored in the database to do so.
When I skip this step, I am able to add records to the database.
It is as if the delete persists somehow.
If I delete the records in the method listed above using the same query, I do not have a problem.
Is my connection and query from the method below persisting somehow?

      set cn = createobject("ADODB.Connection")            
      cn.Open "Driver={Microsoft Access Driver (*.mdb)};Dbq=" + DATA_FILE + ";Uid=Admin;Pwd=;"
      cn.Execute "qryDeleteTradeImport"
      cn.Close
      set cn = nothing
0
 
tonySchleinAuthor Commented:
I found the problem. Carelessness.
0
All Courses

From novice to tech pro — start learning today.