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,422
    Provided Answers: 7
    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

    See clear as mud


    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 based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  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,422
    Provided Answers: 7
    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 17:10.
    hope this help

    See clear as mud


    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 based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  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 04: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
  •