Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
benking9987
Posting Yak Master
124 Posts |
 Posted - 2009-04-01 : 13:22:30
|
| 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.ConnectionSet cnn1 = CurrentProject.ConnectionDim myRecordSet As New ADODB.RecordsetmyRecordSet.ActiveConnection = cnn1Dim mySql As StringmySql = "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 FalsemyRecordSet.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.MoveNextWend DoCmd.SetWarnings TrueEnd Function |
|
|
|
|
|
Access VB Code Put to Work on SQL Server