Python Spyder using Conda PYODBC and 32 vs 64 bit data drivers and Microsoft Office 2019 Pro Plus 32 vs 64 bit

This last week was a crash course in Python using Spyder and Anaconda. Basically, learn everything about Python programming in about 16-24 hours total. The best place to start is an online PDF that goes through the basic extremely fast and then into the fundamentals of the basics like arrays, looping, conditions, etc, and of course the UI GUI, File I/O, Database I/O, etc.

 

The first pass was through Hans-Petter Halvorsen "Python Programming" (REF 1). That took 4-6 hours each day, for two days. That was awesome, yet ran into the learning curve of Python being 64 bit and ODBC drivers c/o Microsoft and Office were 32 bit and conflicting. I spent 3 hours figuring that out and resolving it (REF 2), and eventually had to uninstall Microsoft Office 2019 Professional Plus (32 bit) while development testing using Microsoft Uninstall Powershell utility which was brutally slow and thorough, 2 reboots required (REF 5). Once I got the following to work, then I needed to download the 64 bit version of Office 2019 Professional Plus, which I did, and that worked.

 

Here is the access database x64 engine download example.

  

 

Because Office 2019 Pro Plus is typically 32 bit, you have to learn how to uninstall the 32 bit offending drivers before the 64 bit drivers can be installed by going into the Installer directory, turning on Subject menu item and finding the items that are shown during your attempt to install the X64 Access Database engine drivers.

 

 

 

 

One you get that fixed, then your Spyder Python example .py programs will work 

 

 

 sample source code

 

import tkinter as tk
from tkinter import ttk, messagebox
import pyodbc
import pandas as pd

# ---------------- CONFIG ----------------
DB_PATH = r"C:\data\example.accdb"
TABLE = "Employees"

CONN_STR = (
    r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};"
    rf"DBQ={DB_PATH};"
    r"ExtendedAnsiSQL=0;"

)

# ---------------- DB FUNCTIONS ----------------
def get_connection():
    return pyodbc.connect(CONN_STR)

def insert_employee(name, dept, salary):
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute(
        f"INSERT INTO {TABLE} (Name, Department, Salary) VALUES (?, ?, ?)",
        name, dept, salary
    )
    conn.commit()
    cursor.close()
    conn.close()

def fetch_employees():
    conn = get_connection()
    df = pd.read_sql(f"SELECT * FROM {TABLE}", conn)
    conn.close()
    return df

# ---------------- UI FUNCTIONS ----------------
def on_insert():
    try:
        name = name_entry.get()
        dept = dept_entry.get()
        salary = int(salary_entry.get())

        if not name or not dept:
            raise ValueError("Name and Department required")

        insert_employee(name, dept, salary)
        status_label.config(text="✔ Record inserted successfully")
        refresh_table()

    except Exception as e:
        messagebox.showerror("Error", str(e))

def refresh_table():
    for row in tree.get_children():
        tree.delete(row)

    df = fetch_employees()
    for _, row in df.iterrows():
        tree.insert("", "end", values=list(row))

# ---------------- UI LAYOUT ----------------
root = tk.Tk()
root.title("MS Access Database UI")
root.geometry("700x500")

# Input Frame
input_frame = ttk.LabelFrame(root, text="Add Employee")
input_frame.pack(fill="x", padx=10, pady=5)

ttk.Label(input_frame, text="Name").grid(row=0, column=0, padx=5, pady=5)
name_entry = ttk.Entry(input_frame, width=20)
name_entry.grid(row=0, column=1, padx=5)

ttk.Label(input_frame, text="Department").grid(row=0, column=2, padx=5)
dept_entry = ttk.Entry(input_frame, width=20)
dept_entry.grid(row=0, column=3, padx=5)

ttk.Label(input_frame, text="Salary").grid(row=0, column=4, padx=5)
salary_entry = ttk.Entry(input_frame, width=10)
salary_entry.grid(row=0, column=5, padx=5)

ttk.Button(input_frame, text="Insert", command=on_insert).grid(row=0, column=6, padx=10)

# Status
status_label = ttk.Label(root, text="")
status_label.pack(pady=5)

# Table Frame
table_frame = ttk.Frame(root)
table_frame.pack(fill="both", expand=True, padx=10, pady=10)

columns = ("ID", "Name", "Department", "Salary")
tree = ttk.Treeview(table_frame, columns=columns, show="headings")

for col in columns:
    tree.heading(col, text=col)
    tree.column(col, width=150)

tree.pack(fill="both", expand=True)

# Refresh Button
ttk.Button(root, text="Refresh", command=refresh_table).pack(pady=5)

# Initial Load
refresh_table()

root.mainloop()

 

Finally, once you have your Spyder 6 python ODBC to MS Access working in 64 bit mode, then you can locate Microsoft Office 2019 Pro Plus 64 bit (REF 3, 4) and install and activate that. the IMG (Part 3: Microsoft Office 2019 Pro Plus ISO 64 bit Download and Installation) you can download and open and extract with 7zip and execute the 64 bit Setup.

 

 

 

Once office 2019 pro plus is installed, activated, you can check the version in the About and you'll see 64-bit is shown for each application (Word, Excel, Access, Powerpoint, etc) 

 

 

Enjoy, 48 hours of figuring out Python Spyder 64 bit PYODBC and moving off Microsoft Office 2019 Pro Plus (32 bit) an onto (64 bit)...and more to come...

 

REF 1: https://www.halvorsen.blog/documents/programming/python/resources/Python%20Programming.pdf

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

REF 3: https://www.wps.com/blog/microsoft-office-2019-professional-plus-iso-64-bit-offline-installer-free-download-guide/ 

REF 4: https://officecdn.microsoft.com/pr/492350f6-3a01-4f97-b9c0-c7c6ddf67d60/media/en-us/ProPlus2019Retail.img

REF 5:  https://support.microsoft.com/en-us/office/uninstall-microsoft-365-or-office-from-a-pc-9dd49b83-264a-477a-8fcc-2fdf5dbf61d8?ui=en-US&rs=en-US&ad=US

 

 

The Microsoft Office Uninstall Troubleshooter downloads the following in your %userprofile%  appdata\local\temp folder and you have to grab it and copy it some place, and in Powershell ISE (administrator) open and execute the Run.ps1. In my case I just saved it to the working directory for my Spyder Python Example.mdb/Example.accdb folder, and it took two times and two reboots to get Microsoft Office fully ripped out of my system. While between Office 32 bit and Office 64 bit, I used LibreOffice 25.8 to manipulate and manage the .mdb and .accdb files into the .odb format until I got to this part of the problem solve.