Using a table-valued function of a linked SQL Server database "Markus Eßmayr" <essmayr/at/racon-linz.at> 10/30/2008 12:16:08 PM 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

Re: Using a table-valued function of a linked SQL Server database "Bob Barrows" <reb01501[ at ]NOyahoo.SPAMcom> 10/30/2008 1:37:23 PM Markus Eßmayr wrote: [Quoted Text] > 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

Re: Using a table-valued function of a linked SQL Server database "Markus Eßmayr" <essmayr/at/racon-linz.at> 10/30/2008 3:07:53 PM 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" <tom7744.no.spam[ at ]cox.net> schrieb im Newsbeitrag news:vuejg45m7l1su81c1c9iu667f30vq2ieeh[ at ]4ax.com... [Quoted Text] > 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 > > >>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 >>