Sclar-valued Function & access VB question

[YevS]

Hi,

I'm trying to get a return value from an Scalar-valued function in an SQL server into access.

function is as follows:

[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]CREATE [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]FUNCTION[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [dbo][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][SQLStr][/SIZE]
[SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]()[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]RETURNS [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]nvarchar[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]2000[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]AS[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]BEGIN[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Declare[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] @SQL [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]nvarchar[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]2000[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Set[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] @sql [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]N'SELECT...'[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]return[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] @sql[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]END[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]GO[/SIZE]

and I'm trying to call it like this:

[SIZE=2]Global GadoCn As ADODB.Connection[/SIZE]
[SIZE=2]Function OpenConnection()[/SIZE]
[SIZE=2]Set GadoCn = New ADODB.Connection[/SIZE]
 
[SIZE=2]If GadoCn.State <> 1 Then[/SIZE]
[SIZE=2]  With GadoCn[/SIZE]
[SIZE=2]      .ConnectionString = "PROVIDER=MSDASQL;DRIVER={SQL Server};SERVER=xxx;DATABASE=xxx;TRUSTED_CONNECTION=Yes;"[/SIZE]
[SIZE=2]      .CursorLocation = adUseServer[/SIZE]
[SIZE=2]      .Open[/SIZE]
[SIZE=2]  End With[/SIZE]
 
[SIZE=2]Else    'connection already open[/SIZE]
[SIZE=2]  Exit Function[/SIZE]
 
[SIZE=2]End If[/SIZE]
[SIZE=2]End Function[/SIZE]
 
 
 
[SIZE=2]Public Sub Refresh()   [/SIZE]
 
[SIZE=2]Dim adoCm As ADODB.Command[/SIZE]
[SIZE=2]Set adoCm = New ADODB.Command[/SIZE]
[SIZE=2]With adoCm[/SIZE]
[SIZE=2]OpenConnection[/SIZE]
[SIZE=2]Set .ActiveConnection = GadoCn[/SIZE]
[SIZE=2]  .CommandType = adCmdStoredProc[/SIZE]
[SIZE=2]  .CommandText = "SQLStr"[/SIZE]
[SIZE=2]  .Execute[/SIZE]
[SIZE=2]End With   [/SIZE]
 
[SIZE=2]If GadoCn.State = 1 Then GadoCn.Close[/SIZE]
 
 
[SIZE=2]End Sub[/SIZE]

Unfortunately I dont know how to assign the return value to a string in access. Any help would be appreciated.

Cheers.