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? (Similar to hiding Tables, then using
Views)

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

The trivial Stored Procedure looks as follows (and does not return any
records):

CREATE PROCEDURE TestInsert (@Name char(80))
AS SET NOCOUNT ON
INSERT INTO TestTable (Name)
VALUES (@Name)

Any help would be appreciated. We can't seem to find a good work
around here.

Thanks

Jeff
Jeffrey Walton
noloader

[Lyle Fairfield]

no******@yahoo.com (Noloader) wrote in news:6b543aa7.0405070700.1133bcc0
@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? (Similar to hiding Tables, then using
Views)

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

The trivial Stored Procedure looks as follows (and does not return any
records):

CREATE PROCEDURE TestInsert (@Name char(80))
AS SET NOCOUNT ON
INSERT INTO TestTable (Name)
VALUES (@Name)

Any help would be appreciated. We can't seem to find a good work
around here.

Thanks

Jeff
Jeffrey Walton
noloader

Was the name inserted?
Typically SELECT statements return records while INSERT,DELETE and UPDATE
statements do not.
Your stored procedure takes a fixed length string [char(80)]. Why did you
send it a variable length parameter [adVarChar]?
In what way do you want to customize?
It's generally not a great idea use words like "Name", "Date" etc in
Databases. Munge them up somehow so that no technology confuses them with the
name property of some object.
In my largest table (375 298 467 records) I use "RedHeadName".
--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Noloader]

Lyle Fairfield <Mi************@Invalid.Com> wrote in message news:<Xn*******************@130.133.1.4>...

no******@yahoo.com (Noloader) wrote in news:6b543aa7.0405070700.1133bcc0
@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? (Similar to hiding Tables, then using
Views)

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

The trivial Stored Procedure looks as follows (and does not return any
records):

CREATE PROCEDURE TestInsert (@Name char(80))
AS SET NOCOUNT ON
INSERT INTO TestTable (Name)
VALUES (@Name)

Any help would be appreciated. We can't seem to find a good work
around here.

Thanks

Jeff
Jeffrey Walton
noloader
Was the name inserted?
Typically SELECT statements return records while INSERT,DELETE and UPDATE
statements do not.
Your stored procedure takes a fixed length string [char(80)]. Why did you
send it a variable length parameter [adVarChar]?
In what way do you want to customize?
It's generally not a great idea use words like "Name", "Date" etc in
Databases. Munge them up somehow so that no technology confuses them with the
name property of some object.
In my largest table (375 298 467 records) I use "RedHeadName".

Hi Lyle,
Was the name inserted? Yes
Typically SELECT statements return records while INSERT,DELETE
and UPDATE statements do not. Yes - we changed cmd.execute to the following to dispose of the
message box (apparently, Access needs a hint):
cmd.Execute , , adExecuteNoRecords
In what way do you want to customize? Too many things to name - I suppose it is good from Access's point of
view, but very poor from a User Interface design stand point.
It's generally not a great idea use words like "Name",
"Date" etc in Databases.

We'll definetly use better naming conventions in production.

Thanks for the reply,
Jeffrey Walton
noloader