sql - 在 SQL 中使用自定义函数 Access VBA

Question

我需要使用基于表单控制值在我的 vba 中构建的一些 sql 打开查询或记录集或其他内容(数据 TableView )。我的数据中有“多对多”关系(有点)。假设我们有一个 Person 表、一个 Pet 表和一个 ID Pet_Person 表。我想在一行返回的数据中列出我的人，其中包含他们所有宠物的串联字符串。所以....

Row    Person       Pets
1      Kim          Lucius, Frodo, Cricket, Nemo
2      Bob          Taco

我搜索了一下，发现您可以在 VBA 中编写函数以从 SQL 中调用。所以。这就是问题所在。我有很多记录，一旦打开，我无法在数据 TableView /查询/任何内容中移动，而每次单击一行时都会调用该连接函数。我只需要在最初运行 sql 时调用它一次……比如快照或其他东西。

我不太熟悉 Access 和可能性。我已经尝试了一些我在网上找到的东西，它们都具有相同的结果......当我接触到结果数据集时，就会调用连接函数。

我尝试的最后一件事看起来像:

With db
        Set qdf = .CreateQueryDef("Search Results", q)
        DoCmd.OpenQuery "Search Results", , acReadOnly
        .QueryDefs.Delete "Search Results"
End With

StackOverflow 真的从来没有正确格式化我的东西。可能是用户错误....哦，好吧。

编辑:

哦，巴特 S. 谢谢你，但你走得太早了，我无法理解答案是否存在。谢谢你。

哦，雷姆。 是的，我看到了你的帖子。我用了你的帖子。我在做这个项目时使用了你的许多帖子。为什么 Access 不支持我习惯于使用 MySQL 的所有 SQL 函数，我不知道。你是这个网站的一个很好的补充。我应该把它和我的问题联系起来，但咖啡还没有开始。

我有我的连接函数，我在 sql 中调用它。我正在使用 docmd 打开它以打开该记录集或查询或其他任何内容。但这是我的问题(我可能会通过一次尝试太多解决方案来自己创建这个问题，或者我可能会忽略某些东西)... 每次我触摸生成的数据集/查询/事物 时，它都会一直调用该函数并且有太多的数据无法发生；我看沙漏太多了。我很肯定这是因为我打开它的方式。这旨在成为搜索表单屏幕事物的结果。我想我需要在 Access 中创建另一个表单并用我生成的记录集填充它。我想这就是你们告诉我的。我不确定。这是一个奇怪的例子。但是......你知道使用Excel，当你编写某种内联函数来为每一行获取一些值时......然后你对值进行特殊的复制和粘贴(而不是函数)......我需要那个。因为这个函数(显然不是在 Excel 中)必须查询并且每次单击一行时重新应用都需要很长时间(我认为如果单击一行，它实际上是在重新查询每一行，几乎就像它在重新运行 sql 或其他东西一样)。就像 NIN/Depeche Mode 的歌曲 Dead Souls... 它一直在呼唤我/它。

Answer 1

以下是一些应对不断重新加载数据问题的想法和策略:

确保您的查询设置为 snapshot .表格也一样。
这当然会使数据成为只读的，但它可能会有所帮助。

将查询结果缓存到本地表中，然后显示/绑定(bind)该表而不是查询本身。
这将使用户在执行查询并保存到本地表时最初等待更长的时间，但由于所有数据都是本地的，不需要重新计算，因此之后界面会更加流畅。

创建本地表localPetResult (在客户端)具有与查询匹配的所有字段。

不要将查询本身绑定(bind)到数据表表单，而是绑定(bind) localPetResult到它，然后在表单的 VBA 模块中处理 OnLoad 事件:

Private Sub Form_Load()
    ' Remove all previous data from the local cache table '
    CurrentDb().Execute "DELETE FROM localPetResult"

    ' Define the original query '
    Dim q as String
    q = q & "SELECT Row, "
    q = q & "       Person, "
    q = q & "       Coalesce(""SELECT PetName FROM Pets WHERE Person='"" & [Person] & ""',"","") AS PetNames "
    q = q & "FROM MyData"

    ' Wrap the query to insert its results into the local table '
    q = "INSERT INTO localPetResult " & q

    ' Execute the query to cache the data '
    CurrentDb().Execute q
End Sub

一个你有它的工作，你可以通过多种方式改进它以使其更好(卡住屏幕并显示沙漏，在计算数据后将 ersult 表动态绑定(bind)到表单等)

缓存每次调用合并函数的结果。
我用它来为每条记录计算一次连接，然后将结果存储在字典中，字典的键是记录的 ID。相同 ID 的后续计算只是从字典中提取，而不是重新计算。

例如，将以下内容添加到 VBA 模块。我假设您使用 Remou's Coalesce function也是。

    Option Compare Database
    Option Explicit

    ' A Scripting.Dictionary object we'll use for caching '
    Private dicCache As Object

    ' Call to initialise/reset the cache before/after using it '
    Public Sub ResetCoalesceCache()
        If Not (dicCache Is Nothing) Then
            dicCache.RemoveAll
        End If
    End Sub

    ' Does the Same as Coalesce() from Remou, but attempts to '
    ' cache the result for faster retrieval later '
    Public Function Coalesce2(key As Variant, _
                              sql As String, _
                              sep As String, _
                              ParamArray NameList() As Variant) As Variant

        ' Create the cache if it hasn't been initialised before '
        If dicCache Is Nothing Then
            Set dicCache = CreateObject("Scripting.Dictionary")
        End If

        If IsNull(key) Then
             ' The key is invalid, just run the normal coalesce '                                       
             Coalesce2 = Coalesce(sql, sep, NameList)
        ElseIf dicCache.Exists(key) Then
            ' Hurray, the key exists in the cache! '
            Coalesce2 = dicCache(key)
        Else
            ' We need to calculate and then cache the data '
            Coalesce2 = Coalesce(sql, sep, NameList)
            dicCache.Add(key, Coalesce2)
        End If

    End Function

然后，在查询中使用它:

    ' first clear the cache to make sure it doesn't contain old '
    ' data that could be returned by mistake '
    ResetCoalesceCache

    ' Define the original query '
    Dim q as String
    q = q & "SELECT Row, "
    q = q & "       Person, "
    q = q & "       Coalesce2([Row], ""SELECT PetName FROM Pets WHERE Person='"" & [Person] & ""',"","") AS PetNames "
    q = q & "FROM MyData"

    ' Bind to your form or whatever '
    ...