when an object is created, for good memory management it should be closed when finished with.
but how do you close a function which returns an object? Or doesn't it matter
for example a function could be structured like this
Code:
function getRst(rst as dao.recordset, sqlstr as string)
dim db as dao.database
set db=currentdb
set rst=db.openrecordset(sqlstr)
set db=nothing
end function
'and called
dim rst as dao.recordset
getRst rst, sqlstr
'do something with rst
rst.close
set rst=nothing
or like this
Code:
function getRst(sqlstr as string) as dao.recordset
dim db as dao.database
set db=currentdb
set getRrst=db.openrecordset(sqlstr)
set db=nothing
end function
'and called
dim rst as dao.recordset
set rst=getRst(sqlstr)
'do something with rst
rst.close
set rst=nothing
My question is - does the second method leave memory allocated, ultimately leading to a stack overflow? Any views on which is the better way?
The way I understand how memory is managed is with reference counts, so as long as there is one variable that has not gone out of scope, and that still points to the object, then the object is not destroyed and the memory allocated to that object is not reclaimed by garbage collection.
So in order to create a memory leak, create an object like this, called cLeaky...
Code:
private m_self as cLeaky
private sub Class_Initialize()
[COLOR="Green"] 'this class maintains a reference to itself, and so once instantiated
'this instance cannot be destroyed, because there will always be one
'variable, m_self, pointing to this instance
[/COLOR] set m_self = me
end sub
...and then do something like this, but open 'Task Manager' first, and watch the memory get eaten up...
Code:
private sub Leaky
dim leak as cLeaky
do
[COLOR="Green"] 'instances created in this loop go out of scope
'but are never destroyed, because the cLeaky type contains a reference
'to itself, so this loop creates a memory leak.
[/COLOR] set leak = new cLeaky
[COLOR="Green"] 'this line does not destroy the instance, as you would expect...[/COLOR]
set leak = nothing
loop
end sub
I don't believe that either of the code examples you posted will create a memory leak.
Finally, if I was writing the code, I would write the function that returns the recordset, like this...
Code:
function getRst(sql as string) as dao.recordset
set getRst = currentdb.openrecordset(sql)
end function
First, there are TWO memory areas at work here. (As I understand it...)
When you have a subroutine or function with local variables, what happens is that there is a thing called a "Call Frame" that contains the context of the caller AND all of the declared variables (from Dim statements). So if you have an object variable, it is in the call frame. The hardware uses the Stack Pointer as a base register and the local variables are just offsets from the SP.
However, object variables are tricky. The object actually instantiated through the local variable exists in the HEAP, not on the stack. The heap is a disorganized blob of memory that starts at the lowest unallocated memory page above the code and local program data. Memory on the heap is allocated in increasing address order. The stack starts at the highest allowed memory page and is allocated in decreasing address order.
If you create an object via syntax such as SET objX = NEW GobbleDeGook then memory for the object is allocated from the heap based on the named object and the address of the created object is stored on the stack. A serious "disaster" occurs if the heap and stack ever touch each other. Usually, though, enough stack is allocated in user mode to have quite a few levels of subroutines and still be safe.
Based on the way OTHER Windows apps work (specifically, true code compilers), the stack and heap for Access are probably in the same 2 Gb address space as MSACCESS.EXE itself, and the .MDB/.ACCDB elements are in the other 2 Gb address space. I suspect that all .DLL files are in the same address space as the image file. This is an INFERENCE based on other programs for which I know more details of layout.
As to using an assignment of something for CurrentDB, you don't need that because for a recordset it doesn't need to be persistent. For example,
Code:
Set dbX = CurrentDB
...
dbX.Execute sSomeSQLString, dbFailOnError
If dbX.RecordsAffected = 0 Then ...
For this case, you need a persistent instantiation for CurrentDB because of needing to get the .RecordsAffected property after the query executes.
For the case of opening a recordset, it is the recordset object that needs to be persistent, not the database pointer. There, you are just using the pointer as a means to an end, the end being the recordset object. You don't care that the thing selected by CurrentDB is ephemeral.
Mark, I think you are correct about the "Object Reference Count" method being the way that Access knows it is safe to dissolve the object. In a previous thread we had a discussion about how, since Access is interpreted, the "End Sub" or "Exit Sub" statement can be a complex routine that includes something "behind the scenes" doing the necessary object reference bookkeeping.
Regarding memory leaks, the only thing that would potentially cause issues is if you created an app object based on Excel or Word or some other utility that uses the COM approach AND opened something in that app but failed to close it. It is possible for that external app to take on a life of its own after Access exits. However, for ALL ELSE, when Windows releases your memory, you lose it wholesale, not retail. That is, the memory in your stack, heap, image, and data areas are ALL released back to the memory pool of free pages.
Regarding memory leaks, I'm pretty sure the code I posted creates one. The class I wrote there will not be destroyed by VBA because it references itself, so the reference count to any instance will never hit zero. As a result, the loop I wrote will keep claiming space on the heap that will never be released until you shut down Access.
Mark
a useful discussion for me but not sure if I'm any further forward on which is the best way, or they effectively both have the same impact! I think we are saying neither of my suggested functions create memory leaks which was the point of my question.
Over the years, I've used both of the methods outlined in my original post, but not using a class as per Mark's example although I am considering it for the situation I am trying to resolve.
My question came about because I have a client who needs the db to present in two languages. Up to now, I have used the table>field>caption property to determine what appears in a controls associated label on a form or report. But there is only one caption property so need to find another way. Plus there is more than just changing associated label captions.
As a separate exercise I have also been looking at what is involved (for me) in migrating from Access to sql server. The latter does have a caption property of sorts, but called Description and stored in an extended properties table, so not so easy to use.
So to kill two birds with one stone and provide a consistent way forward, I thought I would develop my own extended properties table, to include a language flag to replace using the caption property - plus also apply to non field items such as single labels, message boxes, etc.
This would be loaded into a public collection in a class module at initial runtime based on the language code. Typically any development will have perhaps a total of 3-400 individual fields across perhaps 30-40 tables. With single labels etc, perhaps 500 items in all.
So if I have understood correctly,
The routine would be to create a recordset (which goes on the heap?), looped through to populate a caption collection and then closed, (freeing up the heap?). But now the caption collection is on the heap?
And the rst itself is basically just a pointer which is taken off the stack by setting it to nothing.
Caption properties, being ... well, object properties - are located in the data segment of the database where those objects exist. The recordset structure in the heap itself has pointers to its data source in the 2 Gb memory space for the .MDB/.ACCDB file.
Even if you did a "property define" you did it in a way to attach it to a database object, so it would be with the database, not with the stack or heap. (That's an impression of the way I think it works.)
hmmm. Sorry yes, question started off in one place and ended up in another.
the original question was about memory management - I was concerned I was creating a memory leak by not being able to close a recordset if I used a function which returned a recordset
function getRst(sqlstr as string) as recordset
my understanding of the conclusion from everyones comments was the function actually returns a pointer
so when the above is called
dim rst as recordset
set rst=getRst(sqlstring)
rst.close
set rst=nothing
the recordset created by the function is sitting in the heap somewhere and the function returns a pointer to it and assigned to rst with
set rst=
the recordset is subsequently cleared from the heap with
rst.close
and the rst pointer can be cleared with
set rst=nothing
That just leaves anything from calling getRst(sqlstring) - it returned a pointer to be assigned to rst - at what point is it removed from the heap? Or is it in the stack and there for the duration of the code running?
However now moved on to properties for which I'm trying to develop a solution for a multi lingual application and caused me to think about the above.
The problem is the caption property from my perspective is actually to do with controls displaying on forms and reports - objects which are not in the database.
Yes, many controls are based on table fields - and in Access if you set the field caption property it will pick up the caption to display in the associated label - a practice I have used for years. But a) I need multiple captions for different languages b) caption properties do not exist in sql server (there is description but difficult to use and same issue for multiple languages) and more importantly c) forms/reports have unbound labels plus things like buttons with captions and I need to translate messages.
When you force the object to close, it doesn't NECESSARILY go away because the stack pointer still references it. When the stack pointer goes away (via Exit Sub/Function) or reaching End Sub/Function, THAT causes the object of the pointer to have its reference count decremented, which then MIGHT cause it to go away if nothing else still pointed to it. So in the "normal" case, they cease to exist as a result of the same action even if not otherwise synchronized.
As to the captions & descriptions, to make them dynamic is trickier. Technically, by the way, if you are picking up something from the table, it is the Field Description, which becomes text in the bottom of the form.
I don't know an automatic way to do what you want to do. I can only say that I would have to visit the database elements having these descriptive strings and perhaps do edits on them. The question would then become: Does it ever happen that two folks using the same back end would have selected two different languages?
Have you considered teaching everyone Esperanto? (One size fits none!)
yes - some users are in the UK (English), some are in France (French). All sharing the same back end. At the moment the backend is Access but it is likely to be moved to their sql servers or to azure.
I have got a way to do it but it requires clear programming rules - a field name can only mean one thing - it can't mean 'name' on one form and 'fullname' on another. normalisation rules apply!
Only issue I've hit so far is comboboxes bound to a family key (e.g. invoice header - select a customer) - on an invoice I want a label 'Customer', on the entry form it is 'Please Select a Customer'. But easily solved by disconnecting the label from the control - and using the label name rather than the fieldname as the collection index. I'm also using more image based controls to indicate actions such as a printer icon rather than the word 'print'.
I will be using google translate to translate the captions to French so each field/disconnected label etc has two records, one in English, one in French. When the app is opened the appropriate language is loaded to a collection depending on user settings. Admin users will have the ability to edit the captions since google translate may not always get it right.
I also need to do some work around message boxes - at the moment the text is hardcode/built in VBA. I need to move them to a table and provide parameters on occasion. e,g. message might be built as
"This customer has been put on hold, please contact " & me.salesperson & " for more information"
which would be stored as
"This customer has been put on hold, please contact {param1} for more information"
which translates to
"Ce client a été mis en attente, veuillez contacter {param1} pour plus d'informations"
and when the message is called a replace function can replace {param1} with the me.salesperson value
CJ, I have every confidence in you. It is no worse than some of the security-related challenges I had with the Dept. of Defense. Different, but probably equal in potential for the tearing out of much hair.
This is a crazy idea, but have you looked at whether you might be able to use a Dictionary Object? You can define a dictionary as a persistent memory resident object and have two elements - the key and the item associated with that key. The item CAN be a string - and so can the key. So some factor of the control, perhaps the TAG if you aren't otherwise using it OR you could just use the control name, is the KEY but in the ON LOAD event of that form you reload whatever caption items are needed with a FOR EACH loop that queries the dictionary. Since the dictionary is in resident in memory and the key IS indexed, that is the fastest possible lookup. So all you would do is have your table of key values and the language-specific messages to be loaded behind-the-scenes in your opening form. Different language? Load different messages. The dictionary doesn't care as long as the key is unique.
The label captions have become data. Load them as data straight from the tables via textboxes formatted to emulate labels by adjusting Locked, Enabled and Border properties.
Have a look at this thread, especially Endre's sample in post 11. It is long time since I looked at it.
However I remember making the suggestion to another poster with a similar requirement. They came back saying they had problems with updateability when they included the "label" data in the form recordset and solved it by putting the labels in a subform. This would somewhat dictate layout but it would work well for Continuous Forms datasheet emulations where the "labels" are in the header. And of course Reports where updatability is not an issue.
I have since casually wondered if the updatability issue might be avoided with careful recordsource query construction.
I'm more familiar with collections (although I do struggle to picture the 'structure' of anything more than a list) but I did look at using a dictionary object and will probably move over to it when nearing completion. So I'm trialling with collections for now
To swap from collection to dictionary is fairly straightforward and I'm aware that dictionaries can be easier to manipulate. One of the things to watch out for is case sensitivity which is a basically a foreign country to Access in normal usage and I know I sometimes use customerPK and another time CustomerPK, so one more level of discipline required
@Galaxiom. Thank you for the link. The example in post#11 works in a very similar way to what I am doing. Main difference is I am working on a standard caption for a field name regardless of which form it is on rather than targeting a specific control and also loading the data to a collection to reduce load times and network traffic. Also the example enables modification to other properties. In theory mine could but I haven't explored that yet
I note the example uses labels, not textboxes but they could be applied - would give the benefit of being able to use conditional formatting.
In my apps I have identified a number of 'types' of captions which would require translating
labels bound to a bound control
labels bound to an unbound control (inc subform)
labels associated with a control but not bound (e.g. column headers in a continuous form)
unbound labels
buttons (same as an unbound label)
hyperlinks (text to display)
web controls (text to display)
option group button labels
Chart control labels
form/report caption
plus the format property of textbox controls (primarily the null format)
within vba code there is msgbox and inputbox (two parameters, the message text and the header text) plus some code here and there for dynamically setting captions. I'm trying to find a way of inspecting the VBA, identify these actions (I can use the VBE find to find 'msgbox', 'inputbox' and 'caption' but that only gives me the module and line number) and extract the text to populate the table and ideally substitute a call function to do the necessary. Not sure whether the time invested would be worthwhile, may be quicker to just manually slog through it.
I can see that in some cases I need the control name - but I'm trying to avoid needing to specify every control on every form - leads to duplicates which require the same value. Plus heavy on the maintenance when controls are added/deleted. So I need a change to my naming convention for unbound and unassociated controls.
Rather than the form open event looping through a recordset to populate the captions, I loop through the form controls instead and I've moved to populating a dictionary so I can use the Exists option to see if there is a change to the Caption available. There is perhaps more activity in the form open event than doing it the other way round and looping through a recordset per Galaxiom's link but does result in less network traffic (dictionary is populated when app opens), can still be used for other properties such as font size etc and requires less maintenance during development/updates. It provides consistency between forms and reports for any given 'value' and if you want to break that consistency, just give the control another name.
Just found another one - value lists for list and combo boxes
(One size fits none!)
