Python and Microsoft Access

Everything about python and Microsoft Access (Step by Step) :

First download and install Microsoft Access Database Engine 2010 Redistributable from this link if you haven’t :

https://www.microsoft.com/en-us/download/details.aspx?id=13255

Then you should install pyodbc module.

• In Pycharm please go to File>Settings>Project:YourProjectName > Project interpreter :  then on the right side click on + and search pyodbc

Generally , jetbrains is very interested in complicating his menus .

• In Thoony go to tools > Manage Packages… and search pyodbc

Now you can connect to access database :

ConFileName=(r'c:\mydb\myaccess.mdb')
conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=' + ConFileName + ';')
cursor = conn.cursor()

To select from any table in the database please use this simple code :

ConFileName=(r'c:\mydb\myaccess.mdb')
conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=' + ConFileName + ';')
cursor = conn.cursor()
cursor.execute('select * from table1')
for row in cursor.fetchall():
    Table1Array.append((row[0],row[1],row[2])
print(str(len(Table1Array))+" records in Table1 loaded successfully.")

You can use Table1Array.append(row) to add all fields or use row[0],row[1],row[2],… to add any fields of table you need.

To insert in to the access table :

ConFileName=(r'c:\mydb\myaccess.mdb')
conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=' +ConFileName+ ';')
cursor = conn.cursor()
for ta in TableArray:
    Sql_insert_query = "INSERT INTO Table1(ID, Value1,Value2,Value3,Value4,Value5,Value6) " \
                         "VALUES ('{a}','{b}','{c}','{d}','{e}','{f}','{g}')".format(a=str(ta[0]),b=str(ta[1]),c=str(ta[2]),d=str(ta[3]),e=str(ta[4]),f=str(ta[5]),g=str(ta[6]))
    cursor.execute(Sql_insert_query)
    conn.commit()
cursor.close()

To Create the new Access database :

import win32com.client
ConFileName = r'Catalog:\mydb\NewAccessDB.mdb'
try:
    Catalog = win32com.client.Dispatch('ADOX.Catalog')
    Catalog.Create('Provider=Microsoft.ACE.OLEDB.12.0;Data Source=' + ConFileName + ';')
    Catalog = None
except:
    Exception as e:
        print("Database generation failed, Error="+str(e))
print("NewAccessDB.mdb created successfully")