MSAccess 2010 global variables in user defined aggregate VBA functions
Okay so I have an issue with Access. Which is not uncommon but this one is bugging me a bit. So here is a simplified version of the problem. The real data is too messy for short stories. I think this problem requires some background to understand so I'm describing it as best I can.
I have a database loaded with objects that have identifying fields and a single string field, call it a description of the object. See the CSV table below.
category, type, color,desc
1,a,b,new
1,a,b,old
1,a,b,new
1,a,b,new
2,a,r,new
3,b,g,old
The data needs to be grouped by the first 3 fields (category, type, color). So I run a SQL query that groups the data, as seen below. Then use a custom VBA aggregate function to concatenate the string values of each item together.
SELECT
FIRST(category),
FIRST(type),
FIRST(color),
LAST(fconcat(desc,(categor
FROM table1
GROUP BY category, type, color;
LAST(fconcat(desc,(categor
So the first time I built this solution I simply used a global variable in fconcat to determine if the ids were the same as the last iteration.
If so the data from the last iteration is pulled from another global variable, the new string from the current iteration is concatenated with the old, stored in the same global variables and fconcat returns the latest complete string.
If ids differ from the last iteration the global variables are overwritten with the new and the new string is returned unaltered.
Because fconcat is wrapped by LAST the data from the most recent iteration for each grouping are used in the results. So I ended up with the complete strings.
That was fine for the first table. But a different table caused some fun results. For some reason on this table the iterations occur in a different order than the grouping. So all the strings from one set of items come at different times mixed in with the other groups. This means the previous iteration has no logical (at least on the layer I'm working at) connection to the current iteration. Global variables become practically useless. Well if VB had better arrays it wouldn't be a big deal.
So my solution was to use a table to temporarily store the data as the iterations come in and always return the latest concatenation of the groups on every iteration. It worked fine.
But I wasn't out of the woods yet. What happens when data in the temp table isn't cleaned up and the scope of the code isn't enough to tell the difference between new and old data? All kinds of fun things of course.
So I used a global variable (uhg...) to determine if this is the first iteration and if so clear out the table before continuing.
Well that worked with mixed results. The problem is the global variables are cleared in a way I don't understand.
If the query runs to completion then is closed and reopened. The global variables are preserved and the function fails to recognize it is a new run.
If the query breaks during execution the variables are cleared. So reopening the query means the flag in the global variable doesn't exist and it knows to clear the temp data table.
Well I want it to recognize when it is on the first iteration of an execution so it can clear the temp data table and run clean. And that is the problem this whole thing was about.
How do I identify the first iteration inside a custom VBA aggregate function? Or maybe clean up after myself instead, how do I identify the last? OR is there a better method of achieving my result all together. All I want to do is concatenate those silly strings. Something like GROUP_CONCAT() from MySQL would be nice...
My current solution is to prefix some characters, in the case below 'store1', to the fconcat call so it doesn't step on the toes of other instances within the overall query execution.
LAST(fconcat(desc,('store1
The data does not change between running instances of Access so overlapping new and old data in this context is not a big issue. The function also contains logic to discard repetitive entries so running the query twice without cleaning up in between yields the correct results but I don't like it.
I'm open to cleaner solutions.
I have a database loaded with objects that have identifying fields and a single string field, call it a description of the object. See the CSV table below.
category, type, color,desc
1,a,b,new
1,a,b,old
1,a,b,new
1,a,b,new
2,a,r,new
3,b,g,old
The data needs to be grouped by the first 3 fields (category, type, color). So I run a SQL query that groups the data, as seen below. Then use a custom VBA aggregate function to concatenate the string values of each item together.
SELECT
FIRST(category),
FIRST(type),
FIRST(color),
LAST(fconcat(desc,(categor
FROM table1
GROUP BY category, type, color;
LAST(fconcat(desc,(categor
So the first time I built this solution I simply used a global variable in fconcat to determine if the ids were the same as the last iteration.
If so the data from the last iteration is pulled from another global variable, the new string from the current iteration is concatenated with the old, stored in the same global variables and fconcat returns the latest complete string.
If ids differ from the last iteration the global variables are overwritten with the new and the new string is returned unaltered.
Because fconcat is wrapped by LAST the data from the most recent iteration for each grouping are used in the results. So I ended up with the complete strings.
That was fine for the first table. But a different table caused some fun results. For some reason on this table the iterations occur in a different order than the grouping. So all the strings from one set of items come at different times mixed in with the other groups. This means the previous iteration has no logical (at least on the layer I'm working at) connection to the current iteration. Global variables become practically useless. Well if VB had better arrays it wouldn't be a big deal.
So my solution was to use a table to temporarily store the data as the iterations come in and always return the latest concatenation of the groups on every iteration. It worked fine.
But I wasn't out of the woods yet. What happens when data in the temp table isn't cleaned up and the scope of the code isn't enough to tell the difference between new and old data? All kinds of fun things of course.
So I used a global variable (uhg...) to determine if this is the first iteration and if so clear out the table before continuing.
Well that worked with mixed results. The problem is the global variables are cleared in a way I don't understand.
If the query runs to completion then is closed and reopened. The global variables are preserved and the function fails to recognize it is a new run.
If the query breaks during execution the variables are cleared. So reopening the query means the flag in the global variable doesn't exist and it knows to clear the temp data table.
Well I want it to recognize when it is on the first iteration of an execution so it can clear the temp data table and run clean. And that is the problem this whole thing was about.
How do I identify the first iteration inside a custom VBA aggregate function? Or maybe clean up after myself instead, how do I identify the last? OR is there a better method of achieving my result all together. All I want to do is concatenate those silly strings. Something like GROUP_CONCAT() from MySQL would be nice...
My current solution is to prefix some characters, in the case below 'store1', to the fconcat call so it doesn't step on the toes of other instances within the overall query execution.
LAST(fconcat(desc,('store1
The data does not change between running instances of Access so overlapping new and old data in this context is not a big issue. The function also contains logic to discard repetitive entries so running the query twice without cleaning up in between yields the correct results but I don't like it.
I'm open to cleaner solutions.
Hamed Nasr
Upload a sample database and explain what do you expect using this sample database.
Like hnasr, I'm a bit confused.
But to your issue of global variables, instead of using them you can use the hidden form trick.
Create a form that opens when your database opens. In that form, add a Textbox named something like "txQueryIteration". Instead of writing to your global variable, write to that textbox:
Forms("MyHiddenForm").txQu
This value won't be reset when errors occur, so you always have it available.
But to your issue of global variables, instead of using them you can use the hidden form trick.
Create a form that opens when your database opens. In that form, add a Textbox named something like "txQueryIteration". Instead of writing to your global variable, write to that textbox:
Forms("MyHiddenForm").txQu
This value won't be reset when errors occur, so you always have it available.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Fyed, I like it. Makes perfect sense. I could actually pass fconcat the target field, table name, and WHERE clause to make it work for most situations. I will give it a shot tonight and post my results.
I've requested that this question be closed as follows:
Accepted answer: 500 points for fyed's comment #a39764887
for the following reason:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Accepted answer: 500 points for fyed's comment #a39764887
for the following reason:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
ASKER
Great outside of the box approach.