Avatar of mlcktmguy
mlcktmguy
Flag for United States of America asked on

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?
Microsoft Access

Avatar of undefined
Last Comment
mlcktmguy

8/22/2022 - Mon
Dale Fye

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?
SOLUTION
PatHartman

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Dale Fye

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Jim Dettman (EE MVE)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
John Tsioumpris

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
mlcktmguy

ASKER
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?
PatHartman

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Jim Dettman (EE MVE)

<<
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.
mlcktmguy

ASKER
Thanks to all, excellent information in these posts.