Export all queries of an access database with powe

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?

Answer 1

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
    }
}

Answer 2

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
    }
}