从大卫·芬顿(David W Fenton)的answer到SU问题Does MS Access 2003 contain a general purpose SQL console
我的问题是:使用CurrentDb执行SQL或打开记录集时会产生开销吗？应该避免吗？
最佳答案
目前尚不清楚您所说的“开销”是什么意思，因此我不知道有人会怎样回答您的措辞。
但是多年来，在Access新闻组中已经对DBEngine(0)(0)与CurrentDB进行了广泛讨论。我很早以前就使用CurrentDB感到安心，所以我将总结一下我所看到的情况。
在以下代码中，DBEngine(0)(0)比CurrentDB快得多:
Dim db As DAO.Database
Dim i As Integer
Debug.Print "Start CurrentDB: " & Now()
For i = 1 to 1000
Set db = CurrentDB
Set db = Nothing
Next i
Debug.Print "End CurrentDB: " & Now()
Debug.Print "Start DBEngine(0)(0): " & Now()
For i = 1 to 1000
Set db = DBEngine(0)(0)
Set db = Nothing
Next i
Debug.Print "End DBEngine(0)(0): " & Now()
如果我没记错的话，ADH97表示DBEngine(0)(0)的速度快了17倍。
但是请看一下该代码-它不会测试任何有用的东西。请记住，CurrentDB和DBEngine(0)(0)都返回指向当前在Access UI中打开的数据库的指针(以下是针对DBEngine(0)(0)的警告)。这些循环中的任何一种都会有用。在实际代码中，您可以执行以下操作:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDB
Set rs = db.OpenRecordset("a SQL SELECT")
[do something with the recordset]
rs.Close
Set rs = db.OpenRecordset("another SQL SELECT")
[do something with this other recordset]
rs.Close
Set rs = Nothing
db.Execute("A SQL DML statement")
Debug.Print db.RecordsAffected
Set db = Nothing
尽管DBEngine(0)(0)可能在循环中快1700％，但它并不重要，因为您永远不会重复将对Access UI中当前打开的数据库的引用重复返回足够多次，以确保差异不大。几乎可以忽略不计的任何事情(不过，这里所说的毫秒数，当然，对于具有更多对象的数据库，CurrentDB将花费更长的时间)。
因此，首先，在我解释为什么存在差异之前，您必须首先认识到性能差异是完全不重要的，因为只有在这种情况下才能超过最大差异的情况就是脑筋急转弯的愚蠢代码。
现在，为什么会有所不同？
好吧，主要有两个原因:
DBEngine(0)(0).QueryDefs.RefreshBefore that, your new query won't be in the QueryDefs collection, but after it, it will. CurrentDB, on the other hand, refreshes all collections each time it is called, so you never have to worry about refreshing any of your collections.
DBEngine(0)(0) returns the internal pointer the Access Jet workspace uses to point to the database currently open in the Access UI. CurrentDB returns a copy of the database structure, and each call to CurrentDB creates a new copy. Thus, CurrentDB will use more memory, because it creates a copy of the structure that points to the database currently open in the Access UI, while DBEngine(0)(0) uses no additional memory, because it returns not a copy, but simply a pointer to an existing memory structure.
Likely the refreshing of the collections is the reason why CurrentDB is "1700%" slower (or whatever the number was), but probably some of the extra time is taken up by the process of setting up the copy of the database object, as well.
Again, none of this makes any difference in actual coding practice, as you just don't need to constantly be opening and closing pointers to the database currently open in the Access UI, as IT'S NOT BEING OPENED AND CLOSED CONSTANTLY.
So, is this a potaeto/potahto thing?
No, because there's one "bug" in DBEngine(0)(0) that could cause it to return an unexpected database pointer (though it would actually be technically correct), and that is in certain contexts immediately after an Access wizard has run, DBEngine(0)(0) will return a pointer to the wizard database, and not to the database currently open in the Access UI.
That is because there is a distinction between:
the database currently open in the Access UI, AND
the first database in the first workspace of the DBEngine object.
CurrentDB, on the other hand, always returns a reference to #1 and never to #2. DBEngine(0)(0), however, can return something else, as for a very brief moment, the wizard really is the first database in the first workspace of the DBEngine object right after the wizard is dismissed.
Now, is it likely that production code could ever encounter this error? Probably not, since it's unlikely that you'd use a wizard in a production app. But this could also apply to library databases, and that's not so uncommon a technique, particularly for advanced Access programmers.
If there were a practical performance difference, DBEngine(0)(0) might be worth it, but since there isn't, CurrentDB is preferable since it is 100% reliable in returning the expected database reference.
All that said, I don't use either in my apps.
Instead, I use a function that caches a database variable initialized with CurrentDB. This means I never have to initialize any database variables, just use my dbLocal() function in place of any database variable. Here's the code:
Public Function dbLocal(Optional bolCleanup As Boolean = False) As DAO.Database
' This function started life based on a suggestion from
' Michael Kaplan in comp.databases.ms-access back in the early 2000s
' 2003/02/08 DWF added comments to explain it to myself!
' 2005/03/18 DWF changed to use Static variable instead
' uses GoTos instead of If/Then because:
' error of dbCurrent not being Nothing but dbCurrent being closed (3420)
' would then be jumping back into the middle of an If/Then statement
On Error GoTo errHandler
Static dbCurrent As DAO.Database
Dim strTest As String
If bolCleanup Then GoTo closeDB
retryDB:
If dbCurrent Is Nothing Then
Set dbCurrent = CurrentDb()
End If
' now that we know the db variable is not Nothing, test if it's Open
strTest = dbCurrent.Name
exitRoutine:
Set dbLocal = dbCurrent
Exit Function
closeDB:
If Not (dbCurrent Is Nothing) Then
'dbCurrent.close ' this never has any effect
Set dbCurrent = Nothing
End If
GoTo exitRoutine
errHandler:
Select Case Err.Number
Case 3420 ' Object invalid or no longer set.
Set dbCurrent = Nothing
If Not bolCleanup Then
Resume retryDB
Else
Resume closeDB
End If
Case Else
MsgBox Err.Number & ": " & Err.Description, vbExclamation, "Error in dbLocal()"
Resume exitRoutine
End Select
End Function
在代码中，您可以这样使用:
Dim rs As DAO.Recordset
Set rs = dbLocal.OpenRecordset("SQL SELECT statement")
[do whatver]
rs.Close
Set rs = Nothing
dbLocal.Execute("SQL INSERT statement")
Debug.Print dbLocal.OpenRecordset("SELECT @@IDENTITY")(0)
dbLocal.Execute("SQL UPDATE statement")
Debug.Print dbLocal.RecordsAffected
第一次调用它时，它将使用CurrentDB初始化自身并返回缓存的数据库对象。
当您关闭应用程序时，可以将bolCleanup标志设置为TRUE来调用该应用程序，以清理缓存的变量。
如果您添加到集合中，它们将不会刷新(因为您不是每次都调用CurrentDB，仅使用已使用CurrentDB初始化的缓存数据库变量)，因此您必须执行以下操作:
[add a new QueryDef]
dbLocal.QueryDefs.Refresh
就是这样。没有全局变量，无需使用CurrentDB(或DBEngine(0)(0))不断初始化数据库变量。您只需要使用它，而不必担心它。唯一的技术细节是确保应用程序的关闭例程调用dbLocal(False)。
因此，这就是我对DBEngine(0)(0)与CurrentDB的看法。
关于清理通过以下两种方法初始化的数据库变量的附带问题:
如果使用CurrentDB初始化db变量，则不关闭它，只需将其设置为Nothing即可:
Dim db As DAO.Database
Set db = CurrentDB
...
'db.Close <= don't do this
Set db = Nothing
如果您确实发出db.Close，则不会发生任何事情，无论好坏。
另一方面，在这种情况下:
Dim db As DAO.Database
Set db = DBEngine(0)(0)
...
'db.Close <= don't do this
Set db = Nothing
...发出db.Close可能会导致您的应用在某些版本的Access中崩溃。
它们实际上都不起作用，因为您无法通过数据库对象的Close方法关闭当前在Access UI中打开的数据库。
另一方面，如果您这样做:
Dim db As DAO.Database
Set db = DBEngine.OpenDatabase("path to external MDB file")
...
db.Close ' <=you *must* do this
Set db = Nothing
...您确实想将其关闭，因为它是一个外部数据库。该代码无法用CurrentDB完成，因为这是打开对另一个数据库的引用的唯一方法。
关于ms-access - MS Access : Is there a significant overhead when using CurrentDB as opposed to DBEngine(0)(0)?，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/1833746/