X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Can You Iterate (loop) Through Functions?, Access 2013    
post Dec 21 2017, 01:55 PM

Posts: 471
Joined: 14-March 05

Initially, I thought of creating an array with say, 5 SQL updates, updating different tables per each array number. Something like strSQL(5); and then defining the variables: strSQL(1) = "Update this table" then strSQL(2) = "update that table" etc..and then a db.execute(strSQL(i))

But it occurred to me that if I have lots of tables (i..e my array number is high) and/or the SQL is relatively complex - that it would use a lot of memory in the code (see below for relative context)? What I don't know is, how substantial the memory use would be? In other words, how many SQL statements/size of all the words/verbiage in the statements would cause a drag on memory resources; that is relative to..what I thought might use less memory and perhaps in some ways be cleaner(?) which is..

Instead of one sub with an array as above, what if I created a sub that just ran the SQL created by functions? So the calling sub would be something like just the db.execute fcnName(i) and the function names being called would be fcnName1, fcnName2 etc. where each function would contain the update SQL?

I tried this function thing as above - db.execute fcnName(i) - and of course it didn't recognize the fcnName(i) as a function. I guess its looking for a variable. So, my question is two fold:

1) Is there any way to iterate through functions somewhere in the ballpark of what i was trying? (and even if this isn't the most effective way - just for conceptual sake - could it be done and if so, how?)
2) Comment on the first thing I discussed? Is there much more memory used assuming 5 array variables and maybe for each SQL statement ~100 characters? (and little else coded). If this is not substantial, what would be?

Thanks for any help!
Go to the top of the page
post Dec 21 2017, 02:13 PM

UtterAccess VIP
Posts: 6,130
Joined: 30-June 11

I'd love to understand a practical application for this.

What I have seen done by some developers is defined Constants and then called the appropriate constant, but without understanding your goal, it is very hard to offer a solution. Or is all of this just a theoretical problem?

I'll also add that with today's hardware, resource limitations are usually not even a consideration anymore! The best thing to do is test it out for yourself and see. You can check RAM usage without such code and then add the code and look at the change and decide from there if you are happy or not.
Go to the top of the page
post Dec 21 2017, 03:47 PM

Posts: 2,117
Joined: 4-February 07
From: USA, Florida, Delray Beach

  1. Why not store the SQL in a Table (tblQueries), Loop thru all Records in the Table, then Execute each SQL Statement in turn?
  2. tblQueries:
    1UPDATE [Order Details] SET [Unit Price] = [Unit Price] * 1.1
    3UPDATE [Suppliers] SET [Company] = 'Supplier Q' WHERE [Company] = 'Supplier A'
  3. Code to Execute each Update Query as defined in the SQL Field ([SQL]) in tblQueries.
    Dim MyDB As DAO.Database
    Dim rst As DAO.Recordset

    Set MyDB = CurrentDb
    Set rst = MyDB.OpenRecordset("tblQueries", dbOpenForwardOnly)

    With rst
      Do Until .EOF
        CurrentDb.Execute ![SQL], dbFailOnError
    End With

    Set rst = Nothing
Go to the top of the page
post Dec 21 2017, 03:57 PM

Posts: 471
Joined: 14-March 05

Adezi, Yeah, I had thought of using a table, in fact my db has code that does reference a table for SQL code for something else..

So, actually, yes, Daniel, it was more for the concept than anything. I just wondered if it could be done. Maybe knowing that helps me to understand other things, you know?

As for the concern over the memory usage, it wasn't the memory per se, my biggest concern was Access going kerbloowey because of too much stuff in memory and causing corruption in the modules. I have another db where this happened - I got an error message saying the modules were corrupted upon opening the db and I wondered if it was because I had 'too much' code; perhaps too many SQL statements and other stuff in memory that caused this that maybe could be prevented by not having so much in memory at one time?

Again, I'm not sure about this. My code work isn't the most elegant. I know that the shorter and more mathematical you can make your code, your are that much closer to the pc's native language and so probably don't have as much overhead/your code will run quicker. So, I'm not the best at math and my code, while not terrible, is not the best either. I'm getting better over time though, at least conceptually if not in practice - for example, regardless of its legitimacy, the memory issue and the function thing is something I wouldn't have even thought of 6 months ago..

Thank you both for your input, much appreciated..!
This post has been edited by catbeasy: Dec 21 2017, 03:58 PM
Go to the top of the page
post Dec 22 2017, 07:55 AM

UA Admin
Posts: 33,786
Joined: 20-June 02
From: Newcastle, WA

As Daniel suggested, this looks like a solution in search of a problem to me.

In fact, the processing needed to iterate that recordset looking for an appropriate statement to call might well outweigh other gains anyway, even if they were possible at all.

Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    21st October 2018 - 09:22 AM