Access Update Query Problem

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 :)
StuOn 7/28/2005 5:13 PM, Stu wrote:
> 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
>
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