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]

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

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]

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

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

[rmurgia]

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.

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

[rmurgia]

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

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]

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

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 :-)