Access VB Code Put to Work on SQL Server

[benkingery]

[FONT=Verdana, Arial, Helvetica]I have an inventory management database that I use to manage multiple channel online sales. The platform is SQL, but the front end I've been using is MS Access because of familiarity. I have the following VB code in a module in Access that makes a record-set out of the current onhand inventory from a transaction ledger and then updates another table with those numbers so we can evaluate inventory across multiple channels at the same time.

Access takes about 25 minutes to run through this procedure and I know SQL could do it much quicker plus I could automate the update. Can anyone out there give me any ideas as to how to do something similar in SQL? I have a pretty advanced knowledge of Access, but more of a moderate to beginning level knowledge of SQL.

Thanks in advance for anyone's help!


Public Function UpdateAZOnhand()
Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection

Dim myRecordSet As New ADODB.Recordset
myRecordSet.ActiveConnection = cnn1

Dim mySql As String
mySql = "SELECT AZ_Transactions.Item, Sum([AZ_Transactions.QTY]*IIf(Type='Credit',1,-1)) AS CurrentOnhand FROM AZ_Transactions GROUP BY AZ_Transactions.Item"

DoCmd.SetWarnings False

myRecordSet.Open (mySql)
While Not myRecordSet.EOF
sSQL = "UPDATE Master_Child SET [AZ_CurrentOnhand]=" & myRecordSet![CurrentOnhand] & " WHERE Master_Child.AZ_Child='" & myRecordSet!Item & "'"
DoCmd.RunSQL (sSQL)
DoCmd.SetWarnings (False)
myRecordSet.MoveNext
Wend
DoCmd.SetWarnings True

End Function[/FONT]

 

[wazz]

i don't work with sql server much but i'll take a shot and suggest looking into pass-through queries. i think the server does the work. and you create the query in Access (via Access). hth.

 

[LPurvis]

Indeed - though you'll need to execute T-SQL specific code in the passthrough - and it should be set based to have even greater advantage over your local recordset method.

Something like:

UPDATE Master_Child 
SET [AZ_CurrentOnhand]= SumTotal
FROM Master_Child 
    INNER JOIN 
(
 SELECT T.Item, Sum(T.QTY * CASE WHEN T.Type = 'Credit' THEN 1 ELSE -1 END) SumTotal
 FROM AZ_Transactions T
 GROUP BY T.Item
) X
ON Master_Child.AZ_Child = X.Item