Lyle Fairfield <MissingAddress@Invalid.Com> wrote in message news:<Xns94E276D93D3A9FFDBA@130.133.1.4>...[color=blue]
>
noloader@yahoo.com (Noloader) wrote in news:6b543aa7.0405070700.1133bcc0
> @posting.google.com:
>[color=green]
> > 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[/color]
>
> 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".[/color]
Hi Lyle,
[color=blue]
> Was the name inserted?[/color]
Yes
[color=blue]
> Typically SELECT statements return records while INSERT,DELETE
> and UPDATE statements do not.[/color]
Yes - we changed cmd.execute to the following to dispose of the
message box (apparently, Access needs a hint):
cmd.Execute , , adExecuteNoRecords
[color=blue]
> In what way do you want to customize?[/color]
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.
[color=blue]
> It's generally not a great idea use words like "Name",
> "Date" etc in Databases.[/color]
We'll definetly use better naming conventions in production.
Thanks for the reply,
Jeffrey Walton
noloader
