Export all queries of an access database with powershell

Question

This question demonstrates how to do it with VBA, but I would like to do it with PowerShell, so it can be scripted and scheduled.

I expected the implementation should be identical as they both allegedly use the same COM but it seems QueryDefs is not defined in the COM that PowerShell accesses as it returns nothing.

# Open file in Access.
$accessCOM = New-Object -ComObject Access.Application
$accessCOM.OpenCurrentDataBase($dbPath, $false)

# get list of queries
$queries = $accessCOM.CurrentDB.QueryDefs # returns nothing.

# export query to file
ForEach ($query in $queries) {
    $name = $query.name
    $sql = $query.sql
    $dest = "$queryDest\$name.sql"
    Write-Output $sql > $dest
}

I can use .CurrentData.AllQueries but that returns a collection of AccessObjects and I don't know how to get the SQL from them. Is there any other way to get the QueryDefs or at least the SQL from the name?

What does $accessCOM.CurrentDB.Name show you?

HansUp
– HansUp

2016-03-02 17:47:51 +00:00
Commented Mar 2, 2016 at 17:47 — HansUp
– HansUp, Commented Mar 2, 2016 at 17:47
"CurrentDb" which is not the name of the .accdb file.

cheezsteak
– cheezsteak

2016-03-02 17:49:15 +00:00
Commented Mar 2, 2016 at 17:49 — cheezsteak
– cheezsteak, Commented Mar 2, 2016 at 17:49

Gord Thompson · Accepted Answer · 2016-03-02 17:48:19Z

3

This works for me with Access 2010. It just dumps the names of the non-system QueryDefs to the console, but it should get you started:

$dbe = New-Object -com DAO.DBEngine.120
$db = $dbe.OpenDatabase("C:\Users\Public\Database1.accdb")
$queries = $db.QueryDefs
ForEach ($query in $queries) {
    $name = $query.Name
    If (!$name.StartsWith("~")) {
        $name
    }
}

answered Mar 2, 2016 at 17:48

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

cheezsteak Over a year ago

I added how I opened the database in powershell as it's different than yours. Hopefully your method will work.

Francis Omoruto · Accepted Answer · 2017-01-14 11:50:58Z

1

Please try this modification to the previous post. Worked for me. After you run it once, other properties of the variables will be available in the PowerShell ISE through IntelliSense.

$dbe = New-Object -com DAO.DBEngine.120
$dbpath ="path/to/data.mdb"
$db = $dbe.OpenDatabase($dbpath,$false,$false,";pwd=r3ealLy?")
$queries = $db.QueryDefs
ForEach ($query in $queries) {
    $name = $query.Name
    If (!$name.StartsWith("~")) {
       $name + ":"
       $query.SQL
    }
}

answered Jan 14, 2017 at 11:50

Francis Omoruto

764 bronze badges

Collectives™ on Stack Overflow

Export all queries of an access database with powershell

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related