Solved

Convert an Access 2003 Function to a MS SQL 2005 Function

Posted on 2007-04-02
17
252 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
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
 
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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

749 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