Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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
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?
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?
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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?
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
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.
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.
<<'
' 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(outputTab
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.
' 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(outputTab
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.
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...
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...
Thanks for all of the responses. Good information in every one of them. I do have a follow up for Jim's comment
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:
Definition of gDDAODB
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?
<<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
Definition of gDDAODB
Public gDAODB As DAO.Database ' used to only DAO connect 1 time
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?
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.
<<
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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial