Using code to build more code ???

[bilbo_baggins_esq]

Other than some very early and limited exposure to original Basic, my first exposure to programming was with Macro sheets in Microsoft Excel.

Without seeming to brag, I must admit that wrote some pretty amazing code with Excel Macros, and continued to use it LONG after VBA was introduced.
My reluctance to abandon Macros is at least partially responsible for my now lagging in actual VB experience but i am learning.

One thing I was able to do in Macros that blew the minds of many other supposed Excel Experts was to use code, to build more code that would run within the currently running operations. This was VERY useful when, for one example, I was reading and evaluating data that contained litterally millions of possible combinations that could not easily be ascertained in advance.

By defining certain cells with named references, and then using the currently operating code to concatenate text strings, and then convert the text to readable values with the text to columns function, I could actually build code, name defined references, designate columns and many more things, without knowing in advance what would be found, all within one operating set of code and do it while it was running, and by itself.

So far, within VBA and or VB, I've not been able to figure this cabability out.
Much of what I'm doing now would seem much easier, and faster if rather than knowing in advance exactly what I'm processing, I could let the application read ahead and build its own code based on what it finds.

Is there any functionality that allows one to build code within a module, either currently running, or an external one that can be referenced by a preexisting function in the running module?

For example, lets say I have a 100,000 text strings and I want to find and define all words contained in those strings.
I do not know in aadvance what words will exist.
I do not know in advance how many words are in each string.
I do not know how many characters will be used.
I only know the strings potential maximum length.

At this point, I have to define a "guess" at the maximum number of words and define a variable for each numbered guess, lets say word_1 through word_25.
Now I have learned AFTER THE FACT that none of the stings has contained more than 15 words, but that is not a rule and could easily be exceeded.
Rather than taking a guess and limiting myself to the 25 maximum words, I'd much rather let the code read, and create variables as it determined the need and then define them.

I have worked with the "gusssing" method described above. I create a table and append to it from a cross tab of the unique strings, and then read each unique string and send them back to the database as one record with specific "Word_nn" columns, then sent each column to a single column in a different table and then cross tab that table to get the unique word references, but the chance exists that one of the strings could contain more words than my guess. This works, but after about 5000 records in a table it starts to slow drastically, and I have to stop it, dump the data into a storage table, delete the processed records and start over.

I could read each line, one at a time, and identify each single word using any of several delimiters, and then check the database to see if that word is already logged, but that seems slow considering that at an average of 10 words per string that creates about a MILLION steps of that single process.

What would seem MUCH better, if if I could let the application read to a certain limit, CREATE and define variables as needed, to a certain limit, and then send that to the database, and then start over until all were processed.

Am I WAY OFF BASE?
Is such a thing possible (to use code to build more code)?

thanks as always!!!

[RhinoBull]

If you can define your data table(s) (using whatever database) you can then run very simple LIKE sql statement to get all possible combinations you might have in your own dictionary.

... not sure if I get your question right though ...

[si_the_geek]

I'm having trouble finding the meaning of your actual question too.. but it sounds to me as if your database is badly designed (you shouldn't have multiple columns holding the same type of data, like word_1 and word_2), which is creating much more work than is needed.

Moving away from a database may well be a better idea tho.. but definitely not "creating variables" (if that is even possible). Instead use an Array, or better still a Collection, as that can stop duplicates very easily.

[bilbo_baggins_esq]

hmmm, I must not be explaining myself well, and or used a poor example to illustrate my real quest.

firstly, for the example.
As I mentioned, I have 100,000 text strings that need to be broken down into words and from that, I'd like to create a unique keyword table.
Within the 100,00 text strings, there is some duplicatoin, but there are still a little more than 50,000 unique strings.

I first tried to do this solely in the database with VBA but was unsatisfied at the time it took. One problem is that I've not been able to figure out how to define VBA variables directly from tables. So far, I've had to open a single record in a form then use somthing like:

Title = Forms!Main_Data!Title

I'm sure it has to be possible to define a variable directly from a column using criteria met within other columns, and I'm sure it will involve plain SQL, but I've thus far been unable to crack the syntax mystery of it. (in general I'm very unhappy with the "Object Browser". Most it looks like greek to me whereas I have specific memory of being able to simply look at macro functions and understand what it did without ever having used it before.)
Anyway, I wrote a public function that would break a string down to words, and used it in a query, but since I did not know in advance how many words would exist in any givin string, I had to "guess" that 25 would be more than enough. Thus, I created a TEMPORARY table that did indeed have 25 "word_1, word_2 columns. In turn the query had 25 columns. I wrote some simple VBA that processed each title, one record at a time. After about three hours, It had only completed about 15,000 titles. If I could have cracked the mystery of how to refer the public function to a column in a table, I could have avoided a lot of form opening and closing, and updateing records as processed, but still I have a feeling it would have taken several hours to run the query, and I still would have the table with 25 word columns.

Being unhappy with the time it was taking, partly due to all of the form opening and closing, I took advantage of my ability to define variables in VB directly from tables, and took a stab at it in VB. The code reads each title, defines pre-existing variables as they are found, then sends each to the database when it reaches the end of the string. I still used the 25 column table as a temporary home for the words in each record.
Since I've not cracked the code for how to compact a database from VB, when it started to slow down, I'd stop the code from running, copy the processed records to another table, delete the processed records from the working table, compact the database, and restart the code until it started to sow again.

I should clear up that the table with Word_1, Word_2, etc, is ONLY for temporary storage of the EXISTING titles. I have already built code in my new application that avoids this problem going forward, but in the mean time, I have to "normalize" my existing data.
From the 25 column table, I then appended each column into a SINGLE column in a different table, and then from a crosstab of that table appended each unique word into a final keyword table.
While I'm sure there was more elegant ways to accomplish this, I did manage to get it done, and rest assured the 25 column table was just a temporary step towards the final keyword table.

As for the my question, I was really just using that as an example of how clunky having to deal with pre-defined variables is for me.
In VB and VBA, I had to create variables Word_1, Word_2, ..., Word_25 in advance of even knowing if they were even needed, AND I had to create the 25 column table to house the records.

It would seem much better if I could have let the VB create and define each variable as it was needed, and then send all of them to a table in a single column.

HOWEVER, I suspect you'll tell me that what I could have done with an ARRAY in this specific example.

Is there a help resource on how to make use of ARRAYs with databases?

As for my general question; can you use code to build code, I'm guessing that since 2 members with a combined post total of over 27,000 didn't have an answer, that either I completely botched the question, or it simply is not possible.

[demon.KILER]

I am too lazy to read all that so my answer might be irrelevant
but for this sentence

As for my general question; can you use code to build code,

yeah of course U can its called a reflective technology I guess :P

[RhinoBull]

As for my general question; can you use code to build code...

What type of code do you need to build using what code?
Can you clarify it in just a few words please? What do you mean by "code" ?

[si_the_geek]

From what I understand, it's not really relevant here.. creating code is not what is needed, just making what is there more efficient/organised.

(the answer tho is actually yes, but it is cumbersome and the code runs slower - and really really doesn't sound like what you want!)

One problem is that I've not been able to figure out how to define VBA variables directly from tables. So far, I've had to open a single record in a form then use somthing like:
Title = Forms!Main_Data!Title

That is basically what you do.. the only obvious improvement is using an SQL statement to return just the relevant records - but for this many Words it's not ideal (as you would be running far too many queries, each time sending/retrieving data) and it becomes more efficient to use an array/collection instead, and at the end put the data from that into the database.

As to using the multiple columns, that is not good at all.. any queries need to look at the values of multiple fields, and to extract the data you need to do extra work (a crosstab is probably the most efficient way of doing that, but still slower than a plain query on a single column).

An array would simplify things, as you don't need to make separate variables, you just use one which contains multiple values (and can be resized if needed). As you are looking for unique values tho, it is easier (and I think faster) to use a Collection instead, as you can stop duplicates from being added by simply using a Key which is the same as the data (the Key cannot be duplicated), eg:

Dim myCollection as Collection
Set myCollection = New Collection

 'add a word (second parameter is the Key)
myCollection.Add "word1", "word1"

On Error Resume Next 
 'add a new word
myCollection.Add "word2", "word2"
 'attempt to add a word we already have - but the key is duplicated, so it wont be added
myCollection.Add "word1", "word1"
On Error Goto 0

..note that this is one of very few times I advocate the use of "On Error Resume Next", and there are 'cleaner' alternatives.

Once all of the words are in the collection, you can then put each value into your unique words table.

[bilbo_baggins_esq]

Hmmm, let me see if I can either show I understand, or show that I don't.

Dim myCollection as Collection
Set myCollection = New Collection
 
Dim Word_Title as string 'variable used to house a word once it is found
 
Return_Point:
' here I would read my title until the first word has been found
 
 'add a word (second parameter is the Key)
myCollection.Add "word1", Word_Title ' I assuming the second instance of "word1" from 
'your example is where I tell myCollection what the value of "word1" is?
 
' Here I could check to see if I'm at the end of the string. 
' either Goto Return_Point: or goto Title_Done:
 
' Here I get lost... See below
 
On Error Resume Next 
 'add a new word
myCollection.Add "word2", "word2"
 'attempt to add a word we already have - but the key is duplicated, so it wont be added
myCollection.Add "word1", "word1"
On Error Goto 0

in the line:
myCollection.Add "word2" , "Word2"

Can it actully be as follows:
myCollection.Add "word" & Word_Counter , Word_Title
(Where "Word_Counter" is a numeric count of words found thus far in the string). If this is possible, then I assume I could do away with the code from "On Error: on down.

If not, then presumably I'd have to take a guess at the maximum number of words possible and make sure that lines through word25 existed if needed.


So then, without regard to the answer above, wopuld I send it to the database as follows:

Set adoRecordset = New ADODB.Recordset
    With adoRecordset
        .Open "SELECT Keyword_Column FROM Keyword_Table", adoConnection, adOpenKeyset, adLockOptimistic
        .AddNew
        .Fields("Keyword_Column").Value = myCollection
        .Update
        .Close
    End With
    Set adoRecordset = Nothing

[si_the_geek]

You misunderstood about the collection idea (I didn't explain it too well!).

A collection can hold any number of items you like (and if you want to, you can add them at any position - unlike an array). The normal usage omits the second parameter (Key), as you normally only care about the value, eg:

myCollection.Add "word1"
myCollection.Add "word2"

The idea is that you create the collection (the Dim and Set lines), then for every word you find, simply add it to the collection. By specifying a Key and using the same value for the Key as well as the value itself, you ensure that only unique items can be added to the collection. If you leave that part out (or use a different key), it removes the benefit of using a collection.

The reason for O.E.R.N. is because if you try to add an item with a Key that already exists, an error occurs and the item doesn't get added - but you don't actually care about the error, as the item is already in the collection.


Unfortunately you cannot simply write an entire collection to the database like that, you will need to use a loop, eg:

        .Open "SELECT Keyword_Column FROM Keyword_Table", adoConnection, adOpenKeyset, adLockOptimistic
Dim lngLoop as Long
        For lngLoop = 1 To myCollection.Count
          .AddNew
          .Fields("Keyword_Column").Value = myCollection.item(lngLoop)
          .Update
        Next lngLoop
        .Close

..of course I may have got confused about what you are trying to achieve here, as your earlier posts were very long!