Hello. I'm a real newbie - using Access 2003 front end and connecting to SQL Server 2005 ODBC.
I'm having trouble accessing functions through access. I've built the following function:
CREATE FUNCTION fnSTR_LEASESTATUS(@.TRS nvarchar(12))
RETURNS TABLE
AS
RETURN
(
SELECT dbo.tblTRACT.STR, dbo.tblTRACT.[TRACT_#], dbo.tblMIN_OWNERS.Min_Owner_Name AS [OWNER OF RECORD], dbo.tblLEASE_TRACTS.LOC_ID, dbo.tblLOCATION.LPR_No, dbo.tblLOCATION.Lease_ID, dbo.tblLEASE_LOG.Date_Mailed, dbo.tblLEASE_LOG.Scan_Lease_Received, dbo.tblLEASE_LOG.Orig_Lease_Recd, dbo.tblLPR_INVOICES.Invoice_No, dbo.tblLPR_PAY.CHECK_DRAFT_No, dbo.tblLESSORS.Name AS [Lease Name]
FROM dbo.tblTRACT LEFT JOIN ((dbo.tblMIN_OWNERS RIGHT JOIN dbo.tblTRACT_OWNER ON dbo.tblMIN_OWNERS.Min_Owner_ID = dbo.tblTRACT_OWNER.Owner_Lease) LEFT JOIN ((((((dbo.tblLPR RIGHT JOIN dbo.tblLOCATION ON dbo.tblLPR.LPR_No = dbo.tblLOCATION.LPR_No) LEFT JOIN dbo.tblLESSORS ON dbo.tblLPR.Lessor_Number = dbo.tblLESSORS.Lessor_Number) RIGHT JOIN dbo.tblLEASE_TRACTS ON dbo.tblLOCATION.LOC_ID = dbo.tblLEASE_TRACTS.LOC_ID) LEFT JOIN dbo.tblLEASE_LOG ON dbo.tblLPR.LPR_No = dbo.tblLEASE_LOG.LPR_No) LEFT JOIN dbo.tblLPR_INVOICES ON dbo.tblLPR.LPR_No = dbo.tblLPR_INVOICES.LPR_No) LEFT JOIN dbo.tblLPR_PAY ON dbo.tblLPR.LPR_No = dbo.tblLPR_PAY.LPR_No) ON dbo.tblTRACT_OWNER.TRACT__Owner_ID = dbo.tblLEASE_TRACTS.Tract_Owner_Id) ON (dbo.tblTRACT.[TRACT_#] = dbo.tblTRACT_OWNER.[TRACT_#]) AND (dbo.tblTRACT.STR = dbo.tblTRACT_OWNER.STR)
WHERE (((dbo.tblTRACT.STR)=@.TRS))
)
GO
I understand now I can create a view of the function Simply by using the function name in my FROM statement. However I get an error that arguments provided do not match parameters required. However, I'm not getting the prompt to enter my criterion. Is my error in my function statement? I can't save the view. I also understand I could use a pass-through query. Is there some sort of guidance or tutorial on that to which you could point me?
Thanks for your time.
You function has param @.TRS.
You could use it:
Code Snippet
SELECT * FROM fnSTR_LEASESTATUS('Your param value')
|||
Hi,
Let me try to address your concerns
There's no need to create a view of a table function, unless you want the view results somehow filtered or different than the function results.
To use the your table function simply SELECT * FROM fnSTR_LEASESTATUS(<put value here>)
If you want to create a view you need to have rights on the SQL Server 2005 database.
You are probably using an Access data project? Using these projects you are not able to just save queries as in regular Access database. With these projects you are connected directly to the server and therefore anything you save you are saving on the server.
You may want one project to create server objects like functions. Then have another regualr access database to save your queries (pass- through preferred)
|||
That allowed me to run it, but when I try to save it I get the message "Must declare the scalar variable @.TRS".
|||
Ok. I can run it in SQL Server, but how do I get to it in Access? I was hoping I could save it as a view and then link to it the way I have my other views.
Thanks.
|||
Oh, sorry. Forgot to tell you. I decided against ADP after reading on the Access user's group. So I build my views in SQL Server and then link to them.
Thanks.
|||To save your view in SQL Server you need to replace the variable reference, @.TRS, with an actual value. Once you have it saved in SQL Server, you can link like other view links|||And if you want to be prompted from Access, forget saving it as a view in SQL Server and try saving it as an Access query or some other way in Access
