Solved

Cannot update ADODB.Recordset from VBScript

Posted on 2006-06-20
6
655 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 20

Accepted Solution

by:
alainbryden earned 500 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 20

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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 utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now