Create new Access database and tables using Python

Question

I'm trying to create an Access database in Python and add two new tables to it. I'm using win32com and have managed to create the database but can't create the tables. All I get is unhelpful Windows errors. Can anyone help me?

The following code works fine:

dbname = r'C:/Users/Guest/Desktop/NewDB.mdb'
db = Dispatch("Access.Application")
dbEngine = db.DBEngine
workspace = dbEngine.Workspaces(0)

dbLangGeneral = ';LANGID=0x0409;CP=1252;COUNTRY=0'
newdb = workspace.CreateDatabase(dbname, dbLangGeneral, 64)

How do I add new tables to this database?

Answer 1

Because MS Access is both a GUI .exe application and a backend database, to create a database is a different process than creating database objects like Tables, Views (i.e., stored queries), and even Procedures.

As a comparison, the other file-level RDMS, SQLite, one must open its .exe shell to CREATE DATABASE. And the server-level RDMS's (SQL Server, MySQL, Postgres) one must log into the server instance to run the command. MS Access does not have the shell or instance facilities, just an application object.

Therefore, after creating a database with CreateDatabase method, consider running a DDL SQL statement to CREATE TABLE which you can do with the Execute() method.

Python COM Interface

from win32com.client import Dispatch

try:
    dbname = r'C:/Users/Guest/Desktop/NewDB.mdb'
    accApp = Dispatch("Access.Application")
    dbEngine = accApp.DBEngine
    workspace = dbEngine.Workspaces(0)

    dbLangGeneral = ';LANGID=0x0409;CP=1252;COUNTRY=0'
    newdb = workspace.CreateDatabase(dbname, dbLangGeneral, 64)

    newdb.Execute("""CREATE TABLE Table1 (
                      ID autoincrement,
                      Col1 varchar(50),
                      Col2 double,
                      Col3 datetime);""")

except Exception as e:
    print(e)

finally:
    accApp.DoCmd.CloseDatabase
    accApp.Quit
    newdb = None
    workspace = None
    dbEngine = None
    accApp = None

Python DB API

While the CREATE DATABASE is not available in MS Access SQL you can run the above Execute with any Python ODBC API with a CREATE TABLE command only after database file is created:

import pypyodbc

dbname = r'C:/Users/Guest/Desktop/NewDB.mdb'     
constr = "DRIVER={{Microsoft Access Driver (*.mdb, *.accdb)}};DBQ={0};".format(dbname)

dbconn = pypyodbc.connect(constr)

cur = dbconn.cursor()
cur.execute("""CREATE TABLE Table1 (
                 ID autoincrement,
                 Col1 varchar(50),
                 Col2 double,
                 Col3 datetime);""")
dbconn.commit()