เรียน Guru ทุกท่านโปรดชี้แนะ ทางสว่างด้วยครับ

ผมฝากไฟล์ที่จะแก้ไขไว้ที่นี่ครับ
http://uploadmb.com/dw.php?id=1237539905&/09-03-20_Union.zip


ผมโพสคำถามไว้ที่

http://www.utteraccess.com/forums/showflat.php?Cat=&Number=1789961&page=&view=&sb=5&o=&fpart=all&vc=1

และก็มี Guru มาช่วยตอบ จนกระทั่งสุดท้ายผมได้ Code Save Query มา แต่ว่าทำไม่เป็นครับ ยังงงอยู่เลย


Saved queries are the queries that you create in Access Query Builder and save to Access database file. Saved queries can be select, append, update, delete or even make table query.

So, instead of doing this:

Code:
--------------------------------------------------------------------------------

Set rs = CurrentDb.OpenRecordset("SELECT * FROM foo;", dbOpenDynaset)
CurrentDb.Execute "INSERT INTO foo (bar, baz) SELECT bar, baz FROM foo2;", dbFailOnError
CurrentDb.Execute "DELETE FROM foo;", dbFailOnError
--------------------------------------------------------------------------------


We'd save those three SQL statement into a query and reference those query by name:

Code:
--------------------------------------------------------------------------------

Set rs = CurrentDb.OpenRecordset ("qtrFoo", dbOpenDynaset)
CurrentDb.Execute "qapFoo", dbFailOnError
CurrentDb.Execute "qdlFoo", dbFailOnError
--------------------------------------------------------------------------------


The difference is that because we're using saved queries instead of dynamic SQL statement, the plan Jet uses for all those queries is already compiled and optimized at the time you save those queries, and doesn't have to be repeated whereas dynamic SQL forces Jet to build a plan for each SQL statement and toss it out.

To wrap it in a transaction, it would be something like this:

Code:
--------------------------------------------------------------------------------

On Error GoTo Fail
DBEngine.BeginTrans
Set rs = CurrentDb.OpenRecordset("qtrFoo", dbOpenDynaset)
CurrentDb.Execute "qapFoo", dbFailOnError
CurrentDb.Execute "qdlFoo", dbFailOnError
DBEngine.CommitTrans
Exit Sub
Fail:
DBEngine.Rollback
--------------------------------------------------------------------------------


By doing it in a transaction, Jet will treat three executions as one transaction, rather than three separate transaction, and this can save on caching and I/Os which are traditional bottleneck in processing data. Transaction are primarily used to guarantee that the operration succeed or fail as one unit, but in this context, we can use it to help reduce on caching and I/O.

What I meant is to *not* use Make Table query at all. Just have a table at all time:

(I'll break my earlier suggestion and use SQL so you can see what each does, but they should be saved queries)
Code:
--------------------------------------------------------------------------------

'Clear out any data from the temporary table
CurrentDb.Execute "DELETE FROM tmpTable;", dbFailOnError
'Start Appending data into the temporary table
CurrentDb.Execute "INSERT INTO tmpTable SELECT * FROM foo;", dbFailOnError
CurrentDb.Execute "INSERT INTO tmpTable SELECT * FROM bar;", dbFailOnError
CurrentDb.Execute "INSERT INTO tmpTable SELECT * FROM baz;", dbFailOnError
'Now get recordset to do some reporting or whatever...
Set rs = CurrentDb.OpenRecordset("tmpTable", dbOpenSnapshot)
--------------------------------------------------------------------------------


Speaking of which, this gives me another way to optimize; instead of having "CurrentDb" in every place, use a variable as a placeholder:

Code:
--------------------------------------------------------------------------------

Dim db As DAO.Database
Set db = CurrentDb
With db
.Execute ...
.Execute ...
.Execute ...
Set rs = .OpenRecordset...
End With
--------------------------------------------------------------------------------