By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,140 Members | 1,301 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,140 IT Pros & Developers. It's quick & easy.

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

P: 63
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.
Mar 30 '12 #1

✓ answered by Rabbit

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

Share this Question
Share on Google+
11 Replies


Rabbit
Expert Mod 10K+
P: 12,357
Why not just link to the table? Why would you need to import all that data into Access?
Mar 30 '12 #2

P: 63
@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.
Mar 30 '12 #3

Rabbit
Expert Mod 10K+
P: 12,357
Use a passthrough query, that will send the SQL directly to the backend server for processing.
Mar 30 '12 #4

NeoPa
Expert Mod 15k+
P: 31,419
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.
Mar 30 '12 #5

P: 63
@Rabbit
Okay. That works but it can't read a View. Does the passthrough query only work with tables?
Mar 31 '12 #6

P: 63
@NeoPa
Yes, this would do it but there are too many different choices the user could make.
Mar 31 '12 #7

P: 63
@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!
Mar 31 '12 #8

Rabbit
Expert Mod 10K+
P: 12,357
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
Mar 31 '12 #9

NeoPa
Expert Mod 15k+
P: 31,419
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).
Apr 1 '12 #10

P: 63
@NeoPa
I appreciate your input. There may also be some cases where I would use that method.
Apr 2 '12 #11

NeoPa
Expert Mod 15k+
P: 31,419
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 :-)
Apr 2 '12 #12

Post your reply

Sign in to post your reply or Sign up for a free account.