Using a table-valued function of a linked SQL Server database

Hello,

is there a way, to use a table-valued function stored in an SQL Server
database, to get the results into a MS Access database?
It should also be possible to pass parameters to the function.

As the function is not listed in the linkable table list, I'm not sure how
to do that.

Is there a way to set up a Module, that uses the existing connection to a
specific linked table, executes any SQL against it and then returns the
results as a View in Access?

Thanks very much in advance!

Max

Markus Eßmayr wrote:
Hello,

is there a way, to use a table-valued function stored in an SQL Server
database, to get the results into a MS Access database?
It should also be possible to pass parameters to the function.

You could use a passthrough query:
select * from yourfunction(parmvalue)

You can use VBA to dynamically build this statement using parameter
values supplied by a user if needed. Unfortunately, this requires
concatenation of strings.

As the function is not listed in the linkable table list, I'm not
sure how to do that.

It can't be. A function is not a table. Not even a table-valued
function. So you can forget about linked tables


Is there a way to set up a Module, that uses the existing connection
to a specific linked table, executes any SQL against it and then
returns the results as a View in Access?

You're talking about adifferent thing here. A function is not a table
so you cannot link to it.

You can open a recordset against a function call, and then assign the
recordset to a form's Recordset property ...

--
HTH,
Bob Barrows

On Thu, 30 Oct 2008 13:16:08 +0100, "Markus Eßmayr"
essmayr/at/racon-linz.at wrote:

Not as a linked table, but you can return the data in an ADO
recordset. Then that recordset can be used to bind a form or report.
For example this calls a scalar function:
Dim rs As ADODB.Recordset
Dim conn As ADODB.Connection
Dim sql As String
Set conn = New ADODB.Connection
conn.Open "Driver={SQL Server Native Client
10.0};Server=MyServer;Database=MyDB;Trusted_Connec tion=yes;"
Set rs = New ADODB.Recordset
sql = "select dbo.MyScalarFunction('aaa', 'bbb')"
rs.Open sql, conn, adOpenKeyset, adLockReadOnly
Debug.Print "Function returns " & rs(0)
rs.Close
Set rs = Nothing
Set conn = Nothing

-Tom.
Microsoft Access MVP


Hello,

is there a way, to use a table-valued function stored in an SQL Server
database, to get the results into a MS Access database?
It should also be possible to pass parameters to the function.

As the function is not listed in the linkable table list, I'm not sure how
to do that.

Is there a way to set up a Module, that uses the existing connection to a
specific linked table, executes any SQL against it and then returns the
results as a View in Access?

Thanks very much in advance!

Max

Tom,

thanks very much.
That seems to be like what I need.
Is it possible to place that piece of code behind a View in Access?

As Access supports views with parameters, so I wonder, if it's possible to
take this parameters, execute the query using VBA and then return the
recordset as result of the view.

That would be the thing that would work best for me!

Thanks!
Max

"Tom van Stiphout" schrieb im Newsbeitrag
...
On Thu, 30 Oct 2008 13:16:08 +0100, "Markus Eßmayr"
essmayr/at/racon-linz.at wrote:

Not as a linked table, but you can return the data in an ADO
recordset. Then that recordset can be used to bind a form or report.
For example this calls a scalar function:
Dim rs As ADODB.Recordset
Dim conn As ADODB.Connection
Dim sql As String
Set conn = New ADODB.Connection
conn.Open "Driver={SQL Server Native Client
10.0};Server=MyServer;Database=MyDB;Trusted_Connec tion=yes;"
Set rs = New ADODB.Recordset
sql = "select dbo.MyScalarFunction('aaa', 'bbb')"
rs.Open sql, conn, adOpenKeyset, adLockReadOnly
Debug.Print "Function returns " & rs(0)
rs.Close
Set rs = Nothing
Set conn = Nothing

-Tom.
Microsoft Access MVP


Hello,

is there a way, to use a table-valued function stored in an SQL Server
database, to get the results into a MS Access database?
It should also be possible to pass parameters to the function.

As the function is not listed in the linkable table list, I'm not sure how
to do that.

Is there a way to set up a Module, that uses the existing connection to a
specific linked table, executes any SQL against it and then returns the
results as a View in Access?

Thanks very much in advance!

Max