是否可以将Access表单连接到SQL Server视图 [英] Is it possible to connect an Access form to a SQL Server view

问题描述

我正在将Access DB迁移到SQL Server，并且一切进展缓慢，但是我不确定如何将Access表单连接到SQL Server视图.

I'm migrating an Access DB to SQL Server and everything is slowly coming along but Im not sure how to connect the Access forms to the SQL Server views.

到目前为止，我已经将所有表链接到SQL Server，并且Im正在将Access查询迁移到视图中，但是我遇到了此错误，A2SS0069: External variable cannot be converted

So far I have all the tables linked to SQL Server and Im working on migrating the Access queries into views, but Ive got this error, A2SS0069: External variable cannot be converted

它引用了我的Access文件中的一个表单:

which references a form in my Access file:

  SELECT TOP 9223372036854775807 WITH TIES 
      [AcuteHospitals].[NHSN_ID], 
      [AcuteHospitals].[HospitalName], 
      [Location_LOV].[Description] AS Location, 
      Sum([RateTable_CLABData].[clabcount]) AS [Number of CLABSI], 
      Sum([RateTable_CLABData].[numcldays]) AS [Central Line Days], 
      [RateTable_CLABData].[CLAB_Mean] AS [National Average]
   FROM 
      (([AcuteHospitals] 
         LEFT JOIN [RateTable_CLABData] 
         ON [AcuteHospitals].[NHSN_ID] = [RateTable_CLABData].[orgID]) 
         LEFT JOIN [Location_LOV] 
         ON [RateTable_CLABData].[loccdc] = [Location_LOV].[CDCLoc]) 
         LEFT JOIN [SummaryYQ_LOV] 
         ON [RateTable_CLABData].[summaryYQ] = [SummaryYQ_LOV].[StartDate]
   WHERE ((([SummaryYQ_LOV].[SummaryYQ]) = forms!YQ_Location.text5 ))
   GROUP BY 
      [AcuteHospitals].[NHSN_ID], 
      [AcuteHospitals].[HospitalName], 
      [Location_LOV].[Description], 
      [RateTable_CLABData].[CLAB_Mean], 
      [RateTable_CLABData].[loccdc]
   HAVING ((([RateTable_CLABData].[loccdc]) NOT LIKE '%ped%'))
   ORDER BY [AcuteHospitals].[HospitalName], [RateTable_CLABData].[loccdc]

其行WHERE ((([SummaryYQ_LOV].[SummaryYQ]) = forms!YQ_Location.text5 ))

所以我需要知道是否有可能，以及如何获取这个新视图以与Access表单关联.

So I need to know if it's possible and how to get this new view to connect with the Access form.

推荐答案

问题在这里

位置((((([SummaryYQ_LOV].[SummaryYQ])= forms！YQ_Location.text5 )))

您无法将此类Access查询转换为SQL视图，但可以使用存储代替过程，然后将forms!YQ_Location.text5字段中的值作为参数传递.

You cannot convert such Access query into a SQL View, but you can use Stored Procedure instead and pass value from the field forms!YQ_Location.text5 as parameter.

此外，您不需要此TOP 9223372036854775807 WITH TIES，它是多余的.

Also, you don't need this TOP 9223372036854775807 WITH TIES it is redundant.

这篇关于是否可以将Access表单连接到SQL Server视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！