Use Powershell to create access 2007 Queries?

Question

I have been following Richard Siddaway's Awesome Series on Powershell+Access2007.

Unfortunately it ends before discussing creating/running/modifying access 2007 queries in powershell. How could this be done?

Answer 1

The cited series of articles uses a definition of stored procedure that is problematic. It says:

An SP is a piece of code that we have defined, and saved in the database".

While this may be correct in a metaphorical sort of way, it's incorrect for Access/Jet/ACE. There is no CODE in the objects in a Jet/ACE database that are referred to by the generic term "procedure. In Access/Jet/ACE, a "procedure" is just a stored QueryDef, as there is no procedural code allowed. I don't know if the OLEDB interface restricts it or not, but my guess is that PROCEDURE means DML query and VIEW means SELECT.

So (and I'm just guessing here -- I'm an Access developer so have no need for doing any of this externally), if you want to create/update a DML QueryDef, you'd use the PROCEDURE keyword and the relevant DML for creating/altering PROCEDUREs. Likewise, with SELECTs, you'd use VIEW (I'm assuming).