Hiding a SP in Access under Query, but retain Execute on SP

Hello,

Access XP, SQL Server 2000

Is it possible to hide a SP under Queries in Access, yet still be able
to Execute it from Access?

We hooked up a custom form to accept the input parameters (MS Feature
Request!) for the Stored Procedure. We had two problems with MS's
'Input Parameter' dialog: 1) We could not customize, 2) We continually
received a message from Access stating, "The stored procedure executed
successfully but did not return records" ('SET NOCOUNT ON' gave no
joy).

Below is the sample code we are using.
TestInsert is the SP
txtName is a text box on the form
@.name is char(80)

Private Sub cmdAdd_Enter()

Dim cmd As New ADODB.Command
Dim par As Parameter

Set cmd.ActiveConnection = CurrentProject.Connection

cmd.CommandText = "TestInsert"
cmd.CommandType = adCmdStoredProc

Set par = cmd.CreateParameter("@.name", adVarChar, _
adParamInput, Len(txtName), txtName)

cmd.Parameters.Append par

cmd.Execute

Set cmd = Nothing

End Sub

Any help would be appreciated. We successfully hid our Tables, and
allowed access through Views. But we can't seem to find a good work
around here.

Thanks

Jeff
Jeffrey Walton
noloaderNoloader (noloader@.yahoo.com) writes:
> Is it possible to hide a SP under Queries in Access, yet still be able
> to Execute it from Access?
> We hooked up a custom form to accept the input parameters (MS Feature
> Request!) for the Stored Procedure. We had two problems with MS's
> 'Input Parameter' dialog: 1) We could not customize, 2) We continually
> received a message from Access stating, "The stored procedure executed
> successfully but did not return records" ('SET NOCOUNT ON' gave no
> joy).

I have to admit that I understand very little of this. But it could
be because I don't know Access. Probably you should put this question
to comp.databases.ms-access instead.

As for the procedures not returning result sets - did you expect them
to return result sets? Did you run them from Query Analyzer?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||The most common advice is not to use the @. character in variable nemes with
the Jet engine. If this does not work then a specific cast of the input vale
to the parameter type might work.
Martin
"Noloader" <noloader@.yahoo.com> wrote in message
news:6b543aa7.0405061431.45bfa457@.posting.google.c om...
> Hello,
> Access XP, SQL Server 2000
> Is it possible to hide a SP under Queries in Access, yet still be able
> to Execute it from Access?
> We hooked up a custom form to accept the input parameters (MS Feature
> Request!) for the Stored Procedure. We had two problems with MS's
> 'Input Parameter' dialog: 1) We could not customize, 2) We continually
> received a message from Access stating, "The stored procedure executed
> successfully but did not return records" ('SET NOCOUNT ON' gave no
> joy).
> Below is the sample code we are using.
> TestInsert is the SP
> txtName is a text box on the form
> @.name is char(80)
> Private Sub cmdAdd_Enter()
> Dim cmd As New ADODB.Command
> Dim par As Parameter
> Set cmd.ActiveConnection = CurrentProject.Connection
> cmd.CommandText = "TestInsert"
> cmd.CommandType = adCmdStoredProc
> Set par = cmd.CreateParameter("@.name", adVarChar, _
> adParamInput, Len(txtName), txtName)
> cmd.Parameters.Append par
> cmd.Execute
> Set cmd = Nothing
> End Sub
> Any help would be appreciated. We successfully hid our Tables, and
> allowed access through Views. But we can't seem to find a good work
> around here.
> Thanks
> Jeff
> Jeffrey Walton
> noloader|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94E293F8A543Yazorman@.127.0.0.1>...
> Noloader (noloader@.yahoo.com) writes:
> > Is it possible to hide a SP under Queries in Access, yet still be able
> > to Execute it from Access?
> > We hooked up a custom form to accept the input parameters (MS Feature
> > Request!) for the Stored Procedure. We had two problems with MS's
> > 'Input Parameter' dialog: 1) We could not customize, 2) We continually
> > received a message from Access stating, "The stored procedure executed
> > successfully but did not return records" ('SET NOCOUNT ON' gave no
> > joy).
> I have to admit that I understand very little of this. But it could
> be because I don't know Access. Probably you should put this question
> to comp.databases.ms-access instead.
Thanks for the suggestion. Done.

> As for the procedures not returning result sets - did you expect them
> to return result sets? Did you run them from Query Analyzer?
The SP does not return any records (its a basic insert). I don't
understand why Access is throwing the dialog. I take a cockpit
approach - silent is good. Don't flash the lights and blow the whistle
until something goes wrong.

Thanks for the help Erland and Martin.

Jeff|||noloader@.yahoo.com (Noloader) wrote in message news:<6b543aa7.0405061431.45bfa457@.posting.google.com>...
> Hello,
> Access XP, SQL Server 2000
> Is it possible to hide a SP under Queries in Access, yet still be able
> to Execute it from Access?
> We hooked up a custom form to accept the input parameters (MS Feature
> Request!) for the Stored Procedure. We had two problems with MS's
> 'Input Parameter' dialog: 1) We could not customize, 2) We continually
> received a message from Access stating, "The stored procedure executed
> successfully but did not return records" ('SET NOCOUNT ON' gave no
> joy).
> Below is the sample code we are using.
> TestInsert is the SP
> txtName is a text box on the form
> @.name is char(80)
> Private Sub cmdAdd_Enter()
> Dim cmd As New ADODB.Command
> Dim par As Parameter
> Set cmd.ActiveConnection = CurrentProject.Connection
> cmd.CommandText = "TestInsert"
> cmd.CommandType = adCmdStoredProc
> Set par = cmd.CreateParameter("@.name", adVarChar, _
> adParamInput, Len(txtName), txtName)
> cmd.Parameters.Append par
> cmd.Execute
> Set cmd = Nothing
> End Sub
> Any help would be appreciated. We successfully hid our Tables, and
> allowed access through Views. But we can't seem to find a good work
> around here.
> Thanks
> Jeff
> Jeffrey Walton
> noloader

> 2) We continually received a message from Access
> stating, "The stored procedure executed successfully
> but did not return records" ('SET NOCOUNT ON' gave no joy).

We modified the Execute call:

Dim cmd As New ADODB.Command
Dim par As Parameter

Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "TestInsert"
cmd.CommandType = adCmdStoredProc

Set par = cmd.CreateParameter("@.name", adVarChar, _
adParamInput, Len(txtName), txtName)

cmd.Parameters.Append par

cmd.Execute , , adExecuteNoRecords

Set cmd = Nothing
Set par = Nothing

Jeff