Improve company productivity with a Business Account.Sign Up

x
?
Solved

Cannot update ADODB.Recordset from VBScript

Posted on 2006-06-20
6
Medium Priority
?
711 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:tonySchlein
  • 4
  • 2
6 Comments
 
LVL 21

Accepted Solution

by:
alainbryden earned 1000 total points
ID: 16946814
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
 
LVL 21

Expert Comment

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

Author Comment

by:tonySchlein
ID: 16946882
The ADODB.Recordset does not have an Edit property.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:tonySchlein
ID: 16947000
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
 

Author Comment

by:tonySchlein
ID: 16947468
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
 

Author Comment

by:tonySchlein
ID: 16947561
I found the problem. Carelessness.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question