We help IT Professionals succeed at work.

Execute dynamic VBA code just like docmd.runsql(SQLtext)

ScottGutman
ScottGutman asked
on
Medium Priority
1,029 Views
Last Modified: 2012-08-13
I am trying to make my app flexible and accept code on the fly.

I am using msaccess 2003 for the frontend and MS SQL2000 for the backend.  I compile all projects and the users run an *.ade file.

currently I create the dynamic code in T-SQL and execute it on the SQL server.  This as it is not secure and could crash with the wrong code.  I want to execute the code locally and/or use VBA code.

So basically I would like to create a string like:

str2Exec = "=iif(Value = 1, "S", "C")"

Then Replace the "Value" with a real number:

str2Exec = Replace(str2Exec, "Value", 1 )

Then finally exec the string and save the result

strResult = RunVBA(str2Exec)


Any ideas on how to execute this code on the fly?
Comment
Watch Question

Have you tried the Eval() function?

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Commented:
Hello,

If your strings contain only standard VB operators and built-in functions, you can use "Eval":

    ? Eval(Replace(" IIf(Value = 1, ""A"", ""B"") ", "Value", "13"))

Cheers!
(°v°)

Author

Commented:
exactly what i was looking for!!

Thanks
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.