Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Convert an Access 2003 Function to a MS SQL 2005 Function

Posted on 2007-04-02
17
Medium Priority
?
261 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

721 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