Solved

Cannot update ADODB.Recordset from VBScript

Posted on 2006-06-20
6
663 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS Access - Capture pressed key onclick 4 28
MsgBox 2 47
TT Auto Dashboard 13 82
Added a column screws up code 5 49
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

910 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

19 Experts available now in Live!

Get 1:1 Help Now