Re: function as criteria for a query

Tech-Archive recommends: Fix windows errors by optimizing your registry

Write the value to a table with a single record in SQL Server.

--
Duane Hookom
MS Access MVP


"Paul" <Paul@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C5E5B509-9989-473C-960A-CA5DFEBDD8B4@xxxxxxxxxxxxxxxx
> What i'm trying to do is base an SQL (stored procedure / view) query
> on the Public variable "userId" so that various forms
> can display records relevant to the 'logged-in' user.
>
> This method works in an MDB query like a charm!
> What i'd like to do is use a similar method
> the ADP version of the database using sql server 2000 (desktop engine)
>
> I have created a public variable called "userId" it stores the user id
> number
> which was entered in a text box on a 'log-in' form.
>
> The function i've created uses the following syntax (as recomended in
> KB210388)
>
> Public Function fnUserId()
> fnUserId = userId
> End Function
>
> The syntax of the stored procedure is as follows:
>
> SELECT dbo.tblUsers.*
> FROM dbo.tblUsers
> WHERE (Id = fnUserId())
>
> Error message returned "ADO error 'fnUserId' is not a recognized function
> name"
>
> How do I get the stored procedure / view to base the value in the WHERE
> clause that is being held
> held in the public variable "userId"?
>
> "Duane Hookom" wrote:
>
>> As you have experienced, you can't use an Access function in an ADP
>> query/view. SQL 2000 does have the ability to create user-defined
>> functions.
>> Since we know nothing about your function, we can't offer alternative
>> syntax.
>>
>> --
>> Duane Hookom
>> MS Access MVP
>>
>>
>> "Paul" <Paul@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:9B038810-E1AF-47AB-8D6E-5A89F76E4EFB@xxxxxxxxxxxxxxxx
>> > i've successfully used a function result as the criteria for a query in
>> > access mdb database (as in KB210388) but i cant use the function in a
>> > similar
>> > query in an ADP database. I get the error: "ADO error 'functionName()'
>> > is
>> > not
>> > a recognized function name". Can anyone show me how to sort this out!
>>
>>
>>


.

Relevant Pages

  • Re: function result as criteria in a query
    ... This method works in an MDB query like a charm! ... I have created a public variable called "userId" it stores the user id number ... Public Function fnUserId() ... > In adp, the query is sent for execution to sql server, which is another ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Stored procedure question
    ... Vyas, MVP (SQL Server) ... > where UserID = @UserID ... > Vyas, MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Configuration of Login and password in VB6 program
    ... I have a VB6 project which the program needs to access SQL Server 2005 ... the userID is QA and the password is qapl. ... able to prevent the designer to have idea of login and password ...
    (microsoft.public.vb.general.discussion)
  • Re: ORDER BY in View and ADP Form
    ... So you are admitting now that Microsoft is NOT committed to ADP's. ... im disagreeing with that David Portas ... WHY DOES EVERY ACCESS MVP DIPSHIT IN THE WORLD NOT UNDERSTAND THE ... Views have no fixed ordering in SQL Server. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Configuration of Login and password in VB6 program
    ... I have a VB6 project which the program needs to access SQL Server 2005 ... the userID is QA and the password is qapl. ... able to prevent the designer to have idea of login and password ... they should probably still have the source code ...
    (microsoft.public.vb.general.discussion)