Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2006
    Posts
    5

    Unanswered: Run Specified Queries From Table in VB

    I have a table that lists out query names, and includes check boxes indicating if the user wants to run the query. How can I write code in VB to loop through this table and run each query?

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,468
    Provided Answers: 10
    something like this.


    Dim Db As Database
    Dim rs As Recordset
    Dim SQL As String

    Set Db = CurrentDb()

    SQL = "YOUR TABLE NAME"

    Set rs = Db.OpenRecordset(SQL)


    docmd.SetWarnings False ' turn off the warning messages
    Do until rs.EOF

    If rs.feild("checkboxfeildname") = True then

    docmd.OpenQuery rs.feild("Querynametorunfeildname")

    else

    end if

    rs.movenext
    loop
    docmd.SetWarnings Ture ' turn on the warning messages

    rs.close
    set db = nothing
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Danger Will Robinson!

    Not to be a stick in the mud, but have you carefully considered the security and integrity ramifications of allowing end-users to run arbitrary SQL stored in a physical table?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011

    More danger!

    With warnings set to false, any hic-cups in the queries could become big problems since the user would never know ....

  5. #5
    Join Date
    Sep 2006
    Posts
    5
    Thanks for the quick reply on the warning and the code sample. After trying out the code, I keep getting a Compile Error: "Method or Data Member Not Found" at the "rs.Field" code segments. How do I get around this?

  6. #6
    Join Date
    Sep 2006
    Posts
    5
    Answered my own question... it should read "rs.Fields"

    Thanks a ton for your help!

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Well done! Happy SQL Injections!
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Teddy
    Well done! Happy SQL Injections!
    Ted - do you really get concerned about SQL injection in an internal Access db (note - I'm not thinking with an enterprise server BE or some omnipresent FE like ASP.NET - just an Access FE & BE which I presume this is)? If so - presumably this sort of thing is the least of your worries - you must really lock your DBs down huh?

    Just curious.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    That's why I don't have/allow stored queries in my Access projects ... Everything is done in code to prevent endusers from being able to run/abuse those queries ... Especially since I'd be the one held accountable and would have to clean up the nuclear fallout ...

    P.S. - My fault for leaving the things exposed in the 1st place ... And as usual, get the "Oops! Didn't know I WAS NOT supposed to run that ..."
    Back to Access ... ADO is not the way to go for speed ...

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by pootle flump
    Ted - do you really get concerned about SQL injection in an internal Access db (note - I'm not thinking with an enterprise server BE or some omnipresent FE like ASP.NET - just an Access FE & BE which I presume this is)? If so - presumably this sort of thing is the least of your worries - you must really lock your DBs down huh?
    Absolutely. Prior to this gig, I did a tour of duty as an Access consultant putting out fires when companies realized their applications were poorly developed, insecure and currently non-functional.

    ANYTHING that is going to grant access to a user who is not me gets the kabosh. If you don't know how to directly write your own queries then I don't want to give you the ability to do so, if you DO know how to roll your own then I definately don't want to give you the ability to do so. My experience tells me average "Access Guru" for a given office generally knows just enough to be dangerous, this is the viewpoint of a person who regularly got brought in from outside after said "Guru" tanked a mission-critical system.

    It's flat-out dangerous to be loading entire sql statements from a physical table, regardless of the user base and/or practical application. There are ways to provide reasonably dynamic SQL features without straight-up loading "raw" sql from an exposed table and running it without a second thought.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  11. #11
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,468
    Provided Answers: 10
    Sorry about the rs.Field yes it should of been rs.Fields


    docmd.SetWarnings False

    I only do this when I 100% happy With My Querys
    Last edited by myle; 09-21-06 at 16:10.
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by myle
    docmd.SetWarnings False

    I only do this when I 100% happy With My Querys
    BTW - perhaps you just coded short hand but if I have no option but to use docmd.SetWarnings False I always include an error trap with the docmd.SetWarnings True in there so it is impossible for the procedure to exit without running that one line of code.

    Thanks Teddy - interesting. All my stuff is now disconnected ADO\ SQL Server so I don't really have to think so hard about Access security. Having said that, I didn't used to bother too much when I used Access BEs either. IME Access is not really designed with ease of security implementation in mind - I never had a problem (not saying there wasn't the potential....) so no real driver to sort something out.
    Last edited by pootle flump; 09-22-06 at 03:41.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •