Link MS Access to SQL Server Table-Valued function to pass parameters

[rmurgia]

I am linking Access to SQL Server to extract data from a table containing over 12,000,000 records. I would like to pass a parameter to SQL Server to be used on the table-valued function assuming that extracting the data in SQL Server would run faster than in Access. I created a table valued function in SQL Server, however, when I go to Access to create the linked table, I only see tables and views. Does anyone know any other way to pass a parameter from Access to SQL Server to have the Server extract the data? Any other suggestions to speed up the process would be appreciated.

[Rabbit]

Why not just link to the table? Why would you need to import all that data into Access?

[rmurgia]

@Rabbit
That is what I am currently doing, but the queries against such a large table take long to run. I felt if I could pass a parameter to a function in SQL Server, the data would be queried in SQL Server rather than in Access and would run faster. Lets say you had a table with 12,000,000 records, but the table in access only needed records belonging to a particular group which made up 1000 records in the table. You could create a view which selects those 1000 records and have the Access table link to the view. That would work for me except, I wouldn't know the criterion until the user selected it, so it would have to be done dynamically. The user would have to select the group they needed, and then I would have to pass this as a parameter into the function.

[Rabbit]

Use a passthrough query, that will send the SQL directly to the backend server for processing.

[NeoPa]

I needed this once, and I set up a table on the server to contain the filter I required then a View to return records from the large table that matched the table in the 'Filter' table. It may not be the most elegant solution, but it worked.

[rmurgia]

@Rabbit
Okay. That works but it can't read a View. Does the passthrough query only work with tables?

[rmurgia]

@NeoPa
Yes, this would do it but there are too many different choices the user could make.

[rmurgia]

@rmurgia
Correction: It did work with the view. I was not typing the view correctly. Thanks for the solution. The query is running much faster!

[Rabbit]

If you want to see if you can make it faster, read this article and see if any of these optimizations apply: http://bytes.com/topic/sql-server/in...ze-sql-queries

[NeoPa]

RMurgia:
Yes, this would do it but there are too many different choices the user could make.

I very much doubt that would have been your optimal solution, anyway. I didn't have Rabbit available (or that excellent article he's linked you to) when I came up with that approach (and I suspect my requirements were more straightforward).

[rmurgia]

@NeoPa
I appreciate your input. There may also be some cases where I would use that method.

[NeoPa]

Thank you. In truth, it was submitted more as a trigger for thought than a suggestion, but I'm glad if it's any help :-)