可我的Access 2010 VBA code提高到运行在共享驱动器上访问文件时，整个快？ [英] Can my Access 2010 VBA code be improved to run faster across when accessing the file on a shared drive?

问题描述

我有存储在网络驱动器上的Access 2010数据库。它执行以下操作：

I have an Access 2010 database stored on a network drive. It does the following:

1. 在拉动25万份清单（这是好的）
2. 分析的部件号在VBA
3. 分析的结果保存到一个二维数组
4. 写列表中退了出来，以一个数据库表中的其他数据库的使用

如果我运行这个过程保存到我的桌面数据库，这大约需要50秒才能完成。

If I run this process with the database saved to my desktop, it takes about 50 seconds to complete.

如果我用保存在网络驱动器上的数据库上运行它，它需要大约300倍的时间。似乎时间最长的元素被写入到从阵列结果表

If I run it with the database saved on the network drive, it takes about 300 times as long. the element that seems to taking the longest is writing to results from array to table

Sub WritePMROutput()
Status "Writing output to tblPmrParts"
Set db = CurrentDb
Dim rstOutput As DAO.Recordset
Set rstOutput = db.OpenRecordset("tblPMRParts")

db.Execute "DELETE tblPMRParts.* FROM tblPMRParts;" 'clear the table of all data before wriing new data

intPMR = 0
Do Until intPMR = intArrPMRSze 'loop until the counter reaches the size of the array
With rstOutput
    .AddNew 'new line in output table
    !PMRID = arrPMR(intPMR, 0)
    !strayChars = arrPMR(intPMR, 1)
    !partno = arrPMR(intPMR, 2)
    !extension = arrPMR(intPMR, 3)
    If Not arrPMR(intPMR, 4) = "" Then !PartPatternID = arrPMR(intPMR, 4) 'the if not function seems to be required here as was having issues with data type with "" in a number field
    If Not arrPMR(intPMR, 5) = "" Then !ExtPatternID = arrPMR(intPMR, 5)
    .Update
    intPMR = intPMR + 1
End With
Loop

LogUpdate "PDM", "tblPMRParts"

End Sub

任何人都可以建议我怎么能提高该操作的效率？还是我只是受限于我的网络速度？

Can anyone suggest how I can improve on the efficiency of this operation? Or am I simply limited by the speed of my network?

有趣的是，我可以将数据库复制到我的桌面，运行它，压缩它，把它复制到网络中约5-10分钟。

Interestingly, I can copy the database to my desktop, run it, compact it, and copy it back to the network in about 5-10 mins.

在此先感谢！

编辑：

继非常成功实施金正日提出的修复，我想我会的结果汇报。 这种情况是： 在本地电脑，花了大约60秒的运行;通过网络，花了约15000秒，运行

Following the very successful implementation of the fix suggested by Kim, I thought I would report back with the results. The situation was: On local PC, took about 60 seconds to run; across network, took about 15,000 seconds to run

实现一个简单的事务之后（开始于子开始，提交底）： 在本地PC上35秒（近两倍的速度）;通过网络500秒 - （！快30倍）

After implementing a simple transaction (begin at the start of the sub, commit at the end): On local PC, 35 seconds (nearly twice as fast); across network 500 seconds - (30 times faster!)

我与执行唯一的问题是，需要记录锁数抛出一个错误，而是一个快速谷歌搜索产生了code简单的线条临时增加的记录锁定的数量 dAO.DBEngine.SetOption dbMaxLocksPerFile 300000 希望这有助于在未来别人！

The only issue I had with implementation was that the number of record locks required threw an error, but a quick Google search yielded a simple line of code to temporarily increase the number of record locksdAO.DBEngine.SetOption dbMaxLocksPerFile, 300000 Hope this helps someone else in future!

推荐答案

我把这个来源，因为它说明pretty的好：
http://support.microsoft.com/kb/146908

I refer to this source as it is explained pretty well:
http://support.microsoft.com/kb/146908

实例语法在此提供过：

Private Sub Form_Load ()
     Dim Starttime, Endtime
     Dim db As Database
     Dim t As RecordSet
     Dim i As Integer
     Dim tempName As String
     Dim temphone As String
     Set db = Workspace(0).OpenDatabase("c:\vb\BIBLIO.MDB") ' Uses a
      ' copy of BIBLIO.MDB.
     Set t = db.OpenRecordSet("Publishers", dbOpenTable)
     Starttime = Now
     'BeginTrans  ' Add this and CommitTrans (below) for greater speed.
     For i = 1 To 100
        tempName = "testname" & Str$(i) ' Make an arbitrary unique
                                        '  string.
        tempPhone = Str$(i)             ' Make arbitrary number.
        t.AddNew ' AddNew clears copy buffer to prepare for new record.
        t!PubID = 30 + i  ' Set primary key to unique value.
        t!Name = tempName  ' Set Name field to unique value.
        t!Telephone = tempPhone  ' Set Telephone field to unique value.
        t.Update   ' Write the record to disk or to transaction buffer.
     Next i
     'CommitTrans  ' Add this and BeginTrans (above) for greater speed.
     Endtime = Now
     MsgBox "Time required= " & Format(Endtime - Starttime, "hh:mm:ss")
     t.Close
     db.Close
     End
  End Sub

您可以使用数据库事务，减少读取写入您的表数。
您可以在内存中执行的循环和逻辑，但只有提交您的记录到数据库一次。

You can use database transactions to reduce the number of read-writes to your table.
You perform your loops and logic in memory, but only commit your recordset to the database once.

从网站引用：

如果您不使用BeginTrans和CommitTrans语句，此   程序报告17秒为486/66 PC上添加100条记录。当你   如图程序注释上述添加的BeginTrans和CommitTrans，   该方案需要小于1秒是同一台计算机上。   性能可能会在不同的计算机而有所不同。

If you do not use the BeginTrans and CommitTrans statements, this program reports 17 seconds to add 100 records on a 486/66 PC. When you add BeginTrans and CommitTrans as shown in the program comments above, the program takes less than 1 second on that same computer. Performance may vary on different computers.

这篇关于可我的Access 2010 VBA code提高到运行在共享驱动器上访问文件时，整个快？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！