I have a current Access query named FindProductsByBatchID that selects data based in user input (See Below). It seemed to me that the query could easily be replaced by an SQL Server User defined Function, so I created one. The SQL Server Management Studio accepts the function as properly formed when I execute the update, saying that the update succeeded. I cannot, however, get MS Access to recognize the function. Access indicates that there is a "Syntax error in FROM clause"

Below is my first attempt (based on a model from the Stored Procedure/Function Programming Book that I am currently reading). This is my first attempt at making a User defined Function here, so any advice is welcome.

-----------------------------------------------------------------------

Original Query:

SELECT BatchID, Product_ID, Amount, Unit, Price, InvoiceNumber, Institution, [Invoice Date]
FROM ViewMasterView
WHERE (BatchID Like "*" & [Type All or Part of BatchID] & "*");

NOTE: ViewMasterView is an SQL Server View that contains all data in the database that staisfies the query.

-----------------------------------------------------------------------

Proposed Function:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER FUNCTION FindProductsByBatchID
(
-- The parameters for the function are here

@TheBatchID nvarchar(50)
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT BatchID, Product_ID, Amount, Unit, Price, InvoiceNumber, Institution, [Invoice Date]
FROM dbo.ViewMasterView
WHERE (BatchID Like @TheBatchID)
)
GO

-----------------------------------------------------------------------

Access query to call the Function:

SELECT BatchID, Product_ID, Amount, Unit, Price, InvoiceNumber, Institution, [Invoice Date]
FROM dbo.FindProductsByBatchID("*" & [Type All or Part of BatchID] & "*");

-----------------------------------------------------------------------

Hi there,

What is this part supposed to do?

WHERE (BatchID Like "*" & [Type All or Part of BatchID] & "*");

Are you expecting that to prompt the user to enter the batch id?

Hi there,

What is this part supposed to do?

WHERE (BatchID Like "*" & [Type All or Part of BatchID] & "*");

Are you expecting that to prompt the user to enter the batch id?


Yes. The purpose of this query is to accept input from the user and get the appropriate records from the database to display on the screen. I have been reading more on the subject since I made the first post, and have also been running tests. It would seem that this approach is not possible. Is there another approach that I could be using?

Yes you cannot do that at all,

SQL server simply cannot interact with anything that is run on the client. Form references and message boxes are impossible for any object in sql server.

But there is an easy way round this, instead of using a the query to raise the input we will just program it in VBA

Example:

Dim vBatchID as int
Dim strSQL as string

vBatchID = INPUTBOX ("Enter BatchID")
strSQL = "SELECT BatchID, Product_ID, Amount, Unit, Price, InvoiceNumber, Institution, [Invoice Date]
FROM dbo.FindProductsByBatchID
WHERE BatchID Like '&#37;' + '" & vBatchID & '" + '%'"

forms!frm1.recordsource = strSQL



I am not sure if you are using this for a forms record source or not, but even if my example isn't quite right it should point you in the correct direction.

Are you using .mdb as front end or .adp? If you are using .adp then it's even easier.

One thing that springs to mind is that I dont understand the need for the view, personally I would get rid of the view and just have all the sql code in the user defined function. The view just adds another level of complexity that isn't needed.