Extract queries from MS Access DB using python

Question

Hi Team

I am currently working on a project to retrieve queries definition from MS Access DB using python and even after trying different ways I am unable to extract queries. Any help on this will be highly appreciated.

Ways I tried.

1. Using pyodbc python library
2. Using mdb tools. With this approach, I am able to fetch tables definition but not queries.
3. Using VBA macro. With this approach, I am able to fetch query definition if I run macro in ms access db but not able to run macro from python.

Thankyou!

Dinesh

Answer 1

Nice work, integrating PowerShell with your Python script.

Continued success with the project.

Answer 2

Thanks both of you for your suggestions :) :)

I think I found a way to fetch queries definition. I constructed one powershell script to extract only queries definition and integrated it with my python code and its working as expected.. It sounds unusual but working for me..

######################################################################

Path to the Access Database

param([Parameter(Mandatory = $true)][string]$database_path,[Parameter(Mandatory = $true)][string]$output_directory)#$database_path = "C:\Users\rc08750\Downloads\Merit TX SUT Detail Refund Review 0605-1005.accdb"#$output_directory = "C:\Users\rc08750\Downloads\queries"

Get the file name without the extension$access_file_name = [System.IO.Path]::GetFileNameWithoutExtension($database_path)

Create a new instance of the Access application$access = New-Object -ComObject Access.Application

Suppress Access UI by setting the Visible property to false$access.Visible = $false

try {# Open the database$access.OpenCurrentDatabase($database_path)# Get the Queries collection$db = $access.CurrentDb()$queries = $db.QueryDefs

Iterate through each queryforeach ($query in $queries) {# Define the output file name$output_file_name = "$output_directory$access_file_name-$($query.Name).txt"# Write the query SQL definition to the text file$query.SQL | Out-File -FilePath $output_file_name -Encoding UTF8# Optionally, output to console for confirmationWrite-Host "Written query '$($query.Name)' to file '$output_file_name'"}}

finally {#Closing current DB$db.close()# Ensure the database is closed even if an error occurs$access.CloseCurrentDatabase()# Quit Access application$access.Quit()# Release COM object[System.Runtime.Interopservices.Marshal]::ReleaseComObject($access) | Out-Null

#Closing all MSAccess processesStop-Process -Name MSACCESS}

Answer 3

I would go with George's suggestion to export to a table. That would make it easier to import into your new systems.

But there is a non code alternative. If you use the Database Documenter built into Access, you can write all the queries including their SQL statements to Word Doc

Answer 4

I suspect that the more complicated Access queries are going to be less similar to the Databricks views than you might anticipate, but that remains to be seen. Particularly if some of the queries are parameterized, or if they use Access specific expressions. One would hope, of course, that those differences are minimal.

You could adapt the procedure you show to write out the SQL into text files that you could then use to create the views.

Or, you might use it to write the SQL into Long Text fields in a table in the accdb, and then extract that table, with those SQL strings, for your use. You already know how to retrieve tables.

In either case, you could write a loop that goes through a recordset of the querydefs in an accdb, one at a time, and add that SQL to a new record in the table.