Workspace code

[CData]

greetings Hans - hope you are well and spring has arrived in your part of the world,

something I saw at a web site - was wondering on your advice... am insetting with [ ...to show the existing code
so that the proposed code stands out... would the 4 new code lines (not indented) really improve the speed as the web site says?

Dim wksp As DAO.Workspace
[Dim rst as DAO.Recordset
[Dim db As DAO.Database
[Set db = CurrentDb
Set wksp = DBEngine.Workspaces(0) ‘ set up a transaction buffer
wksp.BeginTrans ‘ all record set changes are buffered after this
[Set rst = db.OpenRecordset(“select field1 from XYZtable”)
[rst.MoveFirst
[Do While Not rst.EOF
[...more code
[rst.MoveNext
[Loop
wksp.CommitTrans ‘ here we write the changes to disk all at once
[rst.Close
[Set rst = Nothing

[HansV]

Hi CData,

I'm fine. Summer arrived today (it's about 24°C / 75°F) here.

Whether you'll notice the difference depends on the number of write operations (rst.Update) and on the storage medium.
On my PC, which has an SSD, writing thousands of records without a transaction takes less than a second, so wrapping the loop in a transaction (which has an overhead of itself, of course) isn't necessary.
But if the number of write operations is very large, and if you're writing to a network or remote disk, it might well be advantageous to use a transaction.

It's best to test it in a real-world situation, timing the code:

Code: Select all

    Dim t As Double
    ...
    t = Timer
    ...
    your code
    ...
    t = Timer - t
    Debug.Print Format(t, "0.00")

[CData]

ah that does make sense..... much thanks.