"Uri Dimant" wrote:

> Hi
>
> CREATE PROCEDURE qryCompanyName
> @Company nvarchar OUTPUT
> AS
> SELECT Top 1 @Company = CompanyName
> FROM dbo.tblDisplayOptions
> GO
> -- Declare the variable to receive the output value of the procedure.
> DECLARE @Company_out nvarchar
>
> -- Execute the procedure and save the output value in a variable.
>
> EXECUTE qryCompanyName
> @Company = @Company_out OUTPUT
>
> SELECT @Company_out
>
>
>
>
>
> "NevilleT" <NevilleT@discussions.microsoft.com> wrote in message
> news:5F951F57-F299-4DD1-B12E-7FE6BAFC371A@microsoft.com...
> >I have an Access Project that is using a SQL Server 2000 database. I want
> >to
> > create a function in Access to read a table called tblDisplayOptions that
> > only has one record. It is used for configuration. One of the fields is
> > called CompanyName. I want to retrieve the company name and use it on
> > various screens and reports. The stored procedure is:
> >
> > CREATE PROCEDURE qryCompanyName
> > @Company nvarchar OUTPUT
> > AS
> > SELECT @Company = Top 1 CompanyName
> > FROM dbo.tblDisplayOptions
> > GO
> >
> > The proc fails with error
> >
> > Server: Msg 156, Level 15, State 1, Procedure qryCompanyName, Line 5
> > Incorrect syntax near the keyword 'Top'.
> > Server: Msg 2812, Level 16, State 62, Line 5
> > Could not find stored procedure 'qryCompanyName'.
> >
> > The second part of the question is, assuming the proc works, how do I get
> > the parameter in VBA? I set up a parameter in VBA and ran the stored proc
> > but not sure how I recover the value into a string variable to use
> > elsewhere.
> > The relevant part of the code is:
> >
> > Dim cmd As ADODB.Command
> > Dim prmCompany As ADODB.Parameter
> >
> > Set cmd = New ADODB.Command
> > With cmd
> > .ActiveConnection = CurrentProject.Connection ' Use
> > current connection
> > .CommandText = "qryCompanyName" ' Name of the stored procedure
> > .CommandType = adCmdStoredProc
> >
> > Set prmCompany = cmd.CreateParameter(Name:="Company", _
> > Type:=adParamReturnValue, _
> > Direction:=adParamOutput) ' Create a
> > prameter
> > .Parameters.Append prmCompany
> >
> > .Execute ' Execute the
> > command
> >
> > Set cmd = Nothing
> > End With
> >
> >
> >
> >
>
>