running same query in different databases

Question

I wrote a query that I want to run in several access databases. I have 1000+ access databases with the same tables (same names, same fields). So far, I have been manually copying this query from a txt file to the sql view in the access query design screen for each database and then run it. I did not need to change the query language - everything is the same for the 1000 databases. Is there a way to automate this?

Answer 1

You can automate using Python's pyodbc module.
Something like this should get you started:

import pyodbc

def qry_ms_access(db, sql):
    conn = pyodbc.connect('DRIVER={Microsoft Access Driver (*.mdb)};DBQ='+db, 
                          autocommit=True)
    c = conn.cursor()
    c.execute(sql)
    for row in c:
        print row.mycol

    c.close()
    conn.close()

if __name__ == "__main__":
    DBS = ['/path/to/MSAccessDb1.mdb', '/path/to/MSAccessDb2.mdb'] # etcetera

    sql = 'SELECT mycol FROM MyTable;'

    for db in DBS:
        qry_ms_access(db, sql)