链接MS访问SQL Server表值函数以传递参数 [英] Link MS Access to SQL Server Table-Valued function to pass parameters

问题描述

我将Access连接到SQL Server以从包含超过12,000,000条记录的表中提取数据。我想将一个参数传递给SQL Server，以便在表值函数上使用，假设在SQL Server中提取数据的速度比在Access中运行得快。我在SQL Server中创建了一个表值函数，但是，当我去Access创建链接表时，我只看到表和视图。有没有人知道将任何其他方法从Access传递给SQL Server以让服务器提取数据？任何其他建议加快这个过程将不胜感激。

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.

推荐答案

为什么不直接链接到表？为什么需要将所有数据导入Access？

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

@Rabbit


这就是我目前正在做的事情，但对这么大的表的查询需要很长时间才能运行。我觉得如果我可以将参数传递给SQL Server中的函数，那么数据将在SQL Server中而不是在Access中查询并且运行得更快。假设您有一个包含12,000,000条记录的表，但访问中的表只需要属于特定组的记录，这些记录在表中构成了1000条记录。您可以创建一个视图，选择这1000条记录并将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.

使用直通查询，将SQL直接发送到后端服务器进行处理。

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

这篇关于链接MS访问SQL Server表值函数以传递参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！