troubleshooting Question

Scope of Recordset Definition

Avatar of mlcktmguy
mlcktmguyFlag for United States of America asked on
Microsoft Access
9 Comments4 Solutions106 ViewsLast Modified:
I have several subroutines in my Access 2013 application that write to the same output table.  Currently each subroutine opens the output recordset, writes records to it, then closes it.

There are six subroutines and the are executed hundreds of time from a processing loop.

This processing takes a very long time and I am looking for ways to improve the time.

The processing loop and subroutines are all contained in the same module.


Module

Option Compare Database

Option Explicit

DIm wkVar as string
.
. other module wide definitions
.
private sub outsideProcessingLoop()
'
' Can I open the output file here one time such that the called subroutines can write directly to it, wthout having to open and close 'it every time?
'

Do while .....

       processSomeRecords()
       processOtherRecords()

enddo 


end sub

' Typical handling of output file in each of the processing subs

private sub processSomeRecords()
'
Set db = getCurrentDbC
Dim rsOut As DAO.Recordset
Set rsOut = db.OpenRecordset(outputTable, dbOpenDynaset, dbSeeChanges)
.
. processing logic
.
'
rsOut.Close
Set rsOut = Nothing

end sub

private sub processOtherRecords()
'
Set db = getCurrentDbC
Dim rsOut As DAO.Recordset
Set rsOut = db.OpenRecordset(outputTable, dbOpenDynaset, dbSeeChanges)
.
. processing logic
.
'
rsOut.Close
Set rsOut = Nothing

end sub

Is there a way I can define the output file in the header of the module, above all subs and/or functions, so that I will only open it once and all the subroutines can write to the open output file without having to open and close it?

IF so how would this look?
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 4 Answers and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 4 Answers and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros