• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 334
  • Last Modified:

VBScript - Run commands against items in 2 Access Tables


Thanks to LMSConsulting's suggestion in this thread:  http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_24211524.html

-  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

  • 4
  • 2
2 Solutions
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

usslindstromAuthor Commented:
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.
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

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

usslindstromAuthor Commented:
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.  :)
usslindstromAuthor Commented:
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!
usslindstromAuthor Commented:
Very much appreciated!  Worked like a champ!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now