Using eval function to run multiple statements of code separated by a colon

Declan_Basile
Declan_Basile used Ask the Experts™
on
In Access 2003, two statements of code in 1 line separated by a colon (ex. DoCmd.RunSQL("Delete * From Table1"):DoCmd.OpenForm("Form1")  works when run in the immediate window.  Also, setting a variable to either one of the two statements and specifying the variable as a parameter of the eval function works.  However, setting a variable to both statements separated by the colon and using it as a parameter to the eval function will not run.  Is there a limitation to the eval function only being able to run one statement?  If so, is there a way to specify more than one statement in a variable and run it?  I can parse the string and run each statement one at a time with the eval function, but I was hoping for an easier solution before I do that.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
Erm, not sure to understand everything.
Can you provide some sample code ?

Side note:
Give up the DoCmd.RunSql statement, because it is often used in conjonction with turning off Access warning (to avoid pop-ups), as a result, should a query fail (for whatever Reason), it will remain silent.
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
instead of docmd.runsql, I would recommend you use:


currentdb.execute "DELETE * FROM Table1", dbfailonerror

I'm not sure why you would want to execute these as part of an Eval( ) function call.  Can you provide a use case?

Dale

Author

Commented:
I'm creating a very generalized, data defined program to separate the developer's role from the role of the person who defines the program.  In doing this, I'm allowing people to enter code to run into a table and developing a program that will run the code.  I've gotten it to work by parsing the string into individual statements; however, I'm having trouble with the RunSQL.  I'll try the currentdb.execute as you suggested and report back.  Thank you.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
When I run  ...

currentdb.execute("INSERT INTO TempPOIds(POId) SELECT DISTINCT PTQRY.POId FROM PTQRY")

in the immediate window I get the correct records in TempPOIds

When I run ...

x = "currentdb.execute(""INSERT INTO TempPOIds(POId) SELECT DISTINCT PTQRY.POId FROM PTQRY"")"
eval(x)

in the immediate window, it appears to run twice.  I get the 7 records I get from running it the first way, and then the same 7 records are repeated in the same order (14 records total).  Why would I get twice the results from using the eval function?  This is the same problem I had with using DoCmd.RunSQL.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
1.  How are you going to guarantee that the users will get the syntax correct?  This sounds like a recipe for a disaster.  

2.  Do you know anything about SQL Injection attacks?  This is the type of thing that hackers would just love to get their hands on to mess with your data.

3.  No idea why it appears that the:

eval(x)

line is firing the append query twice.  Are you certain that you deleted from that table before running that particular line of code?

Author

Commented:
Yes I'm sure.  I didn't forget to delete the records before running the code.  I almost wrote that in the last message but wanted to keep the message as short as possible.  The users will be limited, they will have a chance to test the code, and their permissions limit them (and the code they write) from making any changes to the data.  The Insert statement only changes data in a local table with temporary data in it.  Thanks for very much for your help!

Author

Commented:
Thank you very much for the help!
Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
Hmm, since the eval function can run potentially any code, giving the possibility to user to play with it (directly or indirectly) is a wide open security breach IMO.

Author

Commented:
Thank you for bringing up this point.  The data that is code that can be run is as secure as any of the other production data.  If someone can get in to update this data then he/she can get in to update any of the other data, in which case he/she wouldn't need to enter malicious code to run to compromise the data.  He/she would be able to compromise whatever data he/she wants by directly changing the data.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial