April 19, 2008 at 10:47 pm
I can not figure this out. Below is a portion of a view that has MULTIPLE other views run from it. The view below acts as a filter basically to return records for the date range specified in the WHERE.
SELECT [dbo].[TAT].[ResultLab], [dbo].[TAT].[PtOrderLoc], [dbo].[TAT].[TestBatCode], [dbo].[TAT].[OrderPriority], [dbo].[TAT].[CollDate], [dbo].[TAT].[Colltime], .......
FROM [dbo].[TAT]
WHERE (ReceiveDate BETWEEN CONVERT(DATETIME, '2007-12-01 00:00:00', 102) AND CONVERT(DATETIME, '2007-12-3121 00:00:00', 102))
What I want to do is set this up so that in the Access adp there is a form with 2 fields StartDate and EndDate. The user would enter these 2 fields and pass them to this view (which I have converted to a Function - below). All the subsequent views that are run would go against this filtered data rather than the entire sql table.
ALTER FUNCTION dbo.Function1 (@Start_Date datetime, @End_Date datetime)
RETURNS TABLE AS
RETURN
(SELECT [dbo].[TAT].[ResultLab], [dbo].[TAT].[PtOrderLoc],
[dbo].[TAT].[TestBatCode], [dbo].[TAT].[OrderPriority],
[dbo].[TAT].[CollDate], [dbo].[TAT].[Colltime],
([dbo].[TAT].[CollDate] + [dbo].[TAT].[Colltime])
AS COLLECTDATETIME, [dbo].[TAT].[ReceiveDate],.....
FROM [dbo].[TAT]
WHERE (ReceiveDate BETWEEN CONVERT(DATETIME, @Start_Date, 102) AND CONVERT(DATETIME, @End_Date, 102)))
For example
The views would be something like
CREATE VIEW [dbo].[TATpriorityUAH]
AS
SELECT ...
FROM dbo.Function1()
I am not sure if I am headed down the right path or not. This was relatively easy in the mdb world. I can not figure out how make the form part of this work, or how to create the subsequent views (I get a Error 313 Insuffecient number of variables...)
If there is an easier way to do this where the user can specify the filter please let me know. As much detail as you can provide on a solution for this is much appreciated.
April 21, 2008 at 5:42 am
I've never done this with views, but stored procedures wortk like a charm.
The trick is in the Access ado form property called Input Parameter.
You place and equalization between your stored procedure variable and your form variable. if should look something like this:
@StartDate datetime=Forms!frmTest!dtStartDateField, @EndDate datetime=Forms!frmTest!dtEndDateField
Make sure you save this string somewhere, like the TAG property. If you change the recordsource property Access deletes everything in the Input Parameter field.
You could also build a connection and command object in ado and then use the CreateParameter commands to euqate your form input with your stored procedure parameters.
April 21, 2008 at 5:51 am
Off the top of my head (/guesswork ahead):
I don't think Access can pass arguments to a view, nor can a view take arguments.
Arguments can be passed to a procedure (although I have played with that approach from Excel, not Access) so the call from Access would be something like EXEC PROC1 fromdate,todate. (Unlike function calls, procedure arguments are not enclosed in (parens).) This might be accomplished via a pass-through query in Access. At least in Excel, the argument prompts could not be enclosed in [brackets] as I had hoped. Instead, I needed to alter the MS Query parameters (arguments) in VB code.
SQL Server views are more restrictive than Access queries. (Beyond the fact that the SQL Sever create view wizard will reject view definitions that are acceptable under a CREATE VIEW foo AS statement.)
(BTW while I have used table returning UDFs, typically in a CROSS APPLY statement, it had never occurred to me to use one in a FROM statement. I learned something from your question.)
April 21, 2008 at 7:57 am
Access forms can directly connect to a table-value function. You don't need the view in between. You can even put the input parameters in the form definition.
In the properties of the form (or report), when you set the data source, put the data you want for the input parameters in the field for that. You can even put object names in there, like "Forms.[My Form].StartDate", if "StartDate" is the name of the field. Separate the parameters with commas, and you're good to go.
But don't put a view in the middle between the form and the function. Just put the form directly on top of the function, and you should be good to go. If the function follows the rules for updatable queries, Access can directly update the data in the table(s) under the function.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 21, 2008 at 8:58 am
Thank you GSquared, I didn't know that.
(If I keep hanging around here, I might someday get the hang of all this foolishness!)
April 21, 2008 at 9:03 am
GSquared (4/21/2008)
Access forms can directly connect to a table-value function. You don't need the view in between. You can even put the input parameters in the form definition.In the properties of the form (or report), when you set the data source, put the data you want for the input parameters in the field for that. You can even put object names in there, like "Forms.[My Form].StartDate", if "StartDate" is the name of the field. Separate the parameters with commas, and you're good to go.
But don't put a view in the middle between the form and the function. Just put the form directly on top of the function, and you should be good to go. If the function follows the rules for updatable queries, Access can directly update the data in the table(s) under the function.
I'm not sure that helps the original question. I was under the impression that the return set from a function (even in Access) was read-only, so using it as the basis for a form will work, but only if the form is to be read-only. Especially in this case, where the ADP function is in fact a SQL Server function.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 21, 2008 at 1:52 pm
In my last job, I spent 6 years managing an Access app on top of an SQL database. I had a lot of forms that used functions and were very definitely updatable.
The rules for updatable functions are the same as for updatable views. Only "inline table-value functions" can work this way. Scalar functions and multi-select functions are read-only.
Only one table can be updated in a function/view by any given update command, even if there are joins in the function/view.
If the view/function returns any aggregated columns, or uses distinct or group by, it can't be directly updated. Derived tables/CTEs in the function that have aggregates are okay, but only if those columns are not returned in the primary select statement.
If the view/function includes a derived table/CTE, none of those columns can be in the final select.
If all of those conditions are met, the function or view can be used to update underlying data.
Basically, the UDF at that point works just like a view with input parameters, except it keeps its own execution plan (unlike a view in most circumstances). Unlike a proc, it can be used in From clauses, joined to, etc. Advantages and drawbacks, but I found them very useful in that Access app.
(This was not a simple application. Over 400 tables, thousands of procs, etc. Very, very efficient business automation, all managed through that app.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 22, 2008 at 11:20 am
I don't know why you just couldn't use:
select * from dbo.function1( '2007-12-01', '2007-12-31' )
instead of wrapping the function (itself a wrapper) inside a view.
Your problem is that there is no way to pass parameter values into a view:
select * from dbo.ViewName( @p1, @p2 )
If your function works correctly, use it. Think of it as a view that does take parameters.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
April 22, 2008 at 12:18 pm
Tomm Carr (4/22/2008)
I don't know why you just couldn't use:
select * from dbo.function1( '2007-12-01', '2007-12-31' )instead of wrapping the function (itself a wrapper) inside a view.
Your problem is that there is no way to pass parameter values into a view:
select * from dbo.ViewName( @p1, @p2 )If your function works correctly, use it. Think of it as a view that does take parameters.
In some cases, the function might be too powerful, and it seems safer to grant select access at the view level. For example, the function parameters might specify the row scope of the returned data. If the user can access the function directly they can reach data they are not authorized to see. The calling view provides a secure environment from which predefined (or calculated) function parameters can be created.
On the on the hand, I guess the function could do it's own check on the appropriateness of the parameters for the user making the call. I guess it all depends...
April 22, 2008 at 1:03 pm
Yes, you can add role-based intelligence inside the function, or you could have different functions and control access to them through roles. Personally, I prefer the latter method as I can play around with, er, fine tune the roles without having to modify and recompile code.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
April 22, 2008 at 1:23 pm
If you're using domain authentication for the connection to the server (Windows security), you can include Where statements in the function that use the User_ID to determine what rows to return.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply