How to create permanent MS Access Query by Python 3.5.1?
<p>I have about 40 MS Access Databases and have some troubles if need to create or transfer one of MS Access Query (like object) from one db to other dbs.
So I tried to solve this problem with <code>pyodbc</code> but.. as I saw <code>pyodbc</code> doesn't support to create new, permanent MS Access Query (object).
I can connect to db, create or delete tables/rows but can't to create and save new query.</p>

<pre><code>import pyodbc

odbc_driver = r"{Microsoft Access Driver (*.mdb, *.accdb)}"

db_test1 = r'''..\Test #1.accdb'''
db_test2 = r'''..\Test #2.accdb'''
db_test3 = r'''..\Test #3.accdb'''
db_test4 = r'''..\Test #4.accdb'''

db_test_objects = [db_test1, db_test2, db_test3, db_test4]

odbc_conn_str = "Driver=%s;DBQ=%s;" % (odbc_driver, db_file)
print (odbc_conn_str)

conn = pyodbc.connect(odbc_conn_str)
odbc_cursor = conn.cursor()

NewQuery = "CREATE TABLE TestTable(symbol varchar(15), leverage double)"

odbc_cursor.execute(NewQuery)
conn.commit()
conn.close()
</code></pre>

<p>SO, How to create and save MS Access Query <strong>like objects</strong> from python?
I tried to search info in Google, but the answers were related with <strong>Run SQL code</strong>.</p>

<p>On VBA this code looks like:</p>

<pre><code>Public Sub CreateQueryDefX()

   Dim base(1 To 4) As String
   base(1) = "..\Test #1.accdb"
   base(2) = "..\Test #2.accdb"
   base(3) = "..\Test #3.accdb"
   base(4) = "..\Test #4.accdb"

   For i = LBound(base) To UBound(base)
    CurrentBase = base(i)
    Set dbo = OpenDatabase(CurrentBase)
        With dbo
        Set QueryNew = .CreateQueryDef("TestQuery", _
         "SELECT * FROM TestTable")
         RefreshDatabaseWindow
        .Close
        End With
   Next i

RefreshDatabaseWindow

End Sub
</code></pre>

<p>Sorry for my English, it's not my native :)</p>

<p>By the way, I know how to solve this by VBA, but I'm interested in solve this by python.</p>

<p>Thank you.</p>

<p>You can use a <a href="https://msdn.microsoft.com/en-us/library/bb177895(v=office.12).aspx" rel="nofollow">CREATE VIEW</a> statement to create a saved Select Query in Access. The pyodbc equivalent to your VBA example would be</p>

<pre class="lang-python prettyprint-override"><code>crsr = conn.cursor()
sql = """\
CREATE VIEW TestQuery AS
SELECT * FROM TestTable
"""
crsr.execute(sql)
</code></pre>

<p>To delete that saved query you could simply execute a <a href="https://msdn.microsoft.com/en-us/library/bb177897(v=office.12).aspx" rel="nofollow">DROP VIEW</a> statement.</p>

<p>For more information on DDL in Access see</p>

<p><a href="https://msdn.microsoft.com/en-us/library/bb267262(v=office.12).aspx" rel="nofollow">Data Definition Language</a></p>

python vba ms-access pyodbc