Are you comparing apples to apples?  In your VBA code, are you
executing the same join syntax that you are trying to excute through
the Access GUI.  I have a suspicion that you are not, and the reason
the query is bombing out on you is that the join is forcing your
workstation to bring over all of the SQL Server data and working on the
update data client side.  However, without code to examine, no freaking
clue :)

Stu

On 7/28/2005 5:13 PM, Stu wrote:
Access Query def:

UPDATE dbo_Order_Line_Invoice INNER JOIN tblOrderTypeValues ON
(dbo_Order_Line_Invoice.Cono = tblOrderTypeValues.cono) AND
(dbo_Order_Line_Invoice.LineNum = tblOrderTypeValues.lineno) AND
(dbo_Order_Line_Invoice.OrderNumber = tblOrderTypeValues.OrderNum) SET
  dbo_Order_Line_Invoice.SXUser10 = tblOrderTypeValues.ordertype;

dbo_Order_Line_Invoice is the SQL server table

VBA code that works:

Sub UpdateOrdertype()

     Dim rsOrderType As Recordset
     Dim strSQL As String
     Dim intCounter As Long


     Set rsOrderType = CurrentDb.OpenRecordset("tblOrderTypeValues")

     Do While Not rsOrderType.EOF

         intCounter = intCounter + 1
         Debug.Print intCounter: DoEvents

         strSQL = "UPDATE dbo_Order_Line_Invoice SET
          dbo_Order_Line_Invoice.SxUser10 = '" & rsOrderType.OrderType &
          "' WHERE (dbo_Order_Line_Invoice.Cono = 1) AND

         (dbo_Order_Line_Invoice.OrderNumber = '" & rsOrderType.OrderNum
          & "' ) AND (dbo_Order_Line_Invoice.LineNum =
          " & rsOrderType.Lineno & ");"

         CurrentDb.Execute (strSQL)

         rsOrderType.MoveNext

     Loop

     rsOrderType.Close

     MsgBox "Done updating ordertype."

End Sub

Looks like my suspicion was correct; in the join scenario, you're
dragging the complete recordset over and attempting to do the join
client side.  If your data set is large (or your machine is
over-taxed), then it'll take a while to do the updates all at once.

In the second scenario (VBA), you're updating one record at a time on
the SQL Server, so the machine is not dealing with multiple records.

Does that make sense?  As to how to fix it, not sure.

Stu

On 7/29/2005 1:22 PM, Stu wrote:
In the join scenario, the msaccess process spikes the CPU but I/O is
stagnant.  So I think there is a bug somewhere as the update join query
originally worked.

Dan

Which I/O?  Disk or network?  I don't think Access manages ODBC by
writing it to a temporary file; I believe it tries to do it in-memory.
I could be wrong about that.

On 8/1/2005 12:44 PM, Stu wrote:
Both.

Stored Procs vs VIEWS: Seeking Comparison
How to find gaps in sequential key
boolean programming
JDBC client - CURSOR as OUT parameter -supported ?
Creating a trigger using a cursor