Function and Request are not good friends

[Alain]

In my MDB application I use a request (Included in the source propriety of
my form) similar to this:

SELECT Table1.Champ1, Table1.Champ2, ...
FROM [Table1]
WHERE ((([Table1].[Champ1])=GetRéfIC())) Or ((([Table1].[Champ2])=-1))
ORDER BY [Table1].[Champ1];

GetRéfIC beeing a function allowing me to transmit a value as a parmeter..

Unfortunamtly in my ADP version, I have an error during the opening of my
form tellin:
'getRéfIC' is not a function name recognise ! (sorry again the translation
maybe poor)

What should I do in order that my function, and by the way my parameter,
would be taken into account in my request ?


merci
@+
Alain

 

[Jacek Segit]

Witaj Alain,
W Twoim li¶cie datowanym 12 maja 2004 (06:47:58) mo¿na przeczytaæ:

A> In my MDB application I use a request (Included in the source propriety of
A> my form) similar to this:
A> SELECT Table1.Champ1, Table1.Champ2, ...
A> FROM [Table1]
A> WHERE ((([Table1].[Champ1])=GetRéfIC())) Or ((([Table1].[Champ2])=-1))
A> ORDER BY [Table1].[Champ1];
A> GetRéfIC beeing a function allowing me to transmit a value as a parmeter..
A> Unfortunamtly in my ADP version, I have an error during the opening of my
A> form tellin:
A> 'getRéfIC' is not a function name recognise ! (sorry again the translation
A> maybe poor)
A> What should I do in order that my function, and by the way my parameter,
A> would be taken into account in my request ?

If its source of form, you can set parameters value in
InputParameters property. It could be reference to control like
forms!myform!mycontrol or function name, for ex.:

@myparameter varchar(50)=GetRefIC(), @myparameter2 int=forms![myform]!mycontrol
etc.

the source of form should look like:

/* start */
CREATE PROCEDURE dbo.aa
@myparameter1 int --i dont know about data type of data returned from function
AS
SELECT Table1.Champ1, Table1.Champ2
FROM [Table1]
WHERE ((([Table1].[Champ1])=@myparameter1)) Or ((([Table1].[Champ2])=-1))
ORDER BY [Table1].[Champ1]

return
/* end */

I dont remember, if you can put function name in ComboBox.rowsource
property (I mean: exec dbo.mystoredprocedure
@parameter1=MyFunction())
but IMO (I cannot check it now) it can't be put
and you have to change rowsource from VB:
me.mycombobox.rowsource = "exec dbo.mystoredprocedure @parameter1='"
& forms!myform!mycontrol & "', @parameter2=" & myfunction2()

Regards
Jacek Segit

 

[Alain]

Hello Jack

Thank you very much for your help, I have been able to create my very first
stored procedure.

Let me just ask you these few questions .

- what is the meaning of "IMO" ?
- I did not succeed to include directly in my Form Source the request: I
was obliged to create a stored procedure and
call this procedure in my formulaire source. +> is it possible to
have no stored procedure and to have directly the request
written in the Source property of my form ? how can I do that ? Or
will I be always obiged to refer to stored procedures ?

many thanks again
regards
Alain

 

[Jacek Segit]

Witaj Alain,
W Twoim li¶cie datowanym 12 maja 2004 (10:25:34) mo¿na przeczytaæ:

A> Hello Jack
A> Thank you very much for your help, I have been able to create my very first
A> stored procedure.
A> Let me just ask you these few questions .

A> - what is the meaning of "IMO" ?


In My Opinion
(I used it in meaning: "I am not sure, but maybe it is as I said")

A> - I did not succeed to include directly in my Form Source the request: I
A> was obliged to create a stored procedure and
A> call this procedure in my formulaire source.
A> is it possible to
A> have no stored procedure and to have directly the request
A> written in the Source property of my form ?

But what type of request?

In MDB you can use: nothing, query or table (maybe function?? i
don't know) or sql string
In ADP source property should have one of below "value":
nothing (blank field)
stored procedure
view

You can also set recordset of form via VB, what do you connect to in
that moment is your choice, whatever you want to connect to (for
example MDB), but very often form is read-only :-(

But I think you want to put into source property SQL string, do you?
Yes, it can be:
select u.* from users as u

but you CANNOT use your own/system VB-based functions
for example "select * from users where
identification=myJCurrentuser()"
You can change this string via VB
strSQl = "select * from users where identification='" &
myJCurrentuser() & "'"
but its strictly CLIENT side query
and you lose optimalization of storedprocedures and transfer more
(I mean: often) data between client and server

Thats why I suggested to use stored procedure

Regards
Jacek Segit

 

[Alain]

many thanks for your answer.

In my MDB application I have included most of my requests in VB code or in
my Forms in order
so I can provide an MDE application to the user and he will not be able to
modify (voluntary or not)
the request.

In an APD project, I beleive that the user could acces and modified the
stored procedure and the view.

So may be best way would be to code directily in VB my form like
Form.recordSource = "SELECT .... FROM ...", in this case I can use function
and the user could not
access this request.

Do you think I am wright doing that or should I better used Stored
procedures ?

Best regards
Alain

 

[Jacek Segit]

Witaj Alain,
W Twoim li¶cie datowanym 13 maja 2004 (17:50:35) mo¿na przeczytaæ:

A> In my MDB application I have included most of my requests in VB code or in
A> my Forms in order
A> so I can provide an MDE application to the user and he will not be able to
A> modify (voluntary or not)
A> the request.
A> In an APD project, I beleive that the user could acces and modified the
A> stored procedure and the view.

If you grant access/modification/select/whatyouwanttodo
the user could
If not - answer is obvious

You can also grant execute on storedprocedure and deny reading the
procedure SQL code (for reading the storedprocedure code Access use
"system" procedures, for example master..sp_helptext If I correctly remember)

A> So may be best way would be to code directily in VB my form like
A> Form.recordSource = "SELECT .... FROM ...", in this case I can use function
A> and the user could not
A> access this request.
A> Do you think I am wright doing that or should I better used Stored
A> procedures ?

But If this code should work you HAVE TO grant select on particular
table and user can do this not by the form (just open main program
view).

If you don't want user to view the sqlcode or view all the table (or
rather all part of table you have granted - you can grant and deny
selection/update on specific column) you should use
Command and Connection other then CurrentProject.Connection
(Currentproject.connection.execute "" execute your sqlcode or return
data)
or use storedprocedure/view

Regards
Jacek Segit

 

[Alain]

Thank you very much for your help Jacek

so long
Alain