Re: How do you capture the value of a local variable from SQL Server?

"acraft" <u42371@uwe> wrote in message news:81a4f7f2e7eca@uwe...
> How do you capture this variable from SQL Server?
> Example of T-SQL code:
>
> declare @strVariable varchar(20)
> set @strVariable = 'This was a success...'
> select @strVariable as NEW_FIELD
>
> How do I capture the returned value "This was a success..." without
> creating
> a temporary table on the SQL Server? I am using MS Access and trying to
> use
> the ADO.Recordset.
>
>
> Should I use a different method? Any suggestions or sample code?
>


Something like this should work. Substitute your own connection string and
stored procedure name of course.

Public Sub TestSub()

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cnn = New ADODB.Connection
cnn.ConnectionString = "Provider=SQLNCLI.1;" & _
"Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=test;" & _
"Data Source=K9\SQLEXPRESS"
cnn.Open
Set rst = New ADODB.Recordset
rst.Open "test", cnn, , , adCmdStoredProc
Debug.Print rst.Fields(0).Value
rst.Close
cnn.Close


End Sub

--
Brendan Reynolds