具有SQL Server后端查询存储最佳实践的MS Access前端

Question

我正在努力更好地优化我的项目组织。使用MS Access作为前端，使用SQL Server作为后端，我的目标是尽可能多地分离，遵循最佳实践，并能够使用不同/多个前端(web、c#、python)。为了实现我的目标，我决定遵循一些设计决策：

• 使用视图在后端存储尽可能多的复杂查询
• 使用存储过程在后端进行尽可能多的处理
• 退出使用链接表，切换到使用简短查询在VBA代码中生成记录集

使用SQL Server的一些流行功能时，我发现了自己无法解决的问题。

• CTE可以参数化。视图不能。存储在视图中的CTE如何？如果答案是否定的，那么有没有其他方便的方法来存储CTE服务器端并从VBA调用它来构建记录集？其他参数化查询呢
• 在MS Access中编写复杂的查询作为VBA代码是一种痛苦。。。手指和眼睛。也许还有其他方法可以像Access中的老板一样存储查询
• 你如何组织你的前端-后端应用程序，特别是如果前端是MS Access，并且它打算移动到任何其他平台

TYA烟雾。

Answer 1

CTE的零-零与是否有参数有关。CTE的概念与在查询上编写查询(或者现在是视图上的视图)完全相同。代替必须编写(并保存)两个视图？你可以使用CTE。

然而，CTE在哪里最有用？尤其是在Access环境中？Access SQL的一个非常好的特性是，别名列可以在该SQL的表达式中重复使用。使用T-SQL语法，您不能！。结果是丑陋得可怕的T-SQL。

以Access中的这个典型查询(Access SQL)漂亮(简单+简单)的查询为例：

SELECT 
ID, Company, State,
(SELECT SUM(Price) FROM Purchases where Customer_ID = Customers.ID) as Purchased,
(SELECT SUM(Payment) FROM Payments where Customer_ID = Customers.id) as Payments,
(SELECT TaxRate from TaxRates where State = Customers.State) as TaxRate,
(Purchased - Payments) as Balance,
(Balance * TaxRate) as BalanceWithTax 

如你所见？在上文中，我们使用子查询来获取购买、付款和税率。然后我们在表达式中使用这3个。但是，对于T-SQL(SQL server)，我们不能使用别名列。所以，你现在明白了：

SELECT ID, Company, State,
(SELECT SUM(Price) FROM Purchases where Customer_ID = Customers.ID) as Purchased,
(SELECT SUM(Payment) FROM Payments where Customer_ID = Customers.id) as Payments,
(SELECT TaxRate from TaxRates where State = Customers.State) as TaxRate,
((SELECT SUM(Price) FROM Purchases where Customer_ID = Customers.ID) - 
(SELECT SUM(Payment) FROM Payments where Customer_ID = Customers.id)) as Balance,
( (SELECT SUM(Price) FROM Purchases where Customer_ID = Customers.ID) - 
(SELECT SUM(Payment) FROM Payments where Customer_ID = Customers.id)) * 
(SELECT TaxRate from TaxRates where State = Customers.State) as BAlanceWithTax
FROM Customers

上面是一个简单的例子。所以在上面，t-sql不允许在表达式中重用列(就像Access sql一样)。所以，以上这些，或者说将Access SQL转换为T-SQL是一种巨大的痛苦——只是彻底的痛苦。

因此，我们可以使用t-sql中的CTE来"；驯服"；以上内容。我们可以去：

WITH MyCTE
AS
(SELECT ID, Company, State,
(SELECT SUM(Price) FROM Purchases where Customer_ID = Customers.ID) as Purchased,
(SELECT SUM(Payment) FROM Payments where Customer_ID = Customers.id) as Payments,
(SELECT TaxRate from TaxRates where State = Customers.State) as TaxRate
FROM Customers)
SELECT ID, Company, State, Purchases, Payments, TaxRate,
(Purchases - Payments) as Balance,
( (Purchases - Payments) * TaxRate) as BalanceWithTax
from mycte

现在，请注意我们仍然必须重复平衡表达式，但至少我们现在可以在其他表达式中使用计算列，而不必重复它们。因此，CTE是一个很好的功能，可以帮助您转换Access SQL中所有那些令人惊叹的查询；非常好"；具有在T-SQL中重复(重用)任何Alised表达式的能力。

至于CTE和参数？它们之间的连接为零。CTE不支持或不具有任何参数，或多或少都不支持t-sql中的任何其他参数。(所以我在这里看到的CTE和参数之间没有联系)。

但是，问题的答案是什么？你能在视图中使用CTE吗？是的，你可以，没有什么理由不这样做。在CTE之前，你可以使用一些技巧，或者(喘息)简单地创建一个视图，保存它，然后创建另一个视图。因此，CTE实际上只是一种对查询进行查询的简单方法，而不必创建单独的视图进行查询。

但是，总的来说，CTE可以用作视图，主要原因是T-SQL缺少Access SQL的可爱功能，该功能允许重用别名列，而T-SQL没有。因此，在尝试将Access SQL转换为T-SQL时，CTE的问题尤其重要——它可以让您恢复Access在SQL中的出色功能，这也是T-SQL语法中您将非常怀念的功能。

在MS Access中编写复杂的查询作为VBA代码是痛苦的

好吧，我不能说.net或大多数其他语言中的代码中的内联sql也那么干净。你倾向于使用字符串串联，这总是一个挑战，但我不能说在say.net开发中这样做比VBA好得多。然而，在大多数情况下？

将sql文本作为保存的查询放入Access中，从而在代码中使用它。那么，用我们上面的例子运行？

你可以在代码中有这个：

dim strSQL    as string
strSQL = "SELECT ID, Company, State," & _
"(SELECT SUM(Price) FROM Purchases where Customer_ID = Customers.ID) as Purchased," & 
"(SELECT SUM(Payment) FROM Payments where Customer_ID = Customers.id) as Payments," & _
"(SELECT TaxRate from TaxRates where State = Customers.State) as TaxRate," & _
"(Purchased - Payments) as Balance," & _
"(Balance * TaxRate) as BalanceWithTax " & _
dim rst     as DAO.RecordSet
set rst = CurrentDB.OpenRecordSet(strSQL)

不能真的说上面的内容比用大多数其他语言写上面的内容更糟糕。但是，当然可以(在Access VBA中通常会)保存该查询，因此上面的代码就变成了一行代码。

例如：

set rst = CurrentDB.OpenRecordSet("qryGetBalance")

因此，VBA中丑陋的内联sql当然可以是"驯服的"；通过保存这样的查询，而不是将它们写在代码中。如果你使用内联sql，我不能说.net会更好(因为你必须处理连接对象、命令对象，在大多数情况下还需要处理数据适配器？好吧，那么.net代码会变得更冗长，需要比VBA中的一行代码更多的代码来做同样的事情。

好的，这样就解决了你的CTE问题，以及在VBA代码中内联和使用SQL的问题。

至于在Access中组织FE？好吧，为了最好的表现做最少的工作？毫无疑问，观点是可行的。原因是Access玩得很好。

如果您在Access中有表单。说直接绑定到链接表。在使用sql server之前，该表单将绑定到accDB后端。

假设有100万行。假设访问后端是文件夹中的共享accDB。假设表单基于此链接表。

那么，在过去，你会如何启动+加载并显示一条记录，并且只从网络管道中提取一条记录呢？好吧，除非你给它一个where子句，否则你不会启动表单。

你这样做：

dim strInvoice   as string
strInvoice = InputBox("Enter invoice number")
docmd.OpenForm "frmInvoices",,,"InvoiceNum = " & strInvoice

现在Access只会从网络管道中提取一条记录。不是整张桌子。

现在，假设我们将BE表迁移到SQL server？现在，假设我们仍然将发票表单链接到由100万行组成的链接表。当然，数据现在在sql server中。如何在网络管道中只绘制一行？你可以使用这个：

dim strInvoice   as string
strInvoice = InputBox("Enter invoice number")
docmd.OpenForm "frmInvoices",,,"InvoiceNum = " & strInvoice

(相同代码)。作为客户端的访问仍然只能拉动一行。尽管表单直接基于链接表，但情况依然如此。因此，作为一般规则，访问不会拉取整个表。它没有访问后端(BE)，如果您有一个链接表和一个直接绑定到100万行链接表的表单，它就没有。

这里没有太多关于使用Access作为FE到sql服务器的考虑；"未来"；比如说使用.net或者现在一些基于web的开发方法。

唯一真正的提示和问题是，您实际上不需要或可以采用从Access到sql的参数。但是使用Access where子句，那么你几乎不需要这样做(所以，是的，在Access中使用开放表单/报告的"where"子句来获得更高的性能，并限制拉到访问客户端的网络数据。所以，虽然大多数开发平台都是围绕着基于参数的sql生存和死亡的？Access根本不是这些情况之一。你生存和死亡(使用大量)Access中where子句的概念，因为它与SQL server配合得很好，并且实现了只从网络管道中提取数据的目标，您可以通过使用open form/report的where子句来告诉Access抓取/获取这些数据。

在哪里你可以得到"；大多数"；重复使用？是的，对于复杂的Access(客户端)查询？是，将它们移动到视图中。对于某些变得相当复杂的查询，可以在Access中使用存储过程。(这意味着通过查询)。

那么，在访问方面，比如说我们有一个复杂的发票检索，涉及复杂的联接，大量的查询？然后你可以创建一个存储过程，然后从访问中使用它来获取数据：

with CurrentDB.queryDefs("ptGetIvnoice")
.SQL = "Exec GetInoice " & strInvoice
rst = .OpenRecordSet
end with

再次，4行代码。所以，一个非常好的提示是，你是用VBA、.net还是其他什么编写代码？没有内联连接字符串代码。正如您在上面看到的，在VBA中，我们在代码中没有任何连接字符串，即使在上面使用T-SQL存储过程时也不需要它。

所以，是的，使用视图意味着你的asp.net或你最终采用的任何系统都允许你使用与你保存的SQL Server端相同的视图，从而访问，或者web或任何其他软件都可以使用相同的视图。

另一个问题是，访问将很好地处理视图，并且您很少需要在T-SQL中采用存储过程来进行基于参数的查询。原因当然是Access具有"；其中"；正如我上面提到的。没有T-SQL参数，在access中向链接视图添加条件(where子句)将与存储过程一样执行，但没有access最初不支持的错误参数。

因此，在Access的开发中，视图几乎是顶级的，因为Access对它们的处理非常好，这也意味着，当你采用asp.net或其他任何方式时，如果你的目标最终是重新放置Access，这些视图可以重复使用。然而，当您的后端是sql server时，Access确实是一个很好的RAD工具。因此，当你这样做的时候，你可以实现良好的可扩展性，更好的安全性，当然还有让asp.net甚至其他桌面开发平台使用这些数据。通常，在将Access数据移动到sql server后，公司会发现几乎没有必要放弃访问，因为所有这些表单、报告和代码都可以继续使用，而可以说采用了基于web的技术。