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

[Markus Eßmayr]

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

 

[Bob Barrows]

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 ...

 

[Tom van Stiphout]

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_Connection=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

 

[Markus Eßmayr]

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