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

We have hundreds of MS access databases which contains multiple tables and queries(don't have exact count). We already have working process to import tables data and now want to extract queries definition. We can always do it manually but that's going to take months and need some less time time taking approach. This should be a one time load and there is not much difference in Access and databricks SQL.

I am not familiar with VBA so I took a small piece of code from Stack overflow to extract queries definitions.

Sub queries_def()

Dim db As DAO.Database

Dim qdf As DAO.QueryDef

Set db = CurrentDb()

For Each qdf In db.QueryDefs

Debug.Print qdf.SQL 

Next qdf

Set qdf = Nothing

Set db = Nothing

End Sub

Answer 2

Okay, thank you for clarifying.

You want the SQL from queries. I have to admit that it never would have occurred to me to try to use Python for that. It might help to know the context in which you want to do this.

Is it a one-time process?

Will you need to do this repeatedly?

How many queries are there in all?

Is the SQL syntax for Access queries the same as the SQL syntax for Databricks views? Can you even do this effectively given the different platforms involved?

If it's a one-time process, why do it the hard way by attempting to extract using a non-Access programming language?

Why not export the SQL from Access to a data transfer format (e.g. a text file) and use it to create the new views (to the extent possible, of course)?

Again, I remind you. Macros are not the same as VBA in Access. When you talk about using "macros" to fetch query definitions, are you actually doing this with VBA? Show us some of that code, please.

Finally, it's pretty straightforward to move data back and forth between disparate platforms, but I'm not aware of a lot of ways to move other objects, like query definitions. So maybe this is not the most impactful approach to the requirement. Maybe a more creative approach will get the job done for you and do it more efficiently.

Answer 3

I want to extract SQL statement of all queries created in ms access using python. So that, l can use those SQL statement to add them as view in databricks...

Answer 4

VBA and macros are two totally different things in Access. And Python is different from both of those.

What, exactly, are you trying to "extract" and why?

Answer 5

What are you trying to accomplish here? Get the SQL statements used by Access queries?