asked on

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

ASKER CERTIFIED SOLUTION

alainbryden

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

alainbryden

*type: rs.Close, the way you have it. (only one dot, I wrote it with two)

tonySchlein

ASKER

The ADODB.Recordset does not have an Edit property.

tonySchlein

ASKER

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.

tonySchlein

ASKER

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

tonySchlein

ASKER

I found the problem. Carelessness.