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

[Noloader]

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.ActiveConne ction = CurrentProject. Connection

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

Set par = cmd.CreateParam eter("@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]

Noloader (no******@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, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Martin Sutton]

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" <no******@yahoo .com> wrote in message
news:6b******** *************** ***@posting.goo gle.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.ActiveConne ction = CurrentProject. Connection

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

Set par = cmd.CreateParam eter("@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

[Noloader]

Erland Sommarskog <so****@algonet .se> wrote in message news:<Xn******* **************@ 127.0.0.1>...

Noloader (no******@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]

no******@yahoo. com (Noloader) wrote in message news:<6b******* *************** ****@posting.go ogle.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.ActiveConne ction = CurrentProject. Connection

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

Set par = cmd.CreateParam eter("@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.ActiveConne ction = CurrentProject. Connection
cmd.CommandText = "TestInsert "
cmd.CommandType = adCmdStoredProc

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

cmd.Parameters. Append par

cmd.Execute , , adExecuteNoReco rds

Set cmd = Nothing
Set par = Nothing

Jeff