Scope of Recordset Definition

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

Open in new window


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?
LVL 1
mlcktmguyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
The first line of your question says "output table", but your comments in your code imply an "output file".  Are you writing to a table, or a text file?
PatHartmanCommented:
ALWAYS include Option Explicit in your module headers.  This is a global setting that you can make in Tools/ Options from the VBA window.

Option Compare Database
Option Explicit
Dim db as DAO.Database
Dim qd as DAO.QueryDef
Dim td as DAO.TableDef
Dim rs as DAO.Recordset

Open in new window

OptionExplicit.JPG
Dale FyeOwner, Developing Solutions LLCCommented:
If you are writing to a recordset, you could open that recordset in the main procedure, as a global variable, and write to it in the modules, or you could simply pass that recordset to the procedures as an argument.

But if you are running through a series of loops to insert one record at a time, that will always be a slower process than writing append queries which operate on multiple records at a time.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

LEARN MORE
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<'
' 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?
'
>>

 Yes.   Just refer to the same file handle.

<<Set rsOut = db.OpenRecordset(outputTable, dbOpenDynaset, dbSeeChanges)>>

  If your just adding to these recordsets, then do:

Set rsOut = db.OpenRecordset("SELECT * FROM <output table> WHERE 1=0", dbOpenDynaset, dbSeeChanges)


 and yes, I would move this into the main routine as well and let the subroutines write to it.   You can declare the rsOut in the procedure and pass the recordset object in to the subroutines, or you can declare it at module level so it's available to all procedures.

<<getCurrentDbC>>

  I assume you have a cached copy of CurrentDB() with this?  That's great, but I would just verify that your not calling CurrentDB() any more than you have to.

Jim.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John TsioumprisSoftware & Systems EngineerCommented:
Your issue boils down to the processing logic...i don't know what kind of data manipulation you are performing but probably you need to change your logic...you can try to hold temp data to an array -- collections---you name it and periodically do a bulk insert to the table...
Also transaction help a lot...but as i said just take a look at your operations and try to make a "block" of data to insert....also "small" things like using a string as index vs an integer could have tremendous effect...if i remember correctly once i had a very heavy application...a lot of data manipulation....it would take 15 seconds due to the fact the "key" was a string...changing to an int resulted in about 1 second...
mlcktmguyAuthor Commented:
Thanks for all of the responses.  Good information in every one of them.  I do have a follow up for Jim's comment

<<getCurrentDbC>>

  I assume you have a cached copy of CurrentDB() with this?  That's great, but I would just verify that your not calling CurrentDB() any more than you have to.

Here is what I am doing with this Jim.  Is it as it should be or does it need revised?

I have this defined at the top of every module using GetCurrentDBC
Dim db As DAO.Database

This is what it looks like:
Public Property Get getCurrentDbC() As DAO.Database
    If (gDAODB Is Nothing) Then
        'Set m_db = CurrentDb
        Set gDAODB = CurrentDb
    End If
    Set getCurrentDbC = gDAODB
End Property

Open in new window


Definition of gDDAODB
Public gDAODB As DAO.Database  ' used to only DAO connect 1 time

Open in new window


The intention was to set it up so no matter how many times getCurrentDBC is called the actual connection is only set once.  Is it correct?
PatHartmanCommented:
I don't see the need for Get and Set to do this.  I showed you how to define variables that are global to a module.  If you want them to have a wider scope, use a standard module rather than a form's class module and use Public rather than Dim.  You can then simply reference them from any other object.  Just make sure that the procedure that sets them runs once at the beginning and the procedure that cleans up runs once at the end.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<
Here is what I am doing with this Jim.  Is it as it should be or does it need revised?
>>

  yes, that's fine.  Although I would add the ability to refresh.   in certain cases, you need a fresh CurrentDB() reference even though you already have it (ie. adding a querydef).

Article here shows what I'm talking about:

https://www.experts-exchange.com/articles/2072/CurrentDB-vs-dbEngine-Workspaces-0-Databases-0-and-an-alternative.html

Jim.
mlcktmguyAuthor Commented:
Thanks to all, excellent information in these posts.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.