Dynamic SQL vs saved parameter queries

Just having a read through some of Bob Barrows old posts recommending saved
parameter queries and Access.
Id like to give it a go, but have a question :-)

Consider this example SQL

sSQL = "INSERT INTO tbltest (username, "
If Admin then sSQL = sSQL & " [password], "
sSQL = sSQL & " category)
sSQL = sSQL & "VALUES ('Value1',"
If Admin then sSQL = sSQL & " 'Password', "
sSQL = sSQL & " 'category')"

So - the SQL string can have 2 different outcomes.
Is it possible to somehow use the same saved parameter query, considering
there may be one or more outcome? or would there just have to be a seperate
spq for each option?

Cheers

Re: Dynamic SQL vs saved parameter queries

dave wrote:
> Just having a read through some of Bob Barrows old posts recommending
> saved parameter queries and Access.
> Id like to give it a go, but have a question :-)
>
> Consider this example SQL
>
> sSQL = "INSERT INTO tbltest (username, "
> If Admin then sSQL = sSQL & " [password], "
> sSQL = sSQL & " category)
> sSQL = sSQL & "VALUES ('Value1',"
> If Admin then sSQL = sSQL & " 'Password', "
> sSQL = sSQL & " 'category')"
>
> So - the SQL string can have 2 different outcomes.
> Is it possible to somehow use the same saved parameter query,
> considering there may be one or more outcome? or would there just
> have to be a seperate spq for each option?
>
> Cheers
No. Flow control is not possible with Jet saved parameter queries (as it is
in SQL Server stored procedures). You will need to have two saved queries:
one of admins, and one for non admins.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.