A more elegant way to update recordsets than this? (1 Viewer)

[peskywinnets]

Disclaimer, I'm a kludger....I grab what I need & then lump the (often disparate) bits of code together to get the end result. I'll never be a programmer (I've got a business to run!)

Ok, with that said, this has been a long journey & I'm pretty much there, but I seek a more elegant way of taking some parsed XML & updating a table with the result.

Let's say I have a table called Orders with a column called OrderID. I parse some XML (taken from the web) & have some new information relating to that OrderID in my access database, let's say it OrderStatus....so I want to update 'OrderStatus' in my table with the contents of the XML.

Here's what I'm doing 9there's a lot of code prior to this, but it's not relevant to the question being asked)...

    Set OrderCount = objxmldoc.selectNodes("//ns1:Order")
    Set status = objxmldoc.selectNodes("//ns1:OrderStatus")
    Set  OrderId = objxmldoc.selectNodes("//ns1:OrderId")
    
    R = 0
    For Each node In OrderCount 'traverse through the XMl
    Set rst = CurrentDb.OpenRecordset("SELECT * FROM Orders) ' I actually use a where statement to slim the number of selected records down, but removed here for simplicity
    OrderID = OrderId(R).text       'parse the XML to find the first OrderID
    OrderStatus = status(R).text    'parse the XML to get the Order status 
    rst.FindFirst "OrderID = 'OrderID'"   ' find the OrderID in the Access table
    rst.Edit                              
    rst!OrderStatus = OrderStatus   'update the Access table with the latest status (as parsed from the XML)
    rst.Update
    rst.Close
    R = R + 1         
    Next

'rinse repeat

...essentially with each bit of parsed XML, I'm opening the table (to ensure the rst.FindFirst starts at the first record), then 'finding' the matched record, then updating the field of interest. It works fine, but - even to my non-programmer's eyes - it doesn't feel elegant.

 

[static]

Editing recordsets is very slow. Learn SQL.

 

[peskywinnets]

Thanks for the info...I wasn't aware of that, I will certainly look at using SQL, but for now, I'm only anticipating about 50-100 records will need updating - & it's quite zippy.

 

[static]

Well you don't need to set the recordset on every loop.

try this

    Set OrderCount = objxmldoc.selectNodes("//ns1:Order")
    Set status = objxmldoc.selectNodes("//ns1:OrderStatus")
    Set  OrderId = objxmldoc.selectNodes("//ns1:OrderId")

For Each node In OrderCount
   currentdb.execute "update orders set orderstatus = '" & status(R).text & "' where orderid='" & OrderId(R).text &"'"
 r=r + 1
next

 

[peskywinnets]

Well that's certainly more easy on the eye...I''ll give it a go

Many thanks!

 

[peskywinnets]

Your suggested code of...

   currentdb.execute "update orders set orderstatus = '" & status(R).text & "' where orderid='" & OrderId(R).text &"'"

...worked a treat.

I'm one happy person right now - thanks!

 

[static]

NP. Glad it worked

 

[peskywinnets]

My next problem-ette (which is embarrassingly simple I guess), is using VBA to essentially duplicate an unmatched append query. I've read lots on the internet re this topic, but I'm as confused now as I was before I started, so can someone throw me a line?!!

I have Two tables, with a record in each table...

Table 1 OrderList.OrderID

Table 2 OrderDetails.OrderID

...all I want to do is this rule ...

"If there's an OrderID in Table 1 (OrderList.OrderID) that doesn't exist on table 2 (OrderDetails.OrderID), then add the missing OrderID to Table 2 (OrderDetails.OrderID) .....basically replicating what an unmatched append query does.

I've already got this working with two Access queries - the first query shows the the unmatched records, the second query is appends the unmatched results of the first query to the second table - it isn't that elegant - plus I'd rather keep this all in VBA!

 

[static]

Dim db As DAO.Database
Set db = CurrentDb

db.Execute "insert into OrderDetails ( OrderID ) select a.OrderID " & _
"from OrderList a left join OrderDetails b on a.OrderID = b.OrderID " & _
"where b.OrderID is null "

MsgBox db.RecordsAffected & " record(s) were added."

 

[peskywinnets]

Many thanks...that's very kind of you to help me out