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.
解决方案Why not just link to the table? Why would you need to import all that data into Access?
@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.
Use a passthrough query, that will send the SQL directly to the backend server for processing.