Solved

Convert an Access 2003 Function to a MS SQL 2005 Function

Posted on 2007-04-02
17
245 Views
Last Modified: 2010-03-19
I am creating a simple Stock DB.
Could you please show me how I can move this Function from Front End Access to the Back End SQL Server where I know it will run much quicker.  I would also like to create a trigger that runs this function after an Insert or Delete event as well.  This will enable my StockItems table to always be up-to-date with the current stock level.
=========
Function CalcRecievedItemsRemainingStockItem(FncStockItemID)
' This will calculate the quantity remaining for a single Stock Item ID
On Error GoTo Err_CalcRecievedItemsRemaining

Dim db As ADODB.Connection
Dim strsql As String

Set db = CurrentProject.Connection

strsql = "UPDATE tblReceivedItem LEFT JOIN vewTotalDispatchedForEachItemReceived ON tblReceivedItem.ReceivedItemID = vewTotalDispatchedForEachItemReceived.ReceivedItemID SET tblReceivedItem.StockRemaining = IIf(IsNull(SumOfQuantity),Quantity,Quantity-SumOfQuantity) WHERE (((tblReceivedItem.StockItemID)=" & FncStockItemID & "))"
Debug.Print strsql
db.Execute strsql

Set db = Nothing

Exit_CalcRecievedItemsRemaining:
    Exit Function

Err_CalcRecievedItemsRemaining:
    MsgBox Err.Description & " " & Err.Number
    Resume Next
   
End Function
===========
0
Comment
Question by:CABHugh
  • 10
  • 7
17 Comments
 
LVL 10

Expert Comment

by:RichardCorrie
ID: 18839569
by function i suppose you mean stored procedure;  An SQL Function cannot be used to update data;
I am assuming that FncStockItemID is an int  - you have to declare the type of variable in SQL. ( a bit naughty of you not to do son in your access module<g>)

create procedure CalcRecievedItemsRemainingStockItem(@FncStockItemID int)
as
UPDATE   tblReceivedItem
SET
StockRemaining = case when SumOfQuantity is null then Quantity else Quantity-SumOfQuantity end
From
tblReceivedItem LEFT JOIN vewTotalDispatchedForEachItemReceived
ON tblReceivedItem.ReceivedItemID = vewTotalDispatchedForEachItemReceived.ReceivedItemID
WHERE
tblReceivedItem.StockItemID = @FncStockItemID
go
0
 

Author Comment

by:CABHugh
ID: 18839751
Hi Richard
Yes it's an int ! ;)

22:22 here in Portugal so I'll try this when I'm in the Office tomorrow morning.  Thanks for responding so promptly.

It looks like exactly what I need and a hell of a lot simpler than I expected to see!

How can I call this with an Insert or Delete Trigger (can I do and Update trigger as well?) to update the table automatically without action from the Front end?

Hugh
0
 

Author Comment

by:CABHugh
ID: 18841946
Hi Richard
Tried this and it does just what I need - Thanks.
2 more questions (so I've tripled the points!):
1 - How do I set the triggers in SQL (Update, Delete, Insert) ?
2 - How do I call these stored procedures from Access front end manually if I choose ?
0
 
LVL 10

Expert Comment

by:RichardCorrie
ID: 18842155
1) you can call this "in a trigger" but need to be careful; which table is being changed which will fire this code
you use the create trigger <<triggerName>> on <<TableName>>
for Insert| update| delete.  If you let me have the name of the table and the structure (or at least the relevant columns ) i wiull try to write the code...

2) to call the proc from access front end, depends on the type of front end u have;  If it is a "normal" (.mdb) access with linked odbc tables u can use a passthru query; if u have created a project (.adp) u can use ADO to call the procedure (look in the help file about calling procedures) but code could look something like

dim cmd as new ado.command
dim prm as ado.parameter
set cmd.connection = currentproject.connection
set prm = cmd.createparameter("@FncStockItemID",sqldb.int) '<-- you will have to use intellisense to get this correct
prm.value = FncStockItemID
cmd.parameters.add(prm)
cmd.execute

/Richard

0
 

Author Comment

by:CABHugh
ID: 18843259
Sorry for the delay in replying.

I have used you example to create several Stored Procedures and they work well.  As an example of how I wish to use these I will use a slightly different one to previously.  This procedure calculates the total received items and dispatched items to calculate the total remaining (CurStockQty).

Stored Procedure Modify shows this:
ALTER procedure [dbo].[UpdateStockLevelSingle](@StockID int)
as
UPDATE    tblStockItem
SET           TotalReceived = isnull(vewTotalReceivedAndDispatchedPerStockItem.ReceivedQty,0),
                 TotalDispatched = isnull(vewTotalReceivedAndDispatchedPerStockItem.DispatchedQty,0),
                 CurStockQty=isnull(vewTotalReceivedAndDispatchedPerStockItem.ReceivedQty - vewTotalReceivedAndDispatchedPerStockItem.DispatchedQty,0)
FROM         tblStockItem INNER JOIN
                      vewTotalReceivedAndDispatchedPerStockItem ON tblStockItem.StockItemID = vewTotalReceivedAndDispatchedPerStockItem.StockItemID
WHERE
vewTotalReceivedAndDispatchedPerStockItem.StockItemID = @StockID

I wish the trigger to run on the Insert, Update, Delete event in tblReceivedItems table.
====
I am using a MDB (MDE when issued to users later) and have looked at the pass-through but am now lost!  If I can see a working example I can usually figure it all out from that.
0
 

Author Comment

by:CABHugh
ID: 18853344
Hi Richard

Know you're busy but any chance you'll have a moment to help further?  If not then I may need to post a new question.

Regards

Hugh
0
 
LVL 10

Expert Comment

by:RichardCorrie
ID: 18856425
if u are using several sps and they are not going to be a source for a form or report u can use ADO in your code, see my example (2) above - you will have to add a reference to Microsoft Advanced Data Objects n.x to you code base.

/Richard
0
 

Author Comment

by:CABHugh
ID: 18856572
Thanks for response and will try the ADO.

What I really want is for this recalculation to happen automatically on INsert, Delete, Update so the Stock levels are always up to date.  It strikes me that this is best achieved by using a trigger on the table.  I just don't know how to write the trigger that will run my SP.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 10

Expert Comment

by:RichardCorrie
ID: 18856885
I gues u have 3 tables here: tblStockItem; tblStockItemDispatched, tblStockItemRecieved?

u then need triggers on tblStockItemDispatched and tblStocklItemReceived to update tblStockItem:

Create trg_AddStockItem on tblStockItemReceived
for insert,Update
as
UPDATE    tblStockItem
SET           TotalReceived =TotalReceived  + I.ReceivedQty,
                 CurStockQty= CurStockQty + I.ReceivedQty
FROM        
tblStockItem  SI
INNER JOIN
inserted I
on
SI.StockID = I.stockID
go
Create trg_RemoveStockItem on tblStockItemDispatched
for insert,Update
as
UPDATE    tblStockItem
SET           TotalDispatched =TotalDispatched + I.DispatchedQty,
                 CurStockQty= CurStockQty - I.DispatchedQty
FROM        
tblStockItem  SI
INNER JOIN
inserted I
on
SI.StockID = I.stockID
go

/Richard

0
 

Author Comment

by:CABHugh
ID: 18858352
That would work well but I want to be able to allow the users to change the quantity after they have inserted in case of typos etc.
Using this method each update would add to the stock so I would very quickly have a problem!

By using the SP that recalculates all recived and dispatched items I will always have an accurate calculation.  I'm not talking about thousands of movements so the numbers involved are quite managable.

The SPs already do the calculations and update the tables I require.

Question - How do I create the Triger that will run a SP on Inset, Update or Delete ?

Getting quite desperate now so have increased points to 500.

Thanks

Hugh
0
 
LVL 10

Accepted Solution

by:
RichardCorrie earned 500 total points
ID: 18881227
if the user can change the values (TotalDispatched, TotalReceived, CurStockQty) in tblStockItem then the triggers i have wriiten will still work.

or
Create trg_AddStockItem on tblStockItemReceived
for insert,Update

UPDATE    tblStockItem
SET           TotalReceived = isnull(vewTotalReceivedAndDispatchedPerStockItem.ReceivedQty,0),
                 TotalDispatched = isnull(vewTotalReceivedAndDispatchedPerStockItem.DispatchedQty,0),
                 CurStockQty=isnull(vewTotalReceivedAndDispatchedPerStockItem.ReceivedQty - vewTotalReceivedAndDispatchedPerStockItem.DispatchedQty,0)
FROM        
tblStockItem
INNER JOIN
vewTotalReceivedAndDispatchedPerStockItem
ON
tblStockItem.StockItemID = vewTotalReceivedAndDispatchedPerStockItem.StockItemID
inner join
Inserted I
vewTotalReceivedAndDispatchedPerStockItem.StockItemID = I.StockItemID
go
/Richard

0
 

Author Comment

by:CABHugh
ID: 18881292
This looks fine - Thanks

How would I write this trigger if I just wanted to run an existing SP?

Hugh
0
 
LVL 10

Expert Comment

by:RichardCorrie
ID: 18881359
calling an SP from a trigger is not very effficient as u have to use a cursor; it is much better to use the code from SP as basis for trigger; however your wish is my command<g>:

Create trg_AddStockItem on tblStockItemReceived
for insert,Update
as
declare @StockID int
declre crsStock as cursor local fast_forward for
select StockItemID from inserted

open crsStock
fetch next from crsStock into @StockID
while @@Fetch_status = 0
begin
exec CalcRecievedItemsRemainingStockItem @StockID
fetch next from crsStock into @StockID
end
close crsStock
deallocate crsStock
go
/Richard
0
 

Author Comment

by:CABHugh
ID: 18882356
Many thanks Richard

I did have to add the 'AS' and 'ON' to get it to work but now fine.

Thank you for giving me your time.

Hugh
0
 

Author Comment

by:CABHugh
ID: 18883653
Sorry to trouble you again but the Delete triger does not work!
Any Idea why?

create trigger [dbo].[trg_UpdateStockLevelsDelete] on [dbo].[tblReceivedItem]
for delete
as
UPDATE    tblStockItem
SET           TotalReceived = isnull(vewTotalReceivedAndDispatchedPerStockItem.ReceivedQty,0),
                 TotalDispatched = isnull(vewTotalReceivedAndDispatchedPerStockItem.DispatchedQty,0),
                 CurStockQty=isnull(vewTotalReceivedAndDispatchedPerStockItem.ReceivedQty,0) - isnull(vewTotalReceivedAndDispatchedPerStockItem.DispatchedQty,0)
FROM        
tblStockItem
INNER JOIN
vewTotalReceivedAndDispatchedPerStockItem
ON
tblStockItem.StockItemID = vewTotalReceivedAndDispatchedPerStockItem.StockItemID
inner join
Inserted I
on
vewTotalReceivedAndDispatchedPerStockItem.StockItemID = I.StockItemID
0
 
LVL 10

Expert Comment

by:RichardCorrie
ID: 18888257
in triggers u have 2 "virtual" tables Inserted and Deleted. in effect this indicates what is happening when a record is amended
when adding the inserted table has details of record inserted; Deleted is empty
when updating the inserted table has details of records updated; Deleted has equivalent records with values prior to update
when deleting, inserted table is empty, Deleted has records being deleted.

so to get the Delete trigger to work, change the reference to Inserted to Deleted.

SQL Books on Line (should have been installed) gives a v good summary of triggers etc.

/Richard
0
 

Author Comment

by:CABHugh
ID: 18888332
Thanks for your help with this.

Sorry for being so dumb!
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now