VBScript - Run commands against items in 2 Access Tables

Posted on 2009-04-27
Last Modified: 2012-05-06

Thanks to LMSConsulting's suggestion in this thread:

-  I've been able to do commands against each individual item in a single Access table.  Meaning, I have a table with a bunch of PC names, and (in the example provided) I can MsgBox each PC name in the list.

If possible, I'd like to evolve this script - to add another table.  IE

ComputerName          ExecuteName
PC1                            Command1.exe
PC2                            Command2.exe

Basically, what I'd like to do here, would be to run all the commands in the right table against the PCs in the left.  So, in other words - run command1 and command2 against PC1 - then move onto PC2, and rinse/repeat.

My guess is that it'll be some sort of "For each PC in Table1.ComputerName ***" but I honestly don't have any ideas past that.

Any assistance you guys can provide on this subject would be greatly appreciated.
Dim rst As DAO.Recordset

Set rst = Me.RecordsetClone



Do Until rst.EOF

  MsgBox "Testing " & rst("SomeFieldNameHere")



Open in new window

Question by:usslindstrom
    LVL 1

    Accepted Solution

    Method  1
       You could create a new query with both tables. Dont make any joins. The multiplier effect will kick in. Your query will have records for every PC with every Command. In your code use that query as your record set. Your code would run unchanged  from that point.
    Method 2
        Open 2 recordset: See code below

    Dim rsPC As DAO.Recordset
    Dim rsComm as DAO.Recordset
    Set rsPC = currentdb().openRecordset(SELECT * FROM tlbPC;)
    Set rsComm = currentdb().openRecordset(SELECT * FROM tlbComm;)
    Do until rsPC.EOF
       Do Until rsComm.EOF
         MsgBox "Testing " & RSComm("SomeFieldNameHere")

    Open in new window

    LVL 5

    Author Comment

    Very nice idea.  :)

    I ended up doing a select statement like you suggested, and it's going to give me the exact results of what I'm looking for.

    If I can ask one more question from ya.'     I ended up combining (UNION) a bunch of queries before I got to the final output of what I needed.  Then I filtered the list by some particulars of what I needed.

    It's sorta' hard to explain.  But basically my end result put the eact string I need to execute (PC and all) INSIDE the query I'm going to run against.

    So basically, I have a new query that has a single column (text block) called "CommandLine"

    psexec.exe -s \\PC1 "\\servername\share\command1.exe /q"
    psexec.exe -s \\PC1 "\\servername\share\command2.exe /q"
    psexec.exe -s \\PC2 "\\servername\share\command1.exe /q"

    and so on...        Is it possible to have the script act on every record of the DB as if it was it's own VB statement or Batch File?

    Thanks so much for helping.  Any assistance that can be provided is very welcome.
    LVL 1

    Assisted Solution

    I am not sure I understand what you want. There are a couple of ways to do what I think you want.
    Method 1:
          Just use the same code you originally had and replace:
         MsgBox "Testing " & rst("SomeFieldNameHere")
         Shell rst(CommandLine) , vbMaximizedFocus

    Method 2:
         Create a function that executes the code. Then add that function to your query. Then it would execute when ever the query is ran.

              Function fctRunCmd(strParam as string) as string
                  Shell strParam , vbMaximizedFocus
             End function
        Then your query would be:

            SELECT fctRndCmd([CommandLine]) AS RunCmd FROM test;

    I hope that helps

    LVL 5

    Author Comment

    Please forgive me, but I'm having some issues implementing that code.  I've copied both of them, and tried them out, but am having some errors when running the script.

    I believe part of the issue, is that in my queries, there's actually quite a bite of VB stuff that I've thrown in the output.  (Sorry for not mentioning it before).

    Meaning, along with that PSExec example I gave in my last post, I've got some queries that return results such as (in the same table):

    MsgBox ("Unable to patch PC1.  Cannot determine the command line switches associated with the patch provided:  'Command1.exe.'  Patching will need to be completed manually").

    When I run the script, I'm getting a "File not found" error...  Which I believe it's trying to Shell out to MsgBox on the local file system, which of course - doesn't exist.  What I can do with my queries, is all the PSExec returns, I can add the Shell statement into the query itself.

    Then, would it be possible just to have the original VB script call on the vb that's in the table itself?

    Sorry if my post rambles on, just trying to wrap my head around it.  Thanks very much for helping though.  :)
    LVL 5

    Author Comment

    Got it!  :)

    My "magic" command was first making a quick string of the data:    stCommandLine = rst("CommandLine").  Then hitting it up with:  Eval(strCommandLine).

    Works like a champ.  :)

    Thank you very much for the assistance on this.  You just helped me turn a patching nightmare for our network into a one-button "fix-all" by using PSExec.   :)   I really appreaciate your help here!
    LVL 5

    Author Closing Comment

    Very much appreciated!  Worked like a champ!

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now