Run vba code or routine stored into field (1 Viewer)

[MajP]

OK I misunderstood what you were attempting. This is even worse than I originally thought. That is why @theDBguy was referring to extensibility and theoretically might be able to be done, but I cannot picture a potential real use. Even if this is possible, this is such a horrible idea for so many reasons. Do not waste your time on this. Just build a robust split database. You can search on this site for many strategies to push an updated front end. That is pretty common and done all the time to distribute the updated code.

 

[gstylianou]

OK I misunderstood what you were attempting. This is even worse than I originally thought. That is why @theDBguy was referring to extensibility and theoretically might be able to be done, but I cannot picture a potential real use. Even if this is possible, this is such a horrible idea for so many reasons. Do not waste your time on this. Just build a robust split database. You can search on this site for many strategies to push an updated front end. That is pretty common and done all the time to distribute the updated code.

Thanks a lot

 

[Pat Hartman]

All the source code has been removed from the .accde. You cannot make "new" code nor update existing code, period.

 

[The_Doc_Man]

Using the idea of having executable code in a text box and trying to execute it violates SO many principles of good programming that I don't know where to begin. But I'll try.

First and foremost, the ability to inject code into an app would allow someone to undo, for example, the steps you take to hide the ribbon and navigation pane. It would allow someone to do a "CurrentDB.Execute 'DELETE * FROM table';" if you allowed unscreened access to such a facility. If someone decides to run an update query to add 50,000 currency units to an account, it will happen and you won't even know how.

Second, the issue with an ACCDE file is that supposedly the compiler isn't going to work because all of the code has already been successfully compiled. I don't think a link remains for the VBA compiler to do anything in the ACCDE file. I.e. you can't pass that text sequence to anything that can do anything useful with it. No way to activate the compiler and that is what you would need to implement this idea as generally as you appear to have in mind.

Third, self-modifying code (which is what you are describing) means you have NO WAY to guarantee code reliability. Someone could enter code with bad syntax and suddenly <BANG><ZOOM> your code crashes because there was no error handling available in that context. They could enter code that violates system security issues in some way and you would have no way to catch it.

Fourth, because user-level security features have LONG been disabled, you don't even have a way to block all sorts of mischief - because there is nothing you can do to block that code from doing bad things. The putative user is already in your system with permission to use this feature. The gate can't be closed but it doesn't matter because at that point, the horses have left the barn.

To say that this approach is incredibly dangerous doesn't quite seem adequate. @gstylianou - I am not trying to "rain on your parade." I hope to prevent you from going in a dangerous and destructive direction.

 

[gstylianou]

Using the idea of having executable code in a text box and trying to execute it violates SO many principles of good programming that I don't know where to begin. But I'll try.

First and foremost, the ability to inject code into an app would allow someone to undo, for example, the steps you take to hide the ribbon and navigation pane. It would allow someone to do a "CurrentDB.Execute 'DELETE * FROM table';" if you allowed unscreened access to such a facility. If someone decides to run an update query to add 50,000 currency units to an account, it will happen and you won't even know how.

Second, the issue with an ACCDE file is that supposedly the compiler isn't going to work because all of the code has already been successfully compiled. I don't think a link remains for the VBA compiler to do anything in the ACCDE file. I.e. you can't pass that text sequence to anything that can do anything useful with it. No way to activate the compiler and that is what you would need to implement this idea as generally as you appear to have in mind.

Third, self-modifying code (which is what you are describing) means you have NO WAY to guarantee code reliability. Someone could enter code with bad syntax and suddenly <BANG><ZOOM> your code crashes because there was no error handling available in that context. They could enter code that violates system security issues in some way and you would have no way to catch it.

Fourth, because user-level security features have LONG been disabled, you don't even have a way to block all sorts of mischief - because there is nothing you can do to block that code from doing bad things. The putative user is already in your system with permission to use this feature. The gate can't be closed but it doesn't matter because at that point, the horses have left the barn.

To say that this approach is incredibly dangerous doesn't quite seem adequate. @gstylianou - I am not trying to "rain on your parade." I hope to prevent you from going in a dangerous and destructive direction.

Hi Doc_Man

All the above appreciated by me, and of course I'm not going to deploy all the vba project into a several fields....I just wanted to use this for a small changes (e.g change the color lets say for example)...Anyway, all understanding by me and i would like to thank all for you time to explain me.

Regards

 

[The_Doc_Man]

I just wanted to use this for a small changes (e.g change the color lets say for example)

I think you would have been disappointed if you tried this. There is a matter of the scope of execution in that the code would HAVE to be for the form that is attempting to execute it (as opposed to some other form), further complicated by the difficulty of saving a form design element (i.e. selecting a color) with a control object's property-change commands while the form is open in Form mode. Not actually impossible, but it has some issues. I've tripped over them while debugging and wanting to change something, forgetting that I really WASN'T in design mode but rather was in FORM mode and running code via debugger actions. It wasn't pretty.

 

[gstylianou]

Thanks all

 

[GBalcom]

If allowed, please let me take a theoretical spin-off of this. We've already admitted that the proposed idea was a poor one, and other solutions have been given.

I use an off the shelf estimating software. Within it, it allows the user to create their own formulas that derive values for the user. The form displayed to create the formulas displays the fields the user is allowed to use within a formula, as well as the allowable math functions such as multiply, divide, equal to, more than, less than, etc. After the user "builds" their formula, they can check it within that form, and save it. It can then be applied to detail rows within the program.

I've often wondered if something like this was even possible with Access and VBA; Allowing the user to create their own functions. If it is possible, how could it be done?

 

[theDBguy]

If allowed, please let me take a theoretical spin-off of this. We've already admitted that the proposed idea was a poor one, and other solutions have been given.

I use an off the shelf estimating software. Within it, it allows the user to create their own formulas that derive values for the user. The form displayed to create the formulas displays the fields the user is allowed to use within a formula, as well as the allowable math functions such as multiply, divide, equal to, more than, less than, etc. After the user "builds" their formula, they can check it within that form, and save it. It can then be applied to detail rows within the program.

I've often wondered if something like this was even possible with Access and VBA; Allowing the user to create their own functions. If it is possible, how could it be done?

Hi. Yes, that's very possible in Access. A close comparison is where you allow the user to build their own adhoc queries. Also, this is what the Wizards do. Otherwise, you can also use VBA extensibility to do the more advanced code manipulation.

 

[The_Doc_Man]

The "EVAL" function, which you should look up if not familiar with it, would allow you to take an arbitrary formula and compute a result. As long as the items in the formula are globally or locally visible to the form holding this facility, you can do the computation. The form in question could also have a button that says "Save this formula" and even prompt you for a name. If you then store the name as a key and the formula as a second field, you could have a combo box that looks up and loads formulas for you. Lots of variations are possible. Numerical formulas are not in the same category as the issue we discussed with gstylianou because an EVAL can only return a number; it can't do anything else to the code around it.